> 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
