Re: Random slow queries

devel(dot)brain99(at)xoxy(dot)net writes:
> As you can see from the logs I posted, it appears the execution plan was
> cached (LOG: duration: 122006.000 ms bind cached-1453392550: select....).
> Maybe those aren't processed by auto_explain?

In that, "cached-1453392550" is a statement name given by the client;
you'd know better than we do where it's coming from, but it has no
particular significance to the server.

The real information here is that what is taking 122 seconds is the BIND
step of extended query protocol. That explains why auto_explain doesn't
notice it; auto_explain only instruments the execution phase. Typically,
what takes time in the BIND step is planning the query, so it seems like
we have to conclude that something in planning is getting hung up. That
doesn't get us very much closer to an explanation though :-(.

Don't know if it would be practical for you at all, but if you could
attach to a process that's stuck like this with a debugger and get a stack
trace, that would probably be very informative.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane