6

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.

Improve this question
3
  • 1
    What kills them after several hours, before they complete? It seems like that would be the best place to log the plan.
    – jjanes
    Feb 10, 2017 at 17:26
  • We end up having to kill the pid. We know which query froze, but we don't have the plan.
    – Andrew
    Feb 10, 2017 at 20:30
  • Unfortunately there is no equivalent on postgres. You can take query text from server logfiles, and run EXPLAIN on it. That's all.
    – filiprem
    Sep 22, 2017 at 16:36

2 Answers 2

Reset to default
1

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!

Improve this answer
0

To be honest, logging long-running queries seems via cron does seem like the most KISS solution to me:

psql -tc "select now() as t, pid, usename, query from pg_stat_activity where state != 'idle'" > /tmp/pg.running.txt

Then you can spot the queries that stay there for several hours and run EXPLAIN on them.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.