Aaron Burnett wrote:
>>> I run a handful of queries overnight when traffic is at it's lowest
on our
>>> system. One particular query will run perfectly fine (around 5
seconds0)
>>> for several weeks, then suddenly decide to hang indefinitely and
never
>>> finish. It needs to be killed manually after several hours (I've
>>> intentionally let it run to see if it would ever finish) in order
for it
>>> to die.
>>>
>>> The fix _seems to be_ to drop and rebuild the index on
>>> xrefchannelmember.member_id. The query then goes back to running in
the 5
>>> seconds and has no problem again for weeks.... until it happens
again.
>> Is the machine busy processing the query or is it idle?
> It is processing and in fact drives the load up a bit.
>> Are there any other queries that may be locking your tables (check
>> pg_locks)?
> I did check, and no, no locks.
Try to get the execution plan of the query when it takes long and
see how it compares to the plan when it runs ok.
Yours,
Laurenz Albe