From: Charlie Savage <cfis@interserv.com>
Subject: Re: Sort performance on large tables
Date: Wed, 09 Nov 2005 10:13:46 -0700
Organization: Hub.Org Networking Services
Lines: 29
Message-ID: <43722E4A.8060904@interserv.com>
References: <43704E1D.80708@interserv.com>
	<1131528955.8300.2101.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
To: Simon Riggs <simon@2ndquadrant.com>
User-Agent: Thunderbird 1.5 (Windows/20051025)
In-Reply-To: <1131528955.8300.2101.camel@localhost.localdomain>
To: pgsql-performance@postgresql.org

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