Hi Simon, Thanks for the response Simon.
PostgreSQL can do HashAggregates as well as GroupAggregates, just like Oracle. HashAggs avoid the sort phase, so would improve performance considerably. The difference in performance you are getting is because of the different plan used. Did you specifically do anything to Oracle to help it get that plan, or was it a pure out-of-the-box install (or maybe even a "set this up for Data Warehousing" install)?
It was an out-of-the-box plan with the standard database install option (wasn't a Data Warehousing install).
Can you let us know how high you have to set work_mem before an EXPLAIN (not EXPLAIN ANALYZE) chooses the HashAgg plan?
The planner picked a HashAggregate only when I set work_mem to 2097151 - which I gather is the maximum allowed value according to a message returned from the server.
Please be aware that publishing Oracle performance results is against the terms of their licence and we seek to be both fair and legitimate, especially within this public discussion forum.
Sorry, I didn't realize - I'll be more vague next time. Charlie ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
