Re: odd intermittent query hanging issue

On 05/18/2012 11:01 AM, Aaron Burnett wrote:
>>> ... One particular query will run perfectly fine (around 5 seconds)
>>> for several weeks, then suddenly decide to hang indefinitely and never
>>> finish....
>> Is the machine busy processing the query or is it idle?
>
> It is processing and in fact drives the load up a bit.
What CPU, disk and memory is it using? It would be very interesting to
see the query plan when things go South.

> Yeah, the query is poo... autogenerated... the LEFT JOIN is not needed as I have pointed out to the person responsible for the code many times, and
> the 'in(1)' may indeed have many categories in there. But the OLY one that
> hangs is the 'in(1)'

Is "1" the largest category? Also, how have you tuned work_mem (show
work_mem;)?

When the query gets bad do you see PostgreSQL swapping to temp files
(watch files in PGDATA/base/DB_OID/pgsql_tmp). Note that work_mem can be
set per-connection so you if it is too small for your nighttime
maintenance you can adjust it for those operations only.

Cheers,
Steve