Re: Optimising a query

Gregory Stark wrote:
> "Richard Huxton" <dev@archonet.com> 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