From: Charlie Savage <cfis@interserv.com>
To: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: Sort performance on large tables
Date: Wed, 09 Nov 2005 10:13:46 -0700
Message-ID: <43722E4A.8060904@interserv.com> (raw)
In-Reply-To: <1131528955.8300.2101.camel@localhost.localdomain>
References: <43704E1D.80708@interserv.com>
<1131528955.8300.2101.camel@localhost.localdomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cfis@interserv.com, simon@2ndquadrant.com
Subject: Re: Sort performance on large tables
In-Reply-To: <43722E4A.8060904@interserv.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox