Try increasing sort_mem temporarily, and see if that
makes a difference:
SET sort_mem = 64000;
EXPLAIN ANALYSE ...
makes a difference:
SET sort_mem = 64000;
EXPLAIN ANALYSE ...
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
