Learning Pathway
02 — Performance: Query & Index Mastery
12 lessons- 01 Reading EXPLAIN: How the Planner Describes a Query How to read EXPLAIN and EXPLAIN ANALYZE: plan nodes, estimated vs actual rows, startup vs total cost, and what loops and buffers reveal.
- 02 How the Cost-Based Optimizer Chooses a Plan How the PostgreSQL planner works: path generation, cost estimation from pg_statistic, the dynamic-programming join search, and GEQO for many-way joins.
- 03 B-Tree Indexes: Structure and When They Win How PostgreSQL B-tree indexes work: the Lehman-Yao structure in nbtree, leaf and internal pages, ordered scans, and index-only scans.
- 04 Beyond B-Tree: GIN, GiST, BRIN, and Hash Indexes When to use each PostgreSQL index type: GIN for arrays/jsonb/full-text, GiST for geometry and ranges, BRIN for huge ordered tables, and hash for equality.
- 05 Statistics and Selectivity: Why the Planner Guesses Wrong How pg_statistic drives planner estimates: MCVs, histograms, n_distinct, correlation, and how extended statistics fix multi-column misestimates.
- 06 work_mem and Spills: Where Sorts and Hashes Go to Disk How work_mem governs in-memory sorts, hash joins, and hash aggregates, when operations spill to temp files, and how to size it safely.
- 07 HOT Updates: How PostgreSQL Avoids Index Writes How Heap-Only Tuple updates work: the conditions that enable HOT, the redirect line pointer, page pruning, and why fillfactor matters for write-heavy tables.
- 08 Parallel Query: How PostgreSQL Splits Work Across Workers How parallel query works: the Gather node, parallel-aware scans, worker allocation, and the settings that decide when PostgreSQL goes parallel.
- 09 Bitmap Scans: How PostgreSQL Combines Indexes How bitmap heap scans work: building TID bitmaps, combining multiple indexes with AND/OR, lossy bitmaps, and where they beat plain index scans.
- 10 Partitioning: How Declarative Partitioning Prunes Work How declarative partitioning works: range/list/hash methods, the partition routing path on insert, and partition pruning at plan time and execution time.
- 11 Connection Overhead: Why Pooling Is Mandatory at Scale Why each PostgreSQL connection is a process, the per-backend memory and snapshot cost, and how PgBouncer pooling modes solve connection scaling.
- 12 Caching Plans: Prepared Statements and the Generic Plan How PostgreSQL caches query plans: custom vs generic plans, the five-execution heuristic, plan_cache_mode, and when generic plans hurt.