Summary and key takeaways
Ananya and Dev's lessons, in order:
- The Tuesday report (The reframe: a layer limited by what the system can know). A plan that was right on Monday was a bet. Estimate errors multiply across joins and lean towards "too small". Ask for the plan's estimated and actual row counts before touching any cost setting.
- Six dimensions, two used (From SQL text to a plan). Normalization, inferred predicates, join elimination through enforced foreign keys, and rule order change the query before any cost is computed.
- The new partition (Estimating cost). Costs only rank plans. Missing or stale statistics (a new partition with none) lead to default constants and a bad plan. Fresh statistics flip it back.
- Four layers of views (Join order search). Past about 15 relations, exact join-order search gives way to heuristics, and plans can differ between runs.
- Four queries with the same meaning (Rewrites).
NOT IN, wrapped columns, repeated CTEs and unused materialized views are cases where a rewrite is blocked by NULL semantics, sargability or subsumption conditions. - The post-mortem (Diagnosing a bad plan). Use a fixed order of checks. Escapes from the planner (hints, baselines, denormalization) have a price.
- 4.2 TB for 40 rows (Data reduction). Not reading is worth more than reading faster. Push the question down: partition pruning, skipping, runtime filters, projection, Top-N, late materialization.
- The large store and the ad hoc join (Execution decisions). Plan reuse binds a decision before its value is known (parameter sniffing); adaptive execution decides after a barrier; result caching skips the work.
Principles that repeat:
| Principle | Where it appeared |
|---|---|
| Independence is assumed everywhere, and correlation breaks it | Cardinality estimates, LIMIT pathology, runtime-filter sizing |
| Approximation needs a safe side. Where an error has an affordable side, systems approximate; where it has none, they defer | Bloom filters and min/max (safe) versus cardinality estimation (no safe side, so adaptivity) |
| Costs are moved, not removed | Every escape in Data reduction moves cost to write time or space; every escape in Execution decisions moves it in time, resource or failure mode |
| A fixed cost gives a natural batch or reuse size | Compile break-even n* = C/Δc; plan reuse over k executions; row-group size |
| Variance matters more than the mean in production | Adaptivity improves the mean but raises plan-shape variance; hints and robust plans buy the tail |
| Observation, correction and sharing need a stopping point | Runtime filters, adaptive execution, shared subplans, group commit, checkpoints |
| The interface vocabulary is the limit | Pushdown reaches only as far as the source's accepted filters |
| Cost numbers are ordinal. They rank plans; they are not predictions | Cost model, random_page_cost folklore |
Key formulas and numbers:
| Quantity | Value |
|---|---|
| Expected range of B random samples | (B − 1)/(B + 1) of the domain: 99.98% at B = 10,000 |
| Read amplification of skipping | block size ÷ matching rows per block |
| Conjunct order | evaluate i before j if (1 − sᵢ)/cᵢ > (1 − sⱼ)/cⱼ |
| Top-N | O(n log n) time, O(n) memory becomes O(n log k) time, O(k) memory |
| Bloom filter | about 10 bits per key at 1%: 10⁶ keys is about 1.2 MB, 10⁹ keys is about 1.2 GB |
| JIT break-even | n* = C_compile / Δc: 50 ms ÷ 20 ns = 2.5 million rows |
| Result cache | worth it if hit rate × query cost > store cost + invalidation cost |
Mechanism checklist for "why is this query slow?" First, are the estimates close to the actual rows? Second, is the predicate sargable and clustered? Third, can the work be removed (prune, skip, runtime filter, metadata answer)? Fourth, does the query or its shape recur (result cache, plan policy)? Fifth, is the first bad estimate before the first barrier (adaptivity cannot help)? Only last: make the work faster (compilation, vectorization).
What the planner cannot do. It cannot see intermediate row counts before running. It cannot prune on a negative predicate or skip on an unsorted column with a range predicate. It cannot revise decisions whose alternatives are closed. It cannot cache without a way to detect change. It cannot be both fully deterministic and fully adaptive. Every escape has a price, and the price is paid in time, space, determinism or the exact answer.