Log plans for queries that never finish We have an UPDATE in our 2am nightly load that has on two occasions pegged CPU at 100% and never completed after many hours. I strongly suspect that this is happening because of inaccurate statistics causing nested loops in a join. I am not able to reproduce it in lower environments with the same hardware specs and postgres configuration.

I need to prove that the nested loops are occurring before I can put in an RFC to address the problem (e.g. adjusting analyze and autovacuum settings). I have setup auto_explain but this only seems to capture plans for queries that actually finish. How can I log plans for queries that do not finish?

In SQL Server I would be able to do this by running sp_whoisactive with @get_plans=1. I am looking for something similar in Postgres. The only idea I've had so far would be to run EXPLAINs using cron, but this seems very hacked together.
The auto_explain feature seems to fit this use case, if you can take a brief service outage to enable it. It logs a plan for any statement exceeding a time threshold whether or not it has completed yet. It can also log plans before execution starts, so you can even get the plan of a statement that blows out memory!