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.

Keep a comparable baseline

Record the statement shape, representative parameter values, returned row count, relevant session settings, and whether the application uses a prepared statement. Different parameters can produce very different populations. A manual query with a literal may not reproduce a prepared statement’s generic plan. A different role or search path can also change which objects and permissions apply.

Inspect current activity while the slowdown is happening. pg_stat_activity can show an active query with a lock wait; active does not mean continuously consuming CPU. pg_blocking_pids identifies sessions currently blocking that backend. Visibility into other roles’ activity requires appropriate privileges, so missing details are not evidence that nothing is running. Use fresh autocommit observations rather than repeatedly reading a cached monitoring snapshot inside one long transaction.

When it breaks

Blindly running ANALYZE treats every slowdown as a stale-statistics problem. That can add maintenance work while leaving the actual blocker untouched. Likewise, building an index on a one-row table cannot release another transaction’s row lock. Accurate estimated rows and a tiny sequential scan can coexist with arbitrarily long application waits.

EXPLAIN ANALYZE is an execution, including for writes, and can itself block. A broad update copied from production is not a harmless inspection command. First inspect waiting and lock ownership; measure a safe fixture or a statement whose effects are understood. Do not terminate an unfamiliar transaction merely because its identifier appears in the blocker list.

Check it quickly

Open three fresh psql sessions, A, B, and C, using the same role and disposable PostgreSQL 18 database with normal autocommit enabled. The named schema must not already exist. Keep the session roles separate; pasting every block into one connection cannot demonstrate blocking between transactions.

  1. In A, create the fixture, then begin a transaction that changes its only row without committing.
\set ON_ERROR_STOP on
CREATE SCHEMA query_recheck_demo;
CREATE TABLE query_recheck_demo.items (id integer PRIMARY KEY,value integer);
INSERT INTO query_recheck_demo.items VALUES (1,0);
ANALYZE query_recheck_demo.items;

BEGIN;
UPDATE query_recheck_demo.items SET value=10 WHERE id=1;
SELECT pg_backend_pid() AS blocker_pid;
  1. In B, start the measured update. It waits until A ends its transaction or the sixty-second statement timeout expires.
SET application_name='xbcatm_recheck_waiter';
SET statement_timeout='60s';
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
UPDATE query_recheck_demo.items SET value=value+1 WHERE id=1;
  1. While B is waiting, run this in C, before the timeout. The test application name identifies B without exposing its complete query text.
SELECT pid,state,wait_event_type,wait_event,pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity WHERE application_name='xbcatm_recheck_waiter';
  1. In A, undo the uncommitted change and release its lock. Then collect B’s completed plan.
ROLLBACK;
  1. After B finishes successfully, use C to verify the stored value and remove the fixture.
SELECT value FROM query_recheck_demo.items WHERE id=1;
DROP SCHEMA query_recheck_demo CASCADE;

The PostgreSQL 18.6 observation showed B as active with wait_event_type=Lock, wait_event=transactionid, and A’s process identifier in blockers. The wait-event detail can depend on the exact contention; the blocking relationship is the central evidence. After A rolled back, B incremented the original zero to one. The final query returned one, and cleanup removed the schema.

B’s scan estimated and read one row. Its top Update node returned zero rows because there was no RETURNING clause, not because it changed nothing. Execution time includes the induced wait and depends on how quickly A releases it. If the timeout occurs first, roll back A and use C’s final block to remove the fixture before restarting. The canceled update did not complete its increment.

Follow the observed cause

For a lock wait, investigate transaction lifetime and ownership before changing access paths. For excessive processed rows, compare selectivity and current statistics. For sorts or hashes that spill, inspect their memory and temporary I/O. Finally, compare database execution with application latency to expose connection or client delays. A new plan is meaningful only when it explains the measured regression and preserves the required result.

Sources