I'm trying to migrate an application from an Oracle
backend to PostgreSQL and have a performance question.
The hardware for the database is the same, a SunFire
v120, 2x73GB U2W SCSI disks, 1GB RAM, 650MHz US-IIe
CPU. Running Solaris 8.
The table in question has 541741 rows. Under Oracle,
the query ' select distinct version from vers where
version is not null ' returns 534 rows in 6.14
seconds, with an execution plan showing a table scan
of vers followed by a sort.
The explain output on postgres shows the same
execution with a scan on vers and a sort but the query
time is 78.6 seconds.
The explain output from PostgreSQL is:
QUERY PLAN
---------------------------------------------------------------------------------
Unique (cost=117865.77..120574.48 rows=142
width=132)
-> Sort (cost=117865.77..119220.13 rows=541741
width=132)
Sort Key: "version"
-> Seq Scan on vers (cost=0.00..21367.41
rows=541741 width=132)
Filter: ("version" IS NOT NULL)
I do have an index on the column in question but
neither oracle nor postgresql choose to use it (which
given that we're visiting all rows is perhaps not
surprising).
I'm not as familiar with postgresql as I am with
Oracle but I think I've configured comparible
buffering and sort area sizes, certainly there isn't
much physical IO going on in either case.
What can I do to speed up this query? Other queries
are slightly slower than under Oracle on the same
hardware but nothing like this.
Thanks!
G
___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to
expressyourself http://uk.messenger.yahoo.com
