Query Plans and the Optimiser

18.1.1 PLAIN — in simple words#

1. The database first needs to understand the query’s structure and names. It then chooses operations that can produce the requested result and executes those operations.
2. There can be several correct routes. An order can be found by scanning a small table or navigating an index. A join can begin with different inputs if the transformation preserves the required meaning.
3. The optimiser chooses using available information and a cost model. “Optimiser” does not mean it knows every future wait or proves the globally fastest possible execution.

18.1.2 PLAIN — a picture in your head#

1. A recipe says what dish to produce, while a kitchen work plan decides which preparation steps can be combined, reordered or shared.
2. A cook may prepare a small sauce first to avoid carrying a large pot through every later step.
3. Where the comparison breaks: a database transformation must preserve formal query semantics. It cannot replace an expensive ingredient with a different one simply because the result seems similar. Outer joins, NULL and duplicate contributions limit legal reorderings.

18.1.3 PLAIN — a worked example#

1. Ask for each order’s agreed total, retaining only totals above 12,000 paise. The canonical answer is O-1042 with 17,100; O-1043’s 11,550 does not qualify.

SELECT order_id, SUM(quantity * unit_price_minor) AS total_minor
FROM order_lines
GROUP BY order_id
HAVING SUM(quantity * unit_price_minor) > 12000
ORDER BY order_id;

2. A conceptual flow is: obtain lines, group them by order, calculate each sum, test the group total, then produce the requested ordering. This describes logical responsibilities, not literal measured node output.
3. Filtering individual lines above 12,000 instead would answer a different question. It would discard the pen contribution and fail to produce the correct order total.

18.1.4 PLAIN — what is really happening inside#

1. Parsing identifies syntax; name and type resolution connect expressions to objects and operations. Rewriting can expand views or apply other supported transformations.
2. Planning considers access paths, join arrangements and methods, estimated row counts, ordering and intermediate work. Execution follows the resulting plan while interacting with storage and transaction visibility.
3. A named common table expression can make a query easier to read, but does not universally force a physical temporary table. Whether it is folded or materialised depends on engine rules and query properties. [S116]

18.1.5 TECHNICAL — the engineer’s version#

1. PostgreSQL describes parsing, rewriting, planning and execution as distinct stages. A query tree and an execution plan are different representations. Views can be expanded through the rewrite system. [S111]
2. Plan search is bounded by computational feasibility and engine heuristics. Statistics and configured costs describe an estimated environment, not complete knowledge of runtime conditions. PostgreSQL explicitly documents cases where exhaustive search is impractical. [S112]
3. Legal rewrites preserve the result contract, including multiplicity and NULL behaviour. Optimisation evidence begins with result equivalence, not a shorter SQL string.

18.1.6 WORDS — remember these#

1. Execution plan: the chosen route to the answer — a structured set of access, join, calculation and ordering operations used by the executor. Query rewrite: a semantics-preserving change in representation — transformation of a query before or during planning under the engine’s rules. Materialisation: save an intermediate result for reuse — creation of a stored working representation rather than recomputing or streaming every use.

18.2.1 PLAIN — in simple words#

1. The optimiser needs an idea of how much data each stage will produce. It usually relies on summaries and samples rather than executing every possible route first.
2. Those summaries can be incomplete or outdated. A rare value can become common, or two fields can be related in a way that separate summaries do not capture.
3. A wrong early estimate can affect later choices. Planning for ten candidate rows when there are 100,000 can lead to a route whose repeated work becomes expensive.

18.2.2 PLAIN — a picture in your head#

1. Mira hires one temporary clerk after estimating ten deliveries. A promotion unexpectedly creates ten thousand. The staffing choice was based on the estimate, not on knowledge of the actual workload.
2. Knowing only the average number of deliveries per branch can also miss one unusually busy branch.
3. Where the comparison breaks: planner statistics are structured numerical summaries and engine-specific estimators. The example illustrates information limits, not a claim that a database literally allocates one worker for ten rows.

18.2.3 PLAIN — a worked example#

1. Imagine a synthetic 100,000-row table where 90,000 rows have status closed and 10,000 have status open. A uniform two-value assumption would estimate 50,000 for either status and misrepresent both.
2. Now suppose every open row belongs to branch A. Estimating status='open' AND branch='A' by multiplying independent fractions can substantially undercount the actual open-A population.
3. Inspect the earliest stage where an estimate departs from observed rows. A later sort receiving too many rows may be a consequence rather than the origin of the mistake.
4. These counts are invented to expose assumptions. They are not copied planner outputs or a benchmark of a statistics feature.

18.2.4 PLAIN — what is really happening inside#

1. Statistics can include approximate distinct counts, frequent values, histograms and selected correlations. No compact summary describes every possible predicate combination exactly.
2. Collection has costs and policies. A newly loaded table may need updated statistics before its distribution is represented usefully.
3. Even with current statistics, complex expressions, parameter uncertainty or correlations outside the supported summaries can produce errors. Re-running statistics collection is not a universal cure.

18.2.5 TECHNICAL — the engineer’s version#

1. Distinguish relation cardinality, predicate selectivity and join cardinality estimates. PostgreSQL’s row-estimation examples show how statistics feed specific estimators; they are not guarantees for arbitrary business distributions. [S115]
2. Compare estimated and actual rows at corresponding nodes, accounting for loops and the output convention. A large ratio at an early node is a clue to investigate, not proof that one particular setting is wrong.
3. Extended statistics can represent selected multicolumn relationships. Their existence does not imply that every join or expression correlation is modelled. Keep the exact engine version and supported statistic type in the diagnosis. [S94]

18.2.6 WORDS — remember these#

1. Histogram: a summary of how values are distributed — a bucketed representation used for approximate frequency or range reasoning. Cardinality estimate: a predicted row count — the planner’s expected output size for an operation under its available statistics and model. Correlation: values vary together — a relationship that can invalidate an assumption that conditions are independent.

18.3.1 PLAIN — in simple words#

1. The same matching rule can be implemented in different ways. A nested loop checks matching work for each row of an outer input. A hash join builds a keyed working structure on one input and probes it with the other. A merge join advances through suitably ordered inputs.
2. None is always best. A small outer input with a useful lookup index can make nested loops effective. Large equality joins can favour hashing. Already ordered inputs can make merging attractive.
3. Every strategy must preserve the matches and multiplicities required by the logical join. A faster algorithm is not allowed to drop repeated valid rows.

18.3.2 PLAIN — a picture in your head#

1. To match two lists, you could search the second list separately for every name on the first; build a name-to-record guide for one list; or sort both lists and walk them together.
2. The work depends on list sizes, available ordering and memory.
3. Where the comparison breaks: database joins can use indexes, batches, parallelism, spilling and complex conditions. The three pictures explain families of strategies, not exact runtime behaviour for every node.

18.3.3 PLAIN — a worked example#

1. Use invented key lists A=[1,2,2] and B=[2,2,3]. An equality join contains four key-2 pairs because two A entries match two B entries.
2. A naive nested loop examines nine pairs and retains four. A toy hash join stores both B entries for key 2, then emits two matches for each of A’s two key-2 entries.
3. A merge-style walk groups the equal keys on both ordered sides and emits the same two-by-two combinations. Advancing both cursors once and emitting only one pair would be incorrect.
4. This small oracle checks semantics independently of any database’s chosen strategy. Larger inputs are needed to compare performance meaningfully.

18.3.4 PLAIN — what is really happening inside#

1. Nested-loop cost depends strongly on the inner operation. Repeating a full scan is different from repeating a selective index lookup, and repeated keys may enable reuse in some engines.
2. A hash join needs memory for its build side and must resolve collisions with actual equality checks. If working data exceeds available memory, partitioning and spill may add work.
3. A merge join needs compatible ordering and comparison rules. Obtaining that order can require sorts, so count preparation costs rather than praising only the final merge step.

18.3.5 TECHNICAL — the engineer’s version#

1. PostgreSQL documents nested-loop, merge and hash join strategies. The planner considers their prerequisites and costs; the logical join type, predicate and available paths constrain legal choices. [S112]
2. In a simplified naive nested loop, comparison work is O(NM). An indexed inner lookup changes the model. A well-distributed in-memory hash join can approach O(N+M+K), where K is output size, but its assumptions and output multiplicity must be stated.
3. The output term matters: a many-to-many equal-key group can produce a large result even with an efficient algorithm. No implementation can return K distinct output contributions without accounting for them somewhere.

18.3.6 WORDS — remember these#

1. Nested-loop join: repeat matching work for each outer record — a join strategy whose inner access can range from a full scan to a selective lookup. Hash join: match through a temporary hashed key structure — a build-and-probe strategy that still checks equality and preserves required duplicates. Merge join: match while advancing through ordered inputs — a strategy using compatible sorted join keys and correct handling of equal-key groups.

