On Mon, Feb 22, 2010 at 2:15 PM, Frank Heikens <frankheikens@xxxxxxx> wrote: > > Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven: > >> On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys >> <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote: >>> >>> On 22 Feb 2010, at 19:35, Yang Zhang wrote: >>> >>>> I also wouldn't have imagined an external merge-sort as being very >>> >>> >>> Where's that external merge-sort coming from? Can you show an explain >>> analyze? >> >> I just assumed that the "Sort" in the EXPLAIN output meant an external >> merge-sort, given that the table has over 50 million tuples and is >> over 3GB, *and* there is no index on the sort key: >> >> tpcc=# explain select * from metarelcloud_transactionlog order by >> transactionid; >> QUERY PLAN >> >> ----------------------------------------------------------------------------------------------------------------- >> Sort (cost=8408637.34..8534662.95 rows=50410244 width=17) >> Sort Key: a.transactionid >> -> Seq Scan on metarelcloud_transactionlog a >> (cost=0.00..925543.44 rows=50410244 width=17) >> (3 rows) >> >> Anyway, I added the INDEX as suggested by Frank, but it's been 20 >> minutes and it's still running. With the index, EXPLAIN says: >> >> tpcc=# explain select * from metarelcloud_transactionlog order by >> transactionid; >> QUERY PLAN >> >> ----------------------------------------------------------------------------------------------------------------- >> Index Scan using i_transactionid on metarelcloud_transactionlog >> (cost=0.00..4453076.81 rows=50410164 width=44) >> (1 row) >> > > Use EXPLAIN ANALYZE to see how the query is executed, gives you more > details. As mentioned, this would take a very long time to complete running -- I have not yet seen one successful query completion yet. I'd need to let it run overnight. > > >>> If your work-mem is too low there's a good chance that Postgres has to >>> use your disks for sorting, which will obviously be quite slow. >> >> Relative to the non-terminating 80-minute-so-far sort, Unix sort runs >> much faster (on the order of several minutes). > > Make sure your index does fit into memory, what's the size of the index? How might I find out the size and whether it's being fit in memory? -- Yang Zhang http://www.mit.edu/~y_z/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
