A Query That Is Slow Only Under Load Is Usually Waiting, Not Working
Some queries are fast when you run them by hand and slow in production, and that gap almost always means time spent waiting rather than time spent computing: waiting for a row lock, for a free connection, for disk, or for a CPU that other sessions are using. EXPLAIN ANALYZE on your own quiet session will not reproduce it, because the thing you are waiting for is other traffic. The diagnosis is to catch the query while it is slow and ask the database what it is waiting on.
Questions this Concept answers
Why will `EXPLAIN ANALYZE` in your own quiet session fail to reproduce a load-only slowdown?
J
jeremy
Text
Diagnosing Lock Contention: who blocks whom in the database
Sometimes a query is slow not because of its plan but because it is waiting for another transaction to let go of a row. The give-away is high response times while the machine looks idle and the same …