From: Gregory Stark <stark@enterprisedb.com>
To: Richard Huxton <dev@archonet.com>
Cc: Paul Lambert <paul.lambert@reynolds.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimising a query
Date: Wed, 19 Dec 2007 09:50:29 +0000
Message-ID: <87ve6vhv2y.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <4768DAC0.4010304@archonet.com>
References: <4768B4D1.1090702@reynolds.com.au>
<4768BE4B.9080308@reynolds.com.au>
<4768DAC0.4010304@archonet.com>
> Also, try replacing the DISTINCT with GROUP BY. The code path for DISTINCT
> unfortunately needs a bit of cleaning up and isn't exactly equivalent to GROUP
> BY. In particular it doesn't support hash aggregates which, if your work_mem
> is large enough, might work for you here.
Sorry, strike that last suggestion. I was looking at the plan and forgot that
the query had DISTINCT ON. It is possible to replace DISTINCT ON with GROUP BY
but it's not going to be faster than the DISTINCT ON case since you'll need
the sort anyways.
Actually it's possible to do without the sort if you write some fancy
aggregate functions but for this large a query that's going to be awfully
complex.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
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: stark@enterprisedb.com, dev@archonet.com, paul.lambert@reynolds.com.au
Subject: Re: Optimising a query
In-Reply-To: <87ve6vhv2y.fsf@oxford.xeocode.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