> Try increasing sort_mem temporarily, and see if that
> makes a difference:
>    SET sort_mem = 64000;

I did this (actualy 65536) and got the following:
pvcsdb=# explain analyze select distinct version from
vers where version is not null;
 Unique  (cost=117865.77..120574.48 rows=142
width=132) (actual time=81595.178..86573.228 rows=536
   ->  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

Reply via email to