18.4.1 PLAIN — in simple words#

1. A plan is more than a choice between “index” and “no index.” It combines ways to obtain rows, match them, reduce them and order the result.
2. An index can be useful for selecting a range, supplying order or carrying needed values. A scan can be useful when much of the input is required.
3. The cheapest first step is not always part of the cheapest whole plan. A slightly more expensive ordered input might avoid a much larger sort later.

18.4.2 PLAIN — a picture in your head#

1. A train ticket with a slightly longer first leg can make the overall journey shorter by avoiding a lengthy transfer.
2. Comparing only the first station’s travel time misses the rest of the route.
3. Where the comparison breaks: plan costs are not simply a sum of independent journey times. Pipelining, blocking operations, parallelism and shared work can change the relationship between local work and elapsed time.

18.4.3 PLAIN — a worked example#

1. Suppose a synthetic report filters one branch and orders by creation time. Candidate A scans the table, filters, then sorts. Candidate B uses an ordered (branch_id, created_at) index and retrieves matching records.
2. If the branch contains very few narrow results, B may save work. If it contains most of a wide table, A may be competitive. If B covers all selected fields, the comparison changes again.
3. Do not attach invented millisecond numbers to these possibilities. Capture actual plans and measurements for a defined dataset.
4. A result-equivalence check must include ties, NULL handling and the final ORDER BY. Equal totals alone do not establish equal ordered records.

18.4.4 PLAIN — what is really happening inside#

1. The planner tracks useful properties of intermediate paths, such as ordering and estimated size, while considering later operations.
2. Some nodes can emit rows as they arrive; others need to accumulate input before producing their answer. A full sort and a final aggregate can create startup work even when the eventual result is small.
3. A query returning only the first few ordered rows may value startup cost differently from a full export. Measure the use case actually required rather than assuming all consumers need the entire result immediately.

18.4.5 TECHNICAL — the engineer’s version#

1. PostgreSQL’s EXPLAIN distinguishes startup and total estimated cost. These support different consumption patterns, including limited results; they are not measured wall-clock durations. [S113]
2. Path properties can make an apparently more expensive local access useful globally. Ordering supplied by an index can affect sort and merge opportunities. [S106]
3. Planner method settings are diagnostic levers, not universal recommendations. Disabling one strategy can expose an alternative for investigation but does not establish that the alternative is correct for all parameter values or concurrent workloads. [S114]

18.4.6 WORDS — remember these#

1. Startup cost: estimated work before the first result — a planner quantity relevant to operations that must prepare or consume input before emitting rows. Blocking operation: input must accumulate before output can proceed — a stage such as a full sort under its particular execution strategy. Path property: useful information about an intermediate route — characteristics such as ordering, parameter dependence and estimated size used in planning.

18.5.1 PLAIN — in simple words#

1. Read a plan from the data sources through the operations that use them. Ask what each node receives, what it returns and how often it runs.
2. Keep estimates separate from measurements. A plan without execution observations tells you what the engine intends and predicts, not what actually happened.
3. Diagnostic commands differ across engines. In PostgreSQL, adding ANALYZE executes the statement. SQLite’s EXPLAIN QUERY PLAN provides a different, more compact description and does not supply PostgreSQL’s runtime fields.

18.5.2 PLAIN — a picture in your head#

1. A work order describes the tasks expected on a factory line. A completed production log adds what was actually processed and how long it took.
2. Repeating a task 1,000 times matters even when the time per repetition is small.
3. Where the comparison breaks: plan-node timings may include descendant work and use per-loop conventions. Adding every displayed duration can double-count. Read the documented units and nesting rather than treating the display as independent stopwatch rows.

18.5.3 PLAIN — a worked example#

1. In the SQLite practice database, inspect the product query using bound values:

sql = """SELECT order_id, line_no, quantity
         FROM order_lines WHERE product_id = ?
         ORDER BY order_id, line_no"""
plan_rows = db.execute("EXPLAIN QUERY PLAN " + sql, ("P-NOTE",)).fetchall()
result_rows = db.execute(sql, ("P-NOTE",)).fetchall()

2. Save the actual plan rows, result rows and SQLite version. The concatenation here adds a fixed diagnostic prefix to a fixed authored query; it does not interpolate untrusted values into SQL.
3. For PostgreSQL, a documented diagnostic form is EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT .... This is not executed in the SQLite lab, and the ellipsis is explanatory notation, not runnable SQL.
4. A hypothetical node reporting 4 output rows per loop over 100 loops represents approximately 400 output-row events under that convention. It does not imply 400 distinct business records.

