From: Paul Lambert <paul.lambert@reynolds.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: Optimising a query
Date: Thu, 20 Dec 2007 06:55:58 +0900
Message-ID: <4769936E.1060709@reynolds.com.au> (raw)
In-Reply-To: <87wsrbhv83.fsf@oxford.xeocode.com>
References: <4768B4D1.1090702@reynolds.com.au>
<4768BE4B.9080308@reynolds.com.au>
<4768DAC0.4010304@archonet.com>
<87wsrbhv83.fsf@oxford.xeocode.com>
Gregory Stark wrote:
> "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.
>
I changed work_mem to 20MB per suggestion and that knocks the query time
down to just over 6 seconds... still a bit fast for my liking, but any
higher work_mem doesn't change the result - i.e. 30, 40, 50MB all give
just over 6 seconds.
The explain analyze shows all the sorts taking place in memory now as
quicksorts rather than on-disk merge in the previous query plan, so I'll
make a permanent change to the config to set work_mem to 20MB.
I've also changed the inner-most select into a two level select with the
lpad's on the outer so they are not being evaluated on every row, just
the collapsed rows - that accounted for about 1 second of the overall
time reduction.
Would increasing the stats of anything on any of these tables speed
things up any more?
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
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: paul.lambert@reynolds.com.au
Subject: Re: Optimising a query
In-Reply-To: <4769936E.1060709@reynolds.com.au>
* 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