Interview questions
Estimates and statistics
1A report was fast all month and became slow after last night's data load, with no code change. Where do you look first?
At the plan's estimated and actual row counts. Run EXPLAIN ANALYZE and find the first node, from the bottom, where they differ by more than about 10×. A newly loaded partition with no statistics yet is the usual suspect.
Read moreAuto-analyze: where staleness is madeDiagnosing a bad plan
2Why is improving the optimizer's cost model usually not the first fix for bad plans?
The cost model only ranks plans against each other. If the row counts fed into it are wrong by large factors, a better model just ranks wrong inputs more confidently. Fix the estimates first.
Read moreWhy errors multiplyThe cost model
3Why do row-count estimation errors grow with the number of joins?
Selectivities are combined by multiplying them. Each estimate's error is a ratio, and ratios multiply, so the error grows exponentially with the number of joins.
4Why do estimation errors tend to be underestimates?
The estimator assumes conditions are independent and multiplies their selectivities. Real conditions are often correlated (a city and its postcode), so together they are much less selective than the product, and the estimate comes out too small.
5Why is underestimating row counts worse than overestimating them?
An underestimate leads the planner to build a hash table that does not fit, broadcast a table that is not small, or run a nested loop over far more rows than expected. An overestimate only gives a conservative, somewhat slower plan.
6What are the four assumptions of a cardinality estimator?
Uniformity (values are evenly spread), independence (columns are unrelated), containment (the values on one side of a join appear on the other) and uniform joins (matches spread evenly). Every estimation failure is one of these breaking.
7What does the planner do when a table has no statistics at all?
It falls back on fixed constants dating from System R: about 1/10 selectivity for an equality, 1/3 for an inequality, 1/4 for a range. The resulting plan is not slightly worse; it is arbitrary.
Read moreWhen there are no statistics at all
8Why can the number of distinct values not be estimated reliably from a sample?
Any estimator working from a sample of k rows out of n has a worst-case error on the order of √(n / k): the sample cannot tell rare values from absent ones. Engines therefore use a HyperLogLog sketch over a full pass instead.
Read moreCounting distinct values from a sample is provably hard
9Why do statistics go stale on a large table that is loaded daily?
Automatic analysis triggers only after 0.1 × n + 50 rows have changed. On a billion-row table that is 100 million rows, about 10 days at 1% growth a day. Collecting statistics has a fixed cost, so the refresh interval is a cost decision you should make deliberately.
10Why is a sample of a join not the join of samples?
Sampling both sides at rate p keeps only about p² of the join's result, so small results can vanish entirely.
Read moreStatistics: the escapes
11Why is the optimizer's cost number not an estimate of run time?
It only ranks plans and is not calibrated; it has no unit. It cannot be used for admission control or for charging teams for their queries.
Read moreThe cost model
Access paths and join order
12When does an index lookup beat a full table scan?
Below a crossover selectivity of about (1 / rows per page) × (sequential cost / random cost), adjusted for how well the table is clustered on the column. The old "5%" rule comes from spinning-disk cost ratios; on NVMe the crossover is higher.
Read moreAccess path selection
13What is the dynamic-programming cliff in join-order search, and what happens beyond it?
Exhaustive dynamic programming over join orders costs O(3ⁿ) and becomes impractical beyond about 12–15 tables. Past that, planners switch to greedy or genetic search, which gives worse plans and may give different plans on different runs.
Read moreJoin order search
14Why is the principle of optimality false for query plans?
A sub-plan that is locally more expensive can win overall if it delivers a useful property, such as a sort order or a data distribution that a later step needs. So the search must keep the best plan per subset of tables for each such property, not just one.
Read moreInteresting orders
Rewrites
15Why does NOT IN often run much slower than NOT EXISTS?
NOT IN against a set containing a NULL returns no rows at all, so the planner may turn it into an efficient anti-join only if it can prove the subquery returns no NULLs. Otherwise it runs the subquery as a nested loop. NOT EXISTS has no such trap.
16Why does WHERE year(ts) = 2024 scan every partition?
The column is wrapped in a function, so the condition is not of the form column op constant, and cannot reach partition pruning or min/max skipping. Rewrite it as a half-open date range: ts >= '2024-01-01' AND ts < '2025-01-01'.
Read moreSargability
17Why can adding a layer of views make a query suddenly slow?
Expanding each view adds its tables to the query, and one more layer can push the total past the point where the planner stops searching join orders exhaustively.
Read moreView expansion
18Why might a fresh materialized view not be used for a query it could answer?
Deciding whether a view contains a query's answer is undecidable in general, so matchers are conservative. Common blockers: the view lacks a column the remaining filter needs, its grouping is coarser than the query's, or its aggregate cannot be rolled up (AVG, COUNT DISTINCT).
Read moreWhy matchers are conservativeWhy aggregate algebra decides reusability
19When is it safe for the planner to eliminate a join?
When an enforced foreign key points to a unique key, and the query uses no column from the joined table. Constraints that are declared but not enforced make a wrong answer possible, and silent.
Read moreJoin elimination
20Why can a database upgrade change query plans even when the statistics are identical?
The set and order of rewrite rules change between versions, and rule engines that apply rules until nothing changes are not proven to reach the same result regardless of order. So the final plan can differ.
Read moreFixpoints and why rule order matters
Reducing the data read
21A query returns 40 rows but scans terabytes. Where do you look first?
At the scan: did the filters reach it, and is the data clustered on the filtered column? A function wrapped around the column, or unsorted data, removes most of the pruning.
Read moreWhat data reduction forbids
22Why are min/max statistics useless on an unsorted column?
A block of unsorted data is effectively a random sample of the column, so its range covers (B − 1) / (B + 1) of the domain for B rows: 99.98% at 10,000 rows. No block can be skipped.
23Which predicates can block statistics never prune?
Negative ones, such as <> and NOT IN. Statistics can prove that a value is absent from a block, not that every value except one is absent.
24How do you speed up an equality filter on an unsorted column?
Add Bloom filters, which summarize membership, or change the layout by clustering on that column. For a range filter on an unsorted column, no metadata helps; only the layout does.
Read moreThree families of skipping metadata
25What is the difference between skipping statistics and an index?
An index maps values to locations, proves presence, is about as large as the data and must be maintained on every write. Skipping metadata maps each block to a summary, proves absence, is small, and needs no maintenance on immutable data.
Read moreSkipping metadata versus an index
26Why does WHERE date(ts) = '2024-01-01' scan everything on a table partitioned by a dt column?
The optimizer does not know how ts relates to dt. Declare the partition as a transform of ts (hidden partitioning, as in Iceberg) so the catalog records the relationship and the filter can prune.
Read morePartition pruning
27What is a runtime filter, and why is it cheap?
A filter built from the join keys on the build side of a hash join and pushed into the scan of the probe side. It is a by-product of building the hash table. It costs a scheduling barrier, since the probe must wait, and its size is limited by the cost of broadcasting it.
28How large is a Bloom filter for a billion keys at a 1% false-positive rate?
About 10 bits per key, so 10⁹ × 10 bits ≈ 1.25 GB: too large to broadcast as a runtime filter. A filter for a million keys is 1.25 MB.
Read moreThe mechanism
29Why can SELECT COUNT(*) be instant on one table and slow on another?
Row counts stored in file footers or manifests can answer it only if the filter is fully resolved by pruning and no deletes are tracked separately. Merge-on-read tables with deletion vectors break the stored counts.
Read moreMetadata-only answers
30How can adding LIMIT make a query slower?
Assuming matching rows are spread uniformly, the optimizer prefers scanning an index on the sort column, expecting to find k matches quickly. If the filter correlates with the sort order, so the matches all come late, it reads almost everything first.
Read moreLimit and Top-N pushdown
31Why does a Top-N operator avoid spilling to disk?
A heap of size k holds only k rows, instead of the whole input a full sort needs, so memory stays small.
Read moreLimit and Top-N pushdown
32When does late materialization, fetching other columns only for rows that pass a filter, lose?
Above a selectivity where gathering the scattered rows costs more than decoding the whole column sequentially. The ratio of sequential to random access cost sets the crossover.
Read moreLate materialization
33In what order should several AND-ed predicates be evaluated?
By rows removed per unit of cost: evaluate predicate i before j if (1 − sᵢ) / cᵢ > (1 − sⱼ) / cⱼ, where s is its selectivity and c its cost per row.
Read morePredicate pushdown: a ladder
Decisions at run time
34What is parameter sniffing, and what are the fixes?
A cached plan, built for the first parameter value seen, is reused for values with very different row counts. Fixes: do not look at the parameter when planning; look, but keep several plans by value; re-plan on every call; or plan for the worst case.
Read morePrepared statements and plan caching
35Why does turning on adaptive query execution not fix bad statistics?
The first stage still runs on the estimates. Adaptive execution can revise only decisions that come after a stage boundary, and only while their alternatives are still possible.
Read moreThe limits of adaptivity
36A plan changed although nothing seems to have changed. What is the likely cause?
Automatic analysis refreshed the statistics and invalidated cached plans, so the plan was rebuilt and flipped. Plan changes cluster at analyze time.
Read moreA link back to statistics
37What makes result caching cheap in a lakehouse?
Every table version has a snapshot ID, so checking whether a cached result is still valid is a single comparison. Tables updated in place need row or partition change tracking instead.
Read moreResult-set caching