Sample questions
Pg Query PlanningDifficulty 1
A developer runs EXPLAIN on a SELECT query, then runs EXPLAIN ANALYZE on the exact same query. What is the core difference between the two?
- aEXPLAIN shows only the estimated plan; EXPLAIN ANALYZE actually runs the query and adds real timings✓
- bEXPLAIN ANALYZE just formats the same plan more readably; neither one runs the query
- cEXPLAIN runs the query and returns its result rows, while EXPLAIN ANALYZE only shows the estimated plan
- dBoth run the query the same way; the only difference is whether the output is JSON or plain text
Explanation:EXPLAIN asks the planner for a plan and shows its cost/row estimates without executing anything. EXPLAIN ANALYZE actually executes the statement, then annotates each node with real elapsed time and real row counts alongside the estimates, so you can compare estimate vs. reality. Neither (b) nor (c) matches this, and (d) ignores that EXPLAIN alone never executes the query.
Pg Query PlanningDifficulty 2
A teammate wants to see the real execution plan of UPDATE orders SET status = 'paid' WHERE customer_id = 42; so they run EXPLAIN ANALYZE directly on it against the live database. What actually happens?
- aPostgreSQL detects the statement is a write and silently downgrades it to a read-only plan estimate
- bThe UPDATE runs and commits as usual; EXPLAIN ANALYZE does not roll it back on its own✓
- cPostgreSQL refuses with a syntax error, since EXPLAIN ANALYZE only accepts SELECT statements
- dThe rows are locked for the duration of the EXPLAIN but no write is ever applied to the table
Explanation:EXPLAIN ANALYZE executes the statement for real to gather actual timings and row counts — for UPDATE/DELETE/INSERT this means the data is really changed and, absent an explicit transaction, committed immediately. It is not read-only (a), it is valid syntax for DML (c), and it is not a lock-only dry run (d). To inspect the plan of a write safely, wrap it in BEGIN; ... ROLLBACK;.
Pg Query PlanningDifficulty 3
You need to see the real EXPLAIN ANALYZE plan for DELETE FROM orders WHERE status = 'cancelled'; on a table with real data, without permanently losing those rows. Real run: before the DELETE status = 'cancelled' had 50000 rows; right after EXPLAIN ANALYZE DELETE ... it had 0; after issuing ROLLBACK, it was back to 50000. Which approach was used?
- aRunning
EXPLAIN instead of EXPLAIN ANALYZE, since EXPLAIN alone never touches data - bTaking a full table backup first, running the DELETE, then restoring the backup afterward
- cOpen a transaction, run
EXPLAIN ANALYZE DELETE ... inside it, then ROLLBACK instead of COMMIT✓ - dRunning the DELETE on a read replica, since replicas allow EXPLAIN ANALYZE to be non-destructive
Explanation:EXPLAIN ANALYZE on a DELETE truly deletes the rows; the only safe way to inspect the real plan without keeping the change is BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK; — the statement runs for real inside the transaction (so the plan and timings are genuine) and the rollback undoes the effect. (a) would give no real timings/actual rows at all. (b) works but is a needlessly heavy detour compared to a transaction. (d) is wrong because a read replica does not accept write statements.
Pg Query PlanningDifficulty 1
In an EXPLAIN line such as Seq Scan on orders (cost=0.00..3627.00 rows=200000 width=32), what does the cost=0.00..3627.00 part represent?
- aThe minimum and maximum number of milliseconds the query actually took to run
- bThe percentage of the query's total CPU time this node is expected to consume
- cThe estimated number of disk blocks that will be read from and written to for this node
- dThe planner's arbitrary cost units for startup and total cost, not a time measurement✓
Explanation:cost=startup..total is expressed in the planner's own arbitrary cost units (derived from settings like seq_page_cost/cpu_tuple_cost), used only to compare candidate plans against each other. It is never a millisecond value (a), a CPU percentage (b), or a block read/write count (c) — those require EXPLAIN ANALYZE with BUFFERS/actual timings instead.
Pg Query PlanningDifficulty 2
Real plan from
pgscratch:
Index Scan using orders_pkey on orders (cost=0.42..8.44 rows=1 width=32)
Index Cond: (id = 42)
What do the two numbers
0.42 and
8.44 in
cost=0.42..8.44 mean, in order?
- a8.44 is the cost of finding the first row, 0.42 is the cost of returning every row
- b0.42 is the estimated cost to reach the first row; 8.44 is the estimated cost for all rows✓
- c0.42 is the number of index pages read, 8.44 is the number of heap pages read
- d0.42 is the cost before ANALYZE was run, 8.44 is the cost after ANALYZE was run
Explanation:cost=X..Y is always startup_cost..total_cost: X is the estimated cost before the node can produce its very first output row, Y is the estimated cost to produce all of the node's output. For this single-row Index Cond lookup both numbers are naturally small. (a) reverses the order, (c) confuses cost units with page counts, and (d) invents an ANALYZE-before/after meaning that cost= does not carry.
Pg Query PlanningDifficulty 2
Real plan for
SELECT * FROM orders WHERE status = 'paid'; on a 200,000-row
orders table (an
orders_status_idx index exists on
status):
Bitmap Heap Scan on orders (cost=556.13..2802.22 rows=49527 width=32)
Recheck Cond: (status = 'paid'::text)
-> Bitmap Index Scan on orders_status_idx (cost=0.00..543.75 rows=49527 width=0)
Index Cond: (status = 'paid'::text)
Without running
EXPLAIN ANALYZE, what does
rows=49527 on the
Bitmap Heap Scan line represent?
- aThe planner's estimate from table statistics of matching rows — not a measured count✓
- bThe exact, already-measured number of rows that matched when this EXPLAIN was generated
- cThe total number of rows in the
orders table, regardless of the WHERE clause - dThe maximum number of rows PostgreSQL will return before applying an implicit LIMIT
Explanation:Plain EXPLAIN (no ANALYZE) never executes the query, so every rows= value on every node — including this one — is purely a planner estimate derived from column statistics (most-common-values, histogram, etc.), not a measured value. To see the real, executed row count you need EXPLAIN ANALYZE, where it appears as actual rows=.... (b) mislabels this as measured, (c) confuses it with the table's total row count (200,000), and (d) invents a LIMIT that isn't in the query.