18.5.4 PLAIN — what is really happening inside#

1. Planning-only output is an estimate. Execution instrumentation can add row counts, loops, memory and buffer observations, with overhead of its own.
2. Buffer hits, reads, dirtied pages and temporary work have different meanings. A buffer read does not necessarily mean the physical device was uncached at every lower layer.
3. EXPLAIN ANALYZE does not reproduce a normal client’s full result-delivery experience. PostgreSQL’s documented options can include serialization work, but network transmission still needs a separate application-level measurement. [S113]

18.5.5 TECHNICAL — the engineer’s version#

1. Read node estimates and actual values using the engine’s documented per-loop and inclusive conventions. Do not sum nested times or buffer totals without accounting for included child work. [S65]
2. PostgreSQL ANALYZE execution can cause the statement’s ordinary side effects. A database ROLLBACK does not universally undo external effects of invoked functions or restore consumed sequence values. Use an isolated authorised experiment, not a casual production probe. [S113]
3. SQLite’s EXPLAIN QUERY PLAN text is intended for interactive diagnosis and may change across releases. Retain raw output as evidence without making a library depend on one exact wording. [S90]

18.5.6 WORDS — remember these#

1. Instrumentation: measurements added to execution — observation code collecting timings, counts or resource evidence, potentially with its own overhead. Loop count: how often a node is invoked — an execution multiplicity that must be considered alongside per-loop measurements. Buffer hit: a needed page was already in the relevant cache — an engine-level observation, not a statement that all possible lower-level work vanished.

18.6.1 PLAIN — in simple words#

1. Start with a reproducible slow case and its expected answer. Preserve the query, data profile, settings and baseline observations before changing anything.
2. Find the earliest important mismatch between expected and observed work. Check data distribution, statistics, predicate meaning and available paths before reaching for a setting that forces a favourite node.
3. Change one justified factor in an isolated environment. Recheck both answers and performance across the workload envelope, then document costs and remaining uncertainty.

18.6.2 PLAIN — a picture in your head#

1. When a delivery route is slow, first determine whether the delay is at the warehouse, on the road or at the customer’s door. Replacing the van does not fix a two-hour queue at dispatch.
2. A useful experiment changes a suspected cause while keeping enough of the rest comparable.
3. Where the comparison breaks: some database changes interact strongly, and a perfectly isolated variable is not always possible. Record those interactions instead of pretending the comparison is controlled when it is not.

18.6.3 PLAIN — a worked example#

1. A hypothetical query is estimated to find 20 open tasks but actually finds 80,000 after a bulk import. First verify that the open-task population is correct and that the import did not misclassify statuses.
2. Inspect statistics freshness and the actual parameter values. Updating statistics in a test environment may change the plan; it may also leave the relevant error unchanged if the estimator still lacks a needed relationship.
3. A candidate partial index or narrower projection can then be tested against the same result contract. Include the common closed-task case and a missing-value case to avoid fixing one parameter while degrading another.
4. The intervention record should show before/after query results, plans, sample distributions, durations, write impact and the exact change. A screenshot of a lower number alone is not sufficient.

18.6.4 PLAIN — what is really happening inside#

1. A plan regression can come from changed data, schema, statistics, configuration, software version or workload contention. More than one may change together.
2. Reproducibility requires saving the relevant state, not just SQL text. A query with different bindings is a different test input.
3. A successful experiment supports a bounded conclusion: this change improved this tested workload under these conditions. Production rollout adds monitoring, rollback and compatibility responsibilities.

18.6.5 TECHNICAL — the engineer’s version#

1. Use a hypothesis-driven sequence: establish correctness; identify the dominant discrepancy; collect bounded evidence; choose a reversible intervention; validate representative cases; and record operational consequences.
2. PostgreSQL exposes planner-method and cost parameters, but its documentation treats these as parts of a model. Global changes can affect unrelated queries, so local experimental evidence is not blanket tuning authority. [S114]
3. The query-plan lab supplies educational observations and equivalence checks. It does not provide a production advisor, automatic index deletion policy or proof of the globally optimal plan.

18.6.6 WORDS — remember these#

1. Baseline: the saved comparison point — a defined workload, configuration and observed result before an intervention. Hypothesis-driven tuning: change what evidence suggests — performance investigation linking an explicit suspected cause to a controlled test. Regression envelope: cases checked for unintended deterioration — representative workloads beyond the one case an intervention aims to improve.