SQL indexes
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 indexes
6 studies- 1.Indexes, Cardinality & EXPLAIN PlansB-tree vs hash; selectivity; composite/covering; Seq/Index/Bitmap; Nested/Hash/Merge; EXPLAIN ANALYZE pitfalls.
- 2.B-tree vs Hash vs GIN vs GiSTB-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.
- 3.Composite & Covering IndexesComposite 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.
- 4.Selectivity, Cardinality & StatisticsSelectivity 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.
- 5.EXPLAIN & EXPLAIN ANALYZEEXPLAIN 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.
- 6.Partial & Expression IndexesPartial 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.