Explain

Session A holds a lock across a blocked connection to waiting session B; observer C points toward the waiting side.

A slower query needs both a plan and a wait check

A formerly fast query can become slow with the same data, statistics, and access plan. Another transaction may hold a lock, the storage cache may be colder, or the application may wait before PostgreSQL even receives the statement. Rechecking the plan is useful, but it belongs inside a broader comparison of where the elapsed time went. Begin by distinguishing time spent doing work from time spent waiting to do it. ...

EXPLAIN ANALYZE performs real work. After savepoint rollback, table changes are restored, but a consumed sequence value remains consumed. The article example is illustrated, not rerun.

EXPLAIN ANALYZE runs the query to show real cost

EXPLAIN describes a PostgreSQL execution plan; EXPLAIN ANALYZE also executes the statement and measures its work. The useful comparison is between estimated rows and observed rows at the same node. Neither number is a verdict by itself: an estimate is a model, while a measurement describes one execution under particular data, cache, concurrency, and instrumentation conditions. ...

Representative plans compare Limit over Sort over Scan with Limit over ordered index access. Both preserve the same ten tenant-17 IDs. The index is commonly used, but exact plan shape varies; the illustration was not rerun.

EXPLAIN shows how Postgres will do the work

EXPLAIN prints the planner’s proposed execution plan; EXPLAIN ANALYZE executes the statement and adds measured rows and times. A slow query needs both a hypothesis and a measurement. Save the query parameters, PostgreSQL version, and original plan before changing an index or rewriting a join. Those details make the next plan comparable and prevent an improvement on one input from hiding a regression on another. ...