Is it safe to run EXPLAIN ANALYZE on an UPDATE or DELETE?
No, not without care. EXPLAIN ANALYZE really executes the statement, so the rows are changed. Wrap it in BEGIN and ROLLBACK to inspect the plan and timings without keeping the change.
Explain it simply
Plain EXPLAIN is like asking a tailor for a quote: nothing is stitched. EXPLAIN ANALYZE is like asking the tailor to actually make the shirt so you can time it. For a SELECT that is harmless. For an UPDATE or DELETE, the data is genuinely modified.
Also, any triggers fire and locks are taken, exactly as in a real run.
Example
-- Dangerous on production
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
-- Safe pattern
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK; -- the delete is undone
-- Plan only, nothing runs
EXPLAIN DELETE FROM orders WHERE status = 'cancelled';The rolled-back delete still did the work, so it can load the server and leave dead rows for VACUUM to clean. Run it on a copy, or off-peak, if the statement is heavy.
Inside a transaction, the EXPLAIN timing for a write includes trigger time, which is useful when a slow delete is caused by foreign key checks.
What to say in the interview
"EXPLAIN ANALYZE executes the statement. For writes I wrap it in BEGIN and ROLLBACK, or just use plain EXPLAIN to see the estimated plan. Even a rolled-back run uses I/O and creates dead tuples."
Common mistakes
- Running EXPLAIN ANALYZE UPDATE directly on a live table and keeping the changes.