Re: How to debug a connection that's "active" but hanging?

Jurrie Overgoor <postgresql-mailinglist(at)jurr(dot)org> writes:
> Hi Vijaykumar, thanks for replying. Yes, this is reproducible. About 50%
> of the times, my connection is hanging. It's always on the same query,
> which I shared in the previous post.

The backtraces you captured look like the query is not "hung", it's
just computing away.

A plausible interpretation of the facts you've given is that the
query's plan is unstable, and sometimes the server is choosing a plan
that takes much longer to run than other times. Ordinarily I'd suggest
that the auto_explain extension might help you debug that, but I think
it only works on queries that do eventually complete. You might need
to investigate by altering your application to capture "EXPLAIN ..."
output just before the troublesome query, so you can see if it gets
a different plan in the slow cases.

regards, tom lane