TopicsSQL
SQL
Query plans, joins, and the index the interviewer hopes you mention.
Common tags: joins, plans, window-functions
- SQL
Sorting, Aggregation, Spills & Parallel Query - External Merge, Top-N, HashAggregate vs GroupAggregate & work_mem Math
Cluster · SQL Query Execution & Optimizer
Sort 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.
Open study →- sql
- query-optimizer
- query-planner
- query-execution
- explain-analyze
- join-algorithms
- hash-join
- merge-join
- nested-loop
- cost-based-optimizer
- join-ordering
- cardinality-estimation
- extended-statistics
- work-mem
- parallel-query
- sargability
- postgresql
- interview
- SQL
Query Rewrites & SARGability - Unnesting, NOT IN vs NOT EXISTS, OR to UNION, Keyset Pagination & ORM N+1
Cluster · SQL Query Execution & Optimizer
Subquery 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.
Open study →- sql
- query-optimizer
- query-planner
- query-execution
- explain-analyze
- join-algorithms
- hash-join
- merge-join
- nested-loop
- cost-based-optimizer
- join-ordering
- cardinality-estimation
- extended-statistics
- work-mem
- parallel-query
- sargability
- postgresql
- interview
- SQL
How a SQL Query Actually Executes - Parse, Rewrite, Plan, Execute, Volcano vs Vectorized & Reading Plan Trees
Cluster · SQL Query Execution & Optimizer
Interview 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.
Open study →- sql
- query-optimizer
- query-planner
- query-execution
- explain-analyze
- join-algorithms
- hash-join
- merge-join
- nested-loop
- cost-based-optimizer
- join-ordering
- cardinality-estimation
- extended-statistics
- work-mem
- parallel-query
- sargability
- postgresql
- interview
- SQL
Join Algorithms - Nested Loop vs Index Nested Loop vs Hash Join vs Merge Join
Cluster · SQL Query Execution & Optimizer
Nested 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.
Open study →- sql
- query-optimizer
- query-planner
- query-execution
- explain-analyze
- join-algorithms
- hash-join
- merge-join
- nested-loop
- cost-based-optimizer
- join-ordering
- cardinality-estimation
- extended-statistics
- work-mem
- parallel-query
- sargability
- postgresql
- interview
- SQL
Cost-Based Optimizer & Join Ordering - Cost Constants, Dynamic Programming, Left-Deep vs Bushy, join_collapse_limit & GEQO
Cluster · SQL Query Execution & Optimizer
PostgreSQL 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.
Open study →- sql
- query-optimizer
- query-planner
- query-execution
- explain-analyze
- join-algorithms
- hash-join
- merge-join
- nested-loop
- cost-based-optimizer
- join-ordering
- cardinality-estimation
- extended-statistics
- work-mem
- parallel-query
- sargability
- postgresql
- interview
- SQL
Cardinality Misestimates & Plan Regressions - Correlated Columns, Extended Statistics, Generic Plans & the Slow-Overnight Runbook
Cluster · SQL Query Execution & Optimizer
Why 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.
Open study →- sql
- query-optimizer
- query-planner
- query-execution
- explain-analyze
- join-algorithms
- hash-join
- merge-join
- nested-loop
- cost-based-optimizer
- join-ordering
- cardinality-estimation
- extended-statistics
- work-mem
- parallel-query
- sargability
- postgresql
- interview
- SQL
Window Functions — PARTITION BY, ORDER BY & Frames
Cluster · SQL Analytics
OVER () computes an aggregate without collapsing rows. Interviews love frames because the default is surprising. Master PARTITION BY, ORDER BY, and ROWS vs RANGE before ranking and running totals.
Open study →- sql
- window-functions
- frames
- partition-by
- interview
- SQL
SQL Analytics — Window Functions, CTEs & Set-Based Thinking
Cluster · SQL Analytics
Senior interviews fail when SQL is written as a loop over rows. Window functions, CTEs, and set-based thinking turn Top-N, running totals, and hierarchies into declarative plans. This hub maps the cluster. Indexes and EXPLAIN stay in the existing SQL Indexes pages.
Open study →- sql
- window-functions
- cte
- set-based
- analytics
- interview
- SQL
Running Totals, LAG/LEAD & Gaps-and-Islands
Cluster · SQL Analytics
Time-series analytics in SQL is cumulative sums, period-over-period deltas, and session islands. These are window problems first. Recursive CTEs are usually overkill for a linear sequence.
Open study →- sql
- running-totals
- lag
- lead
- gaps-and-islands
- interview
- SQL
Ranking & Top-N — ROW_NUMBER, RANK, DENSE_RANK, NTILE
Cluster · SQL Analytics
Top-N per group is a senior interview staple. The wrong ranking function silently changes product behavior on ties. Compare ROW_NUMBER, RANK, DENSE_RANK, NTILE, and Postgres DISTINCT ON.
Open study →- sql
- ranking
- top-n
- row-number
- distinct-on
- interview
- SQL
LATERAL Joins vs Correlated Subqueries — When to Choose What
Cluster · SQL Analytics
LATERAL lets a subquery or set-returning function see columns from preceding FROM items. It shines for per-row Top-N and table functions. Compare it with correlated subqueries and with a window Top-N.
Open study →- sql
- lateral
- correlated-subquery
- top-n
- interview
- SQL
CTEs — Non-Recursive vs Recursive Hierarchies
Cluster · SQL Analytics
WITH clauses stage complex analytics. Recursive CTEs walk trees such as org charts. Interviews probe readability versus materialization, and how you stop a cycle.
Open study →- sql
- cte
- with-recursive
- hierarchies
- materialized
- interview
- SQL
Selectivity, Cardinality & Statistics
Cluster · SQL indexes
Selectivity is the fraction of rows matching a predicate. Cardinality is the estimated row count after filters/joins. Planners use statistics (ANALYZE) — n_distinct, MCV, histograms — to choose Seq Scan vs Index Scan. Bad stats mean bad plans.
Open study →- sql
- postgres
- SQL
Partial & Expression Indexes
Cluster · SQL indexes
Partial indexes index only rows matching a WHERE (e.g. WHERE deleted_at IS NULL) — smaller, hotter cache, perfect for sparse predicates. Expression / functional indexes index LOWER(email) or (data->>'sku') so queries using the same expression can seek. Match expressions exactly.
Open study →- sql
- postgres
- SQL
EXPLAIN & EXPLAIN ANALYZE
Cluster · SQL indexes
EXPLAIN shows the plan plus estimates. EXPLAIN (ANALYZE, BUFFERS) executes and shows actual time/rows plus buffer hits. Always compare Plan Rows vs Actual Rows; big mismatches mean stats or correlation problems. Never run ANALYZE on unchecked writes in prod without a transaction you can roll back.
Open study →- sql
- postgres
- SQL
Composite & Covering Indexes
Cluster · SQL indexes
Composite indexes follow leftmost prefix rules — (a,b,c) serves a, a,b, a,b,c, not b alone. Covering indexes (INCLUDE / index-only scans) store enough columns to avoid heap fetches. Column order is equality first, then range/sort.
Open study →- sql
- postgres
- SQL
B-tree vs Hash vs GIN vs GiST
Cluster · SQL indexes
B-tree is the default for equality + range + ORDER BY. Hash is equality-only (niche). GIN shines for arrays/JSONB/full-text (inverted). GiST powers geometry, ranges, and trigram similarity. Pick the access method by operator class, not vibes.
Open study →- sql
- postgres
- SQL
Indexes, Cardinality & EXPLAIN Plans
Cluster · SQL indexes
B-tree vs hash; selectivity; composite/covering; Seq/Index/Bitmap; Nested/Hash/Merge; EXPLAIN ANALYZE pitfalls.
Open study →- sql
- postgres