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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 6 of 8 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 18, '04 at 11:32a
activeJun 18, '04 at 11:06p
posts8
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase