2

I am running the following query on 489 million rows (102 gb) on a computer with 2 gb of memory:

select * from table order by x, y, z, h, j, l;

I am using psycopg2 with a server cursor ("cursor_unique_name") and fetch 30000 rows at a time.

Obviously the result of the query cannot stay in memory, but my question is whether the following set of queries would be just as fast:

select * into temp_table from table order by x, y, z, h, j, l;
select * from temp_table

This means that I would use a temp_table to store the ordered result and fetch data from that table instead.

The reason for asking this question is that the takes only 36 minutes to complete if run manually using psql, but it took more than 8 hours (never finished) to fetch the first 30000 rows when the query was executed using psycopg2.

Rohita Khatiwada
  • 2,835
  • 9
  • 40
  • 52
  • The slowdown is almost surely in pyscopg2 (I assume your cursor is pyscopg2.cursor). The ordering difference you are talking may improve the 36 minute part but not the 8 hour part! pyscopg2 has nothing to do with the ordering it just sends messages to the server and receives the results. I would play around with decreasing the 30,000 number and see when you start getting slow results. Could be you become memory starved and the python result set gets swapped to disk. – nate c Mar 29 '11 at 17:40
  • Your question, is pretty vague, so it's hard to tell why psycopg2 and server-side cursors didn't work for you. But no, inserting them all into a temporary table and then scanning this table will only slow you down. Tometzky has it right -- create an index on the sort columns. – intgr Mar 30 '11 at 13:07

1 Answers1

3

  1. If you want to fetch this table by chunks and sorted then you need to create an index. Every fetch will need to sort this whole table if there will be no such index. Your cursor probably sorted this table once for every row fetched — waiting for red giant sun would probably end sooner…
    create index tablename_order_idx on tablename (x, y, z, h, j, l);

  2. If your table data is relatively stable then you should cluster it by this index. This way table data will be fetched without too much seeking on disk.
    cluster tablename using tablename_order_idx;

  3. If you want to get data in chunks the you should not use cursor, as it will always work one row at a time. You should use limit and offset:
    select * from tablename order by x, y, z, h, j, l
    limit 30000 offset 44*30000

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • -1 you don't 'need to create an index' because the query 'takes only 36 minutes to complete if run manually using psql'. likewise for 'cluster'. Using 'limit' and 'offset' is just plain wrong - even if no updates happen at all during your rather lengthy query, who is to say there will be no ties in the ordering? In the event of a tie at position 30000, can you be sure postgres will break the tie the same way for your first and second 'chunk'. No. –  Mar 30 '11 at 06:34
  • @JackPDouglas: Not very constructive critique — please provide better explanation. Limit and offset approach requires that primary key is last column in order clause. Updates in another transactions are no problem when serializable transaction level is chosen for this series of queries. – Tometzky Mar 30 '11 at 09:46
  • "Limit and offset approach requires that primary key is last column in order clause" - [not true](http://www.postgresql.org/docs/current/interactive/queries-limit.html) - are you referring to some other database than postgres? –  Mar 30 '11 at 09:57
  • not sure one way or the other about serializable isolation level - at the least you should probably mention it in your answer - but is it [enough](http://www.postgresql.org/docs/9.0/interactive/transaction-iso.html#MVCC-SERIALIZABILITY) anyway? –  Mar 30 '11 at 10:02
  • 2
    @JackPDouglas: I mean that to get meaningful results from limit/offset you must strictly order your data, which means that you need for example primary key as last column in order clause. It is not enforced but it does not work otherwise. Serializable isolation is sufficient for reading consistent snapshot of data in read-only transaction. It would be impossible to do consistent backups otherwise. Maybe I should've mentioned it all in my answer, but hey — we are all time-starved. – Tometzky Mar 30 '11 at 11:45