Message-ID: <4768BE4B.9080308@reynolds.com.au>
Date: Wed, 19 Dec 2007 15:46:35 +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>
In-Reply-To: <4768B4D1.1090702@reynolds.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Paul Lambert wrote:
> <snip> 


This part of the query alone takes a significant part of the time:

SELECT DISTINCT ON (finbalance.dealer_id, finbalance.year_id, 
finbalance.subledger_id, finbalance.account_id)
			finbalance.year_id AS year,
			finbalance.dealer_id AS dealer_id,
			lpad(finbalance.subledger_id::text,4,'0') AS subledger,
			lpad(finbalance.account_id::text,4,'0') AS account
		FROM finbalance

Runs with a query plan of :

"Unique  (cost=30197.98..32782.33 rows=20675 width=16) (actual 
time=5949.695..7197.475 rows=17227 loops=1)"
"  ->  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"
"        ->  Seq Scan on finbalance  (cost=0.00..8409.70 rows=206748 
width=16) (actual time=0.042..617.949 rows=206748 loops=1)"
"Total runtime: 7210.966 ms"


So basically selecting from the finbalance table (approx. 206,000 
records) takes 10 seconds, even longer without the distinct clause in 
there - the distinct collapses the result-set down to around 17,000 rows.

Taking out the two lpad's in there knocks off about 1500ms, so I can 
come up with something else for them - but I'd like to get the query as 
a whole down to under a second.

dealer_id, year_id, subledger_id and account_id are all part of the 
primary key on the finbalance table, so I don't think I can index them 
down any further.

Are there any config settings that would make it faster...

I'm running on a Quad-core pentium Xeon 1.6GHZ server with 4GB RAM. I 
imagine shared_buffers (32MB) and work_mem (1MB) could be bumped up a 
good bit more with 4GB of available RAM?


-- 
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company