EXPLAIN ANALYZE and its safety boundary · PostgreSQL

The name suggests inspection. The behaviour is execution.

The measurement

Data-loss riskEXPLAIN ANALYZE on a DELETE— Captured on 18.6. The table held 1000 rows before this statement.

$ psql -U postgres -c "SELECT count(*) FROM safety" -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) DELETE FROM safety WHERE id <= 500" -c "SELECT count(*) FROM safety"

 before_explain_analyze
------------------------
                 1000

Delete on safety (actual rows=0.00 loops=1)
 Buffers: shared hit=505
 ->  Seq Scan on safety (actual rows=500.00 loops=1)
       Filter: (id <= 500)
       Rows Removed by Filter: 500

after_explain_analyze
-----------------------
                  500

Five hundred rows gone. EXPLAIN ANALYZE is the statement plus instrumentation, not a simulation of it.

EXPLAIN without ANALYZE is safe: it plans and prints, and executes nothing.

The safe form

Read-only / Safethe same analysis with the write discarded— Captured on 18.6 immediately after the previous capture, so the starting count is 500.

$ psql -U postgres -c "SELECT count(*) FROM safety" -c "BEGIN" -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) DELETE FROM safety WHERE id > 900" -c "ROLLBACK" -c "SELECT count(*) FROM safety"

 before
--------
  500

... plan output ...

after_rollback
----------------
          500

Unchanged. This is the pattern to use for any write statement you need real numbers for:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'archived' WHERE created_at < '2024-01-01';
ROLLBACK;

Type the ROLLBACK before you type the EXPLAIN

The failure mode is not forgetting to open the transaction. It is opening it, running the EXPLAIN ANALYZE, being distracted by the output, and never issuing the ROLLBACK — leaving an idle in transaction session holding locks on the rows it just modified, on production, with everything from lesson VII-05 following.

In psql, compose the whole thing before executing any of it:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM …;
ROLLBACK;

paste it as one block, and let it run as one unit.

Better still, defend against yourself:

SET default_transaction_read_only = on;
-- now any write statement errors before it can execute
EXPLAIN (ANALYZE) DELETE FROM orders WHERE id = 1;
-- ERROR:  cannot execute DELETE in a read-only transaction

A read-only session cannot be talked into modifying anything, which is the right posture for a session opened to investigate a problem.

What a rollback does not undo

The transaction is reverted. Four things are not.

Sequence advances. nextval() is deliberately non-transactional, so that concurrent sessions never receive the same value. An EXPLAIN ANALYZE of an INSERT consumes sequence values permanently.

SELECT last_value FROM orders_id_seq;
BEGIN;
EXPLAIN (ANALYZE) INSERT INTO orders (…) SELECT … FROM staging;
ROLLBACK;
SELECT last_value FROM orders_id_seq;   -- advanced by the row count

Harmless in most schemas. Not harmless where the sequence feeds externally-visible identifiers such as invoice numbers, and impossible to explain afterwards if nobody knows an EXPLAIN was run.

External side effects. A trigger that writes to a foreign data wrapper, calls an extension that sends a notification, or writes to a file has already done so. Rollback governs this database, not the world.

Resource consumption. The statement ran. It read the pages, filled shared buffers, wrote WAL for the changes it made, dirtied pages that a checkpoint will flush, and generated dead tuples that vacuum must reclaim. On a large UPDATE this is real load, and the rollback does not give it back.

Locks, until the rollback happens. Between the EXPLAIN ANALYZE and the ROLLBACK, every row the statement touched is locked. That window is as long as you leave it.

On a production system, prefer the alternatives

EXPLAIN without ANALYZE answers most questions. It gives the plan shape, the estimates, and the chosen access methods. If the question is “why is it choosing a sequential scan”, this is sufficient.

A restored copy. If you need actual timings for a heavy write, run it on a recent restore. It also verifies the backup, which lessons XIII-01 and XIII-08 argue you should be doing anyway.

auto_explain captures plans of statements that ran naturally, which is strictly better evidence than a plan you produced by hand under different conditions.

