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.