Efficient way to run select query for millions of data

I want to run various select query 100 million times and I have aprox. 1 million rows in a table. Therefore, I am looking for the fastest method to run all these select queries.

So far I have t开发者_如何学Cried three different methods, and the results were similar.

The following three methods are, of course, not doing anything useful, but are purely for comparing performance.

first Method:

for i in range (100000000):
    cur.execute("select id from testTable where name = 'aaa';")

second method:

cur.execute("""PREPARE selectPlan  AS
    SELECT id FROM testTable WHERE name = 'aaa' ;""")

for i in range (10000000):
    cur.execute("""EXECUTE selectPlan ;""")

third method:

def _data(n):
    cur = conn.cursor()
    for i in range (n):
    yield (i, 'test')

sql = """SELECT id FROM testTable WHERE name = 'aaa' ;"""   
cur.executemany(sql, _data(10000000))



And the table is created like this: 

cur.execute("""CREATE TABLE testTable ( id int, name varchar(1000) );""")
cur.execute("""CREATE INDEX indx_testTable ON testTable(name)""")

I thought that using the prepared statement functionality would really speed up the queries, but as it seems like this will not happen, I thought you could give me a hint on other ways of doing this.

This sort of benchmark is unlikely to produce any useful data, but the second method should be fastest, as once the statement is prepared it is stored in memory by the database server. Further calls to repeat the query do not require the text of the query to be transmitted, so saving a small about of time.

This is likely to be moot as the query is very small (likely the same quantity of packets over the wire as repeating sending the query text), and the query cache will serve the same data for every request.

What's the purpose of retrieving such amount of data at once? I don't know your situation, but I'd definitely page the results using limit and offset. Take a look at: 7.6. LIMIT and OFFSET

If you just want to benchmark SQL all on it's own and not mix Python into the equation try pgbench.

http://developer.postgresql.org/pgdocs/postgres/pgbench.html

Also what is your goal here?