Message-ID: <4769936E.1060709@reynolds.com.au>
Date: Thu, 20 Dec 2007 06:55:58 +0900
From: Paul Lambert <paul.lambert@reynolds.com.au>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Optimising a query
References: <4768B4D1.1090702@reynolds.com.au>
	<4768BE4B.9080308@reynolds.com.au>
	<4768DAC0.4010304@archonet.com> <87wsrbhv83.fsf@oxford.xeocode.com>
In-Reply-To: <87wsrbhv83.fsf@oxford.xeocode.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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