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 express yourself http://uk.messenger.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match