-- postgresql.conf: shared_preload_libraries = 'auto_explain'
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_timing = off;
ALTER SYSTEM SET auto_explain.log_nested_statements = on;

log_analyze = on instruments every statement that qualifies, so log_timing = off is worth pairing with it to remove the per-node clock cost. log_nested_statements captures statements inside functions, which are otherwise invisible and are frequently where the time is.

Start with a high log_min_duration and lower it while watching the overhead.

`EXPLAIN` on a parameterised query

A query the application runs with parameters may not plan the same way when you paste literals into psql. The planner sees the literal and uses the exact value against the statistics; with a parameter it may produce a generic plan instead.

PREPARE q (int) AS SELECT * FROM orders WHERE customer_id = $1;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q (42);

Executing a prepared statement several times is what makes PostgreSQL consider switching to a generic plan, so run it more than once if you are investigating a plan that only misbehaves in production.

Generic plans, custom plans, and the query that is fast in psql

A PREPAREd statement — which every driver using the extended protocol creates, whether or not the application writes PREPARE — can be executed with a custom plan, planned afresh for the actual parameter values, or a generic plan, planned once for any values.

PostgreSQL’s rule: the first five executions always use a custom plan. From the sixth, it compares the average cost of those custom plans against the cost of a generic plan, and if the generic plan is not appreciably more expensive it switches to it permanently, saving the planning work.

Read-only / Safethe switch, visible in the Index Cond— EXECUTION VALIDATED on 18.6. The literal value in the condition is the tell: a custom plan was planned for that parameter, a generic plan was not.

$ PREPARE p1(int) AS SELECT * FROM pgbench_accounts WHERE aid = $1;
EXPLAIN EXECUTE p1(1);   -- ... through p1(7)

--- execution 1 ---
 Index Cond: (aid = 1)
--- executions 2 to 5 ---
 Index Cond: (aid = 2)
 Index Cond: (aid = 3)
 Index Cond: (aid = 4)
 Index Cond: (aid = 5)
--- execution 6 ---
 Index Cond: (aid = $1)          <- GENERIC PLAN
--- execution 7 ---
 Index Cond: (aid = $1)

The failure this produces is specific and maddening. A query filtering on a heavily skewed column — 99% of rows have status = 'complete', 1% have 'pending' — has a custom plan for 'pending' that uses an index and a custom plan for 'complete' that scans. The generic plan uses the average selectivity and may pick the wrong one for both.

The symptom is a query that is fast in psql and slow from the application, on identical data, because psql with a literal always gets a custom plan.

Two levers.

-- force one or the other, session-wide
SET plan_cache_mode = 'force_custom_plan';    -- replan every time
SET plan_cache_mode = 'force_generic_plan';   -- never replan
SET plan_cache_mode = 'auto';                 -- the default behaviour

force_custom_plan costs planning time on every execution and is frequently worth it on a skewed column. It can be set per role, which is usually the right scope:

ALTER ROLE app_user SET plan_cache_mode = 'force_custom_plan';

To see which you are getting, run EXPLAIN (ANALYZE) EXECUTE … six or more times and watch for the plan changing on the sixth. A plan containing $1 rather than a literal in its conditions is a generic plan.

What to take from this

EXPLAIN ANALYZE executes the statement. Measured: it deleted 500 rows.
BEGIN … ROLLBACK is the safe form. Type the ROLLBACK first.
SET default_transaction_read_only = on makes an investigation session incapable of writing.
Rollback does not undo sequence advances, external side effects, resource consumption or the locks held meanwhile.
On production prefer plain EXPLAIN, a restored copy, or auto_explain.
A query fast in psql and slow from the application is often a generic plan.

Cross-course references

Git, CI/CD & GitOps — Part LVII (Approval gates) covers why running EXPLAIN ANALYZE on a writing statement in production is a change, not an investigation, and belongs behind the same gate.
Observability for Production Sysadmins — Part CIX (Incident investigation workflows) covers capturing a plan as evidence with the timestamp and the settings that produced it.