Paul Lambert wrote:
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.
Well, if you need to summarise all the rows then that plan is as good as
any.
If you run this query very frequently, you'll probably want to look into
keeping a summary table updated via triggers.
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). Don't go overboard though, your big
query will probably use multiples of that value.
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.
Stick the lpads in a query that wraps your DISTINCT query.
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.
A CLUSTER <pkey-index> ON <table> might help, but it will degrade as you
update the finbalance table.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
