> Try increasing sort_mem temporarily, and see if that > makes a difference: > SET sort_mem = 64000; > EXPLAIN ANALYSE ...
I did this (actualy 65536) and got the following: pvcsdb=# explain analyze select distinct version from vers where version is not null; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=117865.77..120574.48 rows=142 width=132) (actual time=81595.178..86573.228 rows=536 loops=1) -> Sort (cost=117865.77..119220.13 rows=541741 width=132) (actual time=81595.169..84412.069 rows=541741 loops=1) Sort Key: "version" -> Seq Scan on vers (cost=0.00..21367.41 rows=541741 width=132) (actual time=10.068..7397.374 rows=541741 loops=1) Filter: ("version" IS NOT NULL) Total runtime: 86647.495 ms (6 rows) In response to Tom Lane, I have compared a select/order by on the same data in Oracle and PG to see if this changes things: PG: Time: 67438.536 ms 541741 rows Oracle: After an hour and a half I canned it So it seems the idea that oracle is dropping duplicate rows prior to the sort when using distinct may indeed be the case. >From what I've seen here, it seems that PGs on-disk sort performance is exceeding that of Oracle - it's just that oracle sorts fewer rows for distinct. ___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend