Query Planning — EXPLAIN, Execution Plans, Tuning

Why this session exists

A query returning ten rows can still scan millions, build a large hash table, or repeat a cheap lookup thousands of times. An index is one possible remedy, not a diagnosis. The objective is to read PostgreSQL 16 plans, distinguish predictions from observations, identify excessive work, and choose a semantics-preserving change. We also translate the ideas to MySQL 8.4, SQL Server and BigQuery rather than pretending their cost units are interchangeable.

By the end, you should be able to trace rows through scan/join/aggregate nodes, investigate cardinality errors, explain six common expensive shapes, select an index family, and propose a safe measurement. A large estimation error is a clue, not proof that the query or statistics are defective.

Prerequisites

Use the bag/cardinality reasoning from SQL joins, the access paths from indexes, and the side-effect/isolation boundaries from transactions. S040 is CTEs, not the joins lesson. Isolation affects visible rows and locking; it is not a simple switch selecting an index.

The GPS that never drove the route

An optimizer resembles a route planner using summaries of roads rather than driving every possible route. PostgreSQL considers alternatives using page/CPU cost parameters, row-count estimates, widths, available indexes and join orders. It does not enumerate every possible plan without limit, and a cheapest estimated plan need not be the fastest observed one. Stale statistics can matter, but fresh statistics still summarize rather than perfectly describe the data.

EXPLAIN plans without executing the statement. EXPLAIN ANALYZE executes and annotates the plan with observed rows, loops and timing. Costs are arbitrary planner units, conventionally related to a sequential page fetch, not milliseconds. Compare costs under the same settings and assumptions; actual latency also depends on cache state, contention, I/O and instrumentation.

For write statements, rollback is not a universal safety net: sequence increments and external effects from functions may survive it, and locks/work still happen. Even a SELECT can call a side-effecting function. Use disposable fixtures and a statement timeout; inspect plain EXPLAIN first. Never upload an employer's plan containing SQL literals, identifiers or other private data to a public visualizer without authorization and sanitization.

History without invented milestones

System R's 1979 access-path work helped establish cost-based optimization; it did not make every later optimizer identical. PostgreSQL's version-11 release notes document JIT in 2018, not in PostgreSQL 9.5. PostgreSQL 6.0 was not a 1993 release. The important evolution is from static estimates to richer statistics, execution instrumentation and, in some other engines, runtime adaptation. A graphical plan is a presentation of evidence, not a guarantee of correct estimates. Dates not established by the cited primary release are not prerequisites to interpreting a plan.

Tracing rows through a query plan

Read from scans toward the root, then inspect the root to understand total output. This is a schematic, not captured PostgreSQL output; no timing can be inferred from the red or largest-looking node.

Suppose a scan estimates 200 rows but produces 2,000,000: the ratio is 10,000. A join chosen expecting 200 probes can be expensive at two million. However, a sequential scan with estimated 10,000,000 and actual 10,048,712 is close in cardinality; that comparison alone says nothing about whether an index helps or whether the scan consumes 95% of runtime.

Anatomy of an EXPLAIN ANALYZE plan

For a simplified node (cost=0.42..85.00 rows=200 width=40) (actual time=0.02..0.10 rows=3 loops=100000), startup cost precedes total cost; rows estimates output per execution, not all rows examined. Actual time and rows are averages per loop. Rough repeated elapsed work is 0.10 × 100000 = 10000 ms, and output is roughly 3 × 100000 = 300000 rows. Rounded output, short-circuiting and parallel plans need additional care.

Node timing includes child execution. Adding parent and child totals double-counts work; subtracting them is only an approximation, especially with parallel overlap. Rows Removed by Filter helps explain rows read but not returned. LIMIT may stop a child early, so a low observed row count is not automatically a faulty full-scan estimate. Record plan settings, parameter values, buffers, loops and execution context before declaring a bottleneck.

BUFFERS distinguishes shared/local/temp activity. A shared hit is served from PostgreSQL shared buffers. A shared read is loaded into those buffers and may come from the operating-system cache, not necessarily physical disk. Temp reads/writes can expose spill. These are block-access counts, not counts of distinct blocks or elapsed-time attribution. The plain EXPLAIN misconception is thus false: its costs predict; ANALYZE measures an execution with instrumentation overhead.

Repetition, filtering and the actual local engine

Compare the estimated 200 rows with actual 3 per loop, not with 300,000 accumulated output rows: the per-execution estimate is about 66.7 times too high while the outer plan causes 100,000 executions. Those are two separate diagnoses. Rows Removed by Filter, when reported for repeated PostgreSQL nodes, is also a per-loop average (rounded in text output). Buffer counters are accumulated block accesses, not another per-loop row average; do not multiply them by loops or sum parent and child counters indiscriminately. Node time includes children and is not exclusive operator CPU time.

The installed SQLite fixture produces a SCAN orders before the status index and a SEARCH orders USING COVERING INDEX ix_status (status=?) after it. The result stays forty IDs; the covering path can supply id through the rowid carried by the index because this table's id INTEGER PRIMARY KEY aliases rowid. These are recorded SQLite observations, not PostgreSQL plan output. SQLite EXPLAIN QUERY PLAN supplies a strategy tree, not PostgreSQL's numeric cost/actual-time/loops fields; its text format is explicitly unstable. Never fabricate those missing metrics or call a scan-to-search change a measured speedup.

The six expensive shapes and their alternatives

work_mem is a per-operation budget, not one budget per server or even per query. Multiple operators, workers and concurrent sessions multiply exposure. Raising it globally because one sort spilled can cause memory exhaustion elsewhere.

Node types and index families

A sequential scan reads relation pages; an index scan follows an access method to matching tuples and normally fetches heap rows. A bitmap index scan collects tuple locations; a bitmap heap scan visits heap blocks and may recheck lossy matches. A nested loop evaluates an inner path for each outer row. Hash join builds/probes equality-key state; merge join walks appropriately ordered inputs and must handle duplicate runs. Hash and merge work is output-sensitive: a key with m rows on one side and n on the other emits m*n pairs. Merge is not memory-free; sorting or duplicate handling can use memory and disk.

Aggregate without GROUP BY forms one group even for empty input, but HAVING can filter its result out; without such a filter it emits one aggregate row; HashAggregate maintains groups, and sorted aggregation exploits order. An explicit Sort need not correspond to a written ORDER BY: a merge join or grouping strategy may require order.

Choose an index by its supported operators [PG index types]: B-tree for equality/ranges/order; hash for equality; GIN for inverted membership such as arrays/full text and supported JSON operators; GiST for extensible search such as spatial/range operators; BRIN for summaries of block ranges in physically correlated large tables. GiST behavior depends on its operator class. BRIN trades compact summaries for lossy rechecks; there is no universal thousand-fold size ratio. The pg_trgm GiST/GIN operator classes can support LIKE/ILIKE substring searches, but very short patterns with no useful trigrams can degenerate to a full index scan.

Deriving cardinality errors and choosing a remedy

Let a 10,000-row address fixture have 1,000 Paris rows, all of them in France, and 2,000 France rows overall. Independence predicts 10000 × 0.1 × 0.2 = 200 rows for both predicates; the actual count is 1,000. Negative correlation can instead make an independence estimate too high. Neither error requires stale data.

Statistics include sampled most-common values, histograms and distinct estimates. PostgreSQL extended statistics can model selected within-table dependencies, multi-column common values or distinct counts; they do not solve every expression, join or parameter-sensitive estimate. CREATE STATISTICS needs subsequent ANALYZE to collect statistics. A materialized CTE does not automatically give PostgreSQL newly analyzed statistics; a deliberately created intermediate table followed by ANALYZE is a different intervention with storage and transaction costs.

Work through the first surprising estimate, but also inspect correctly estimated expensive work, missing access paths, waits and parameter-dependent plans. Errors can propagate up joins; they need not always multiply or always dominate runtime. Compare three remedies:

• Statistics: low application change, broader benefits, but incomplete correlation coverage and collection overhead.
• Query restructuring: remove redundant work, preaggregate at the correct grain, or analyze an intermediate. Test NULLs, duplicate multiplicity and outer-join preservation; IN/EXISTS cannot blindly become a duplicate-producing JOIN.
• Plan intervention: engine-specific hints/baselines or session planner controls can stabilize an incident but can age badly. Core PostgreSQL does not offer arbitrary SQL optimizer hints. Record scope, measurements and an expiry rather than treating hints as a permanent fix.

Hands-on: bounded SQL and plan-reading lab

This complete Python 3 program uses in-memory SQLite, 2,000 deterministic rows and its own EXPLAIN QUERY PLAN. It tests result equivalence, expression indexing, ordering and cardinality arithmetic, not PostgreSQL's planner or production performance. The raw plan strings are observations from the installed SQLite version; the oracle compares values rather than freezing its textual plan format.

import sqlite3
from collections import Counter
 
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT, amount INT, status TEXT, notes TEXT)")
rows = [(i, i % 100, i % 1000, "refunded" if i % 50 == 0 else "paid", f"note {i % 100}") for i in range(2000)]
con.executemany("INSERT INTO orders VALUES(?,?,?,?,?)", rows)
q = "SELECT id FROM orders WHERE status='refunded' ORDER BY id"
expected = [(i,) for i in range(0, 2000, 50)]
assert con.execute(q).fetchall() == expected
before = con.execute("EXPLAIN QUERY PLAN " + q).fetchall()
con.execute("CREATE INDEX ix_status ON orders(status)")
after = con.execute("EXPLAIN QUERY PLAN " + q).fetchall()
assert con.execute(q).fetchall() == expected
expr = "SELECT id FROM orders WHERE upper(notes)='NOTE 42' ORDER BY id"
con.execute("CREATE INDEX ix_notes ON orders(notes)")
con.execute("CREATE INDEX ix_upper ON orders(upper(notes))")
assert con.execute(expr).fetchall() == [(i,) for i in range(42, 2000, 100)]
top = con.execute("SELECT id,amount FROM orders ORDER BY amount DESC,id LIMIT 3").fetchall()
assert top == [(999,999), (1999,999), (998,998)]
con.execute("CREATE INDEX ix_amount ON orders(amount DESC,id)")
assert con.execute("SELECT id,amount FROM orders ORDER BY amount DESC,id LIMIT 3").fetchall() == top
# The self-join is redundant only because id is unique and the filter is identical.
joined = con.execute("SELECT o.id FROM orders o JOIN orders b ON b.id=o.id WHERE b.amount>998 ORDER BY o.id").fetchall()
assert joined == con.execute("SELECT id FROM orders WHERE amount>998 ORDER BY id").fetchall()
# Nonunique join negative control: JOIN can duplicate where IN cannot.
con.executescript("CREATE TABLE keys(k INT); INSERT INTO keys VALUES(1),(1);")
semi = con.execute("SELECT id FROM orders WHERE customer_id IN (SELECT k FROM keys)").fetchall()
bag = con.execute("SELECT id FROM orders JOIN keys ON customer_id=keys.k").fetchall()
assert Counter(bag) == Counter({x: 2 for x in semi})
addresses = [("Paris", "FR")] * 1000 + [("Lyon", "FR")] * 1000 + [("Other", "XX")] * 8000
actual = sum(city == "Paris" and country == "FR" for city,country in addresses)
assert actual == 1000 and len(addresses) * .1 * .2 == 200
assert .1 * 100000 == 10000
print({"rows":len(rows), "refunded":len(expected), "top":top, "independent_estimate":200, "actual":actual, "before":before, "after":after})
con.close()

PostgreSQL 16 reference experiment and what each block does

On an authorized disposable database, use temporary tables rather than dropping a shared orders table. This reference SQL is source-reviewed, not executed here. Its 2,000-row size is for safe inspection, not a promise of reproducing spill, nested-loop pathology or a particular index plan.

BEGIN;
SET LOCAL statement_timeout = '5s';
CREATE TEMP TABLE plan_orders(id integer PRIMARY KEY, status text, notes text, amount integer);
INSERT INTO plan_orders
SELECT i, CASE WHEN i % 50 = 0 THEN 'refunded' ELSE 'paid' END,
       'note ' || (i % 100), i % 1000
FROM generate_series(0,1999) AS g(i);
ANALYZE plan_orders;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM plan_orders WHERE status='refunded';
CREATE INDEX ON plan_orders(status);
CREATE INDEX ON plan_orders(upper(notes));
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM plan_orders WHERE upper(notes)='NOTE 42';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM plan_orders ORDER BY amount DESC,id LIMIT 100;
ROLLBACK;

Setup is deterministic, ANALYZE provides samples, the two indexes expose candidate access paths, and LIMIT illustrates that a top-N sort may be small. For stale-statistics study, compare an explicit known distribution change before/after ANALYZE and inspect auto-analyze activity; do not assume a particular estimate. The old random five-million-row script neither guaranteed sort spill nor demonstrated a hash-spill join. The six-shape table above explains those mechanisms independently.

Plan-reading exercise and answer: Given the illustrative inner node above, report roughly 10 seconds of repeated work, not 0.1 ms. Compare the outer row estimate, actual loops and access path before changing the inner index. A shared read is not proof of disk I/O. A visualizer can help display this, but do not export confidential plans. Record before/after result bags, execution settings and observed buffers; no 30× speedup or 10% estimate accuracy is promised.

Production reality: mechanisms rather than invented incidents

Subqueries and duplicate semantics. A semi-join can implement IN (SELECT ...); it is not intrinsically a footgun. If one customer has two matching child rows, replacing membership with an ordinary join duplicates that customer's amount. The executed negative control demonstrates the failure. Inspect the actual plan and index the supported lookup rather than attributing an unsupported 45-second-to-12-ms result to GitLab.

Fast defaults and migration locks. PostgreSQL 11 can add a column with a nonvolatile default without rewriting every row; existing rows obtain the stored missing value. Volatile defaults still require computation for rows. This is not a blanket O(1) migration promise: locking, catalog/dependency work and other ALTER actions matter. No bank/payment-company outage duration is established here [PG default].

Date predicates and time zones. For a timestamptz column, use explicit business-zone day boundaries. Casting created_at::date depends on the session zone and is not an immutable expression suitable for a bare expression index. A range from one local midnight to the next preserves DST-length days; adding a fixed 24 hours need not. For a UTC-day contract, this fragment is precise:

WHERE created_at >= TIMESTAMPTZ '2024-06-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2024-06-02 00:00:00+00'

Current-day predicate: preserve the original question

The fixed UTC dates above answer a particular UTC day. To rewrite the original created_at::date = CURRENT_DATE for a PostgreSQL 16 timestamptz column while preserving the session's time-zone contract, use:

WHERE created_at >= CURRENT_DATE::timestamptz
  AND created_at <  (CURRENT_DATE + 1)::timestamptz

Each date becomes its own local midnight. CURRENT_DATE follows the transaction's notion of current date; it is not a clock that advances each row. Keep the same session zone for both predicates. For a different business zone, bind explicit lower/upper instants computed from consecutive dates in that zone instead; do not silently change the original semantics. In America/New_York, 2024-03-10 spans 23 elapsed hours, while 2024-11-03 spans 25. A fixed 24-hour duration is not equivalent. A timestamp without time zone requires its own wall-clock contract rather than blindly adopting these instants. These are source-reviewed PostgreSQL expressions, not locally executed PostgreSQL SQL.

Translating plans across engines

MySQL 8.4 EXPLAIN reports access types such as ALL, range, ref and eq_ref, possible/chosen keys and estimated rows; TREE/EXPLAIN ANALYZE provides iterator-oriented actual execution. eq_ref means a unique-key lookup per preceding row combination, not PostgreSQL's cost scale. SQL Server's actual execution plan includes runtime information and warnings as well as estimates; compare estimated versus actual rows, join operators, lookups and spills. Requesting an actual plan runs the query. BigQuery's execution graph exposes stages, step types, input/output rows, shuffle output and timing/slot work; bytes scanned and slot-milliseconds are distinct from wall-clock duration [MySQL plan; SQL Server plan; BQ plan].

For all three, ask where rows/bytes expand, whether data movement or spill dominates, and which estimates differ. Do not compare their numeric costs as a common unit. This carries into MongoDB explain, Spark exchanges, warehouse materializations and slow-query observability; DynamoDB does not expose a PostgreSQL-like optimizer EXPLAIN for arbitrary SQL joins. A cache only hides misses, so expensive miss queries still need this analysis.

Recall and worked answers

1. EXPLAIN versus ANALYZE? Prediction versus execution with instrumentation; use ANALYZE only when the statement and load are safe.
2. 100 estimated versus 1,000,000 actual? Investigate stale/sample statistics, skew, correlation, predicates, parameters and execution context. Check loops and LIMIT before comparing. Extended stats are selective tools, not universal fixes.
3. Why can a sequential scan win? An index path can require more scattered heap access than scanning the needed relation. Width, correlation, caching and matching pages matter; no fixed percentage decides.
4. External merge Disk: 234MB? That sort used temporary disk. Reduce work, consider order-preserving access or bounded memory after concurrency budgeting; 234MB is an example, not lab output.
5. Why not the plain email B-tree for upper(email)? Indexed values do not generally support equality on the transformed value. A matching expression index is a candidate; the optimizer can still prefer a scan.
6. BUFFERS in one sentence? Counts shared/local/temp block accesses, with shared reads possibly served by OS cache, not necessarily disk.
7. Sargable date rewrite? A half-open range with explicit intended time-zone boundaries, as above; confirm its semantics match the original predicate.
8. BRIN instead of B-tree? Large physically correlated data where coarse block pruning is useful and rechecks acceptable; measure size and pruning rather than quoting a universal ratio.
9. Why PostgreSQL 11 fast defaults? Nonvolatile default values can be represented in metadata for old rows rather than physically filling all rows; volatile defaults and lock acquisition remain important exceptions.

Explain out loud: describe a prediction versus observation, name the six expensive shapes and their countercases, then explain why a cardinality mismatch is a hypothesis generator rather than an automatic ANALYZE prescription.

Sources and next step

Primary documentation checked on 2026-10-07; exact supporting passages, versions, raw/text hashes and claim entailments are in the batch-3 evidence. References: PG EXPLAIN, PG statistics, PG index types, PG trigram, PG default, PG release 11, MySQL plan, SQL Server plan, BQ plan.

Next: NoSQL landscape. The executed SQLite fixture and cost arithmetic are local algorithm/SQL evidence, not a PostgreSQL, cloud or cluster benchmark.

Explain-out-loud answer guide

1. What is EXPLAIN ANALYZE? It executes the statement and annotates the plan with observed work; side effects, locks and instrumentation overhead are real, so start with plain EXPLAIN and an authorized bounded fixture.
2. Six expensive shapes: broad scan (check selectivity/pages); repeated loop probes (outer rows multiply work); hash spill (inspect batches); external sort (reduce rows/width or use suitable order); transformed predicate (matching expression access); leading wildcard (suitable trigram rather than a normal prefix seek).
3. A 100× estimate mismatch: compare per-loop populations at the same node and account for LIMIT, then investigate statistics freshness, skew/correlation and parameter context. An ANALYZE, statistics object, rewrite or access path is a hypothesis to test, not an automatic diagnosis.

Optional archived resources

These links retain the original pre-read route; historical titles are labels, not verified endorsements. Video attribution, duration, playback and external reachability were not revalidated. Use the version-qualified primary references above for correctness; mutable /current or /latest pages may differ.

• Postgres Explain Explained - How Databases Prepare Optimal Query Plans to Execute SQL — archived video/discovery link.
• Postgres Explain Explained - How Databases Prepare Optimal Query Plans to Execute SQL — archived video/discovery link.
• pgMustard — visualising EXPLAIN plans — archived video/discovery link.
• PostgreSQL Docs — Using EXPLAIN — archived reading link.
• Use The Index, Luke! — archived reading link.
• VACUUM, ANALYZE, EXPLAIN, COUNT — archived reading link.

Series route and study format

Hub: The 6-Month Learning Plan. Previous: S042; next: S044. These are navigation pointers, not certification of every linked body or permission to publish it.

The original 130-session series suggests a 90-minute study cycle: intuition → visual explanation → hands-on practice → production failure analysis → recall. Treat that as a pacing suggestion, not a tested completion time. Work the examples and explain the failure cases before moving on; reference-only service exercises need separate execution authority.