From: Paul Lambert <paul.lambert@reynolds.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: Optimising a query
Date: Wed, 19 Dec 2007 15:46:35 +0900
Message-ID: <4768BE4B.9080308@reynolds.com.au> (raw)
In-Reply-To: <4768B4D1.1090702@reynolds.com.au>
References: <4768B4D1.1090702@reynolds.com.au>
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
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: <4768BE4B.9080308@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