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).
Can you post explain analyze for the same query? It contains actual numbers alond side the chosen plan.
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.
Well, for postgresql you should check out
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
HTH
Shridhar
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org