SQL Query Execution & Optimizer
Studies in this cluster, in series order. Each one keeps its own URL.
SQL
Query plans, joins, and the index the interviewer hopes you mention.
SQL Query Execution & Optimizer
6 studies- 1.How a SQL Query Actually Executes - Parse, Rewrite, Plan, Execute, Volcano vs Vectorized & Reading Plan TreesInterview hub: parse -> analyze -> rewrite -> plan -> execute; Volcano iterator vs vectorized vs compiled execution (runnable model counting next() calls, LIMIT pipelining); real PostgreSQL 17 run where one query shape gets index+nested-loop vs seq-scan+hash-join plans depending on the constant; reading plans top-down (control) vs bottom-up (data), inclusive vs exclusive time and q-error (runnable); PG vs MySQL vs SQLite vs DuckDB planner contrasts.
- 2.Join Algorithms - Nested Loop vs Index Nested Loop vs Hash Join vs Merge JoinNested loop vs index nested loop vs hash join vs merge join: runnable work-count model, textbook I/O cost formulas and Postgres-style batch math (runnable), real PG 17 plans forcing each algorithm (incl. Memoize) and a hash join spilling to Batches: 8 under small work_mem; when each wins table, the planned-10-got-1M nested loop failure, MySQL (no merge join, hash join 8.0.18+), SQLite automatic indexes, DuckDB range joins.
- 3.Cost-Based Optimizer & Join Ordering - Cost Constants, Dynamic Programming, Left-Deep vs Bushy, join_collapse_limit & GEQOPostgreSQL cost constants verified exactly against pg_class (Seq Scan = relpages + reltuples x cpu_tuple_cost); random_page_cost 4 vs 1.1 flipping seq vs index scan in real PG 17; runnable index-vs-seq crossover model and physical correlation; runnable Selinger DP join enumeration with left-deep vs bushy and the n!/Catalan explosion; join_collapse_limit/from_collapse_limit/GEQO with a real written-order plan; CBO vs rule-based vs Cascades vs adaptive vs learned.
- 4.Cardinality Misestimates & Plan Regressions - Correlated Columns, Extended Statistics, Generic Plans & the Slow-Overnight RunbookWhy estimates go wrong: independence assumption with correlated and anti-correlated columns (runnable q-errors and multiplicative error growth, Leis et al. VLDB 2015); real PG 17 CREATE STATISTICS dependencies/ndistinct fixing estimates; stale stats and out-of-range values; custom vs generic plans for skewed prepared statements (real plans + runnable heuristic sim), parameter sniffing contrast; auto_explain/pg_stat_statements; step-labeled slow-overnight runbook; pg_hint_plan and plan-pinning trade-offs.
- 5.Sorting, Aggregation, Spills & Parallel Query - External Merge, Top-N, HashAggregate vs GroupAggregate & work_mem MathSort strategies (quicksort, external merge, top-N heapsort, incremental sort) with a runnable external merge sort pass counter and top-N vs full sort comparisons; HashAggregate vs GroupAggregate; real PG 17 spills (external merge Disk, HashAggregate Batches/Disk Usage, hash_mem_multiplier) and a Gather/Partial Aggregate parallel plan; runnable work_mem worst-case sizing math; temp-file monitoring; MySQL/SQLite/DuckDB/warehouse contrasts.
- 6.Query Rewrites & SARGability - Unnesting, NOT IN vs NOT EXISTS, OR to UNION, Keyset Pagination & ORM N+1Subquery unnesting to semi/anti joins vs the NOT IN NULL trap (real PG 17 plans and counts plus runnable sqlite3 repro); SARGability: functions and casts on columns vs half-open ranges, MySQL string-vs-number index rule; BitmapOr vs OR-to-UNION; OFFSET vs keyset pagination (150,020 vs 20 rows read in real PG); runnable ORM N+1 fingerprint detector; rewrite catalog table.