Re: Sort performance on large tables

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