Re: Optimising a query

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