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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
