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:47:24 +0000
Message-ID: <87wsrbhv83.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>
"Richard Huxton" <dev@archonet.com> writes:
> Paul Lambert wrote:
>
>> " -> Sort (cost=30197.98..30714.85 rows=206748 width=16) (actual >> time=5949.691..7018.931 rows=206748 loops=1)"
>> " Sort Key: dealer_id, year_id, subledger_id, account_id"
>> " Sort Method: external merge Disk: 8880kB"
> Before that though, try issuing a "SET work_mem = '9MB'" before running your
> query. If that doesn't change the plan step up gradually. You should be able to
> get the sort stage to happen in RAM rather than on disk (see "Sort Method"
> above).
FWIW you'll probably need more than that. Try something more like 20MB.
Also, note you can change this with SET for just this connection and even just
this query and then reset it to the normal value (or use SET LOCAL). You don't
have to change it in the config file and restart the whole server.
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.
--
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: <87wsrbhv83.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