Gregory Stark wrote:
"Richard Huxton" <dev@xxxxxxxxxxxx> 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
---------------------------(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
