Simple select query hangs

Question

I have a simple query:

SELECT a.name AS a_name,
       b.description AS b_description
    FROM a
    JOIN b ON a.id = b.a_id
    WHERE b.table_name IS NULL;

(Names changed to protect the innocent)

This usually takes ~3ms to run. Sometimes, this query hangs. It has currently been hanging for 3 days. Luckily, I'm in a position where I can try and debug this in its current state, for a while. It appears there's nothing blocking it (found by querying pg_locks and pg_stat_activity), though it is blocking other things.

Even with the other query in a hung state, I can run the same query again, and it completes successfully in the expected time period.

Things that are likely to be involved: we currently have two cron jobs (don't ask) that dump the database. I can find the exact commands being run if it's likely to be relevant.

Any suggestions on how to debug this gratefully received!

Answer 1

Check the wait events in pg_stat_activity to see if your query is running or hanging.

In the latter case, examine pg_locks or use the pg_blocking_pids function to see which session blocks you.

If no locks are involved, see if the backend process consumes CPU or not. Optionally use strace to see what the backend is doing.