Chapter 23 — Further Reading

The essential resource (everyone)

  • Markus Winand, Use the Index, Luke! — https://use-the-index-luke.com/ (free) and SQL Performance Explained (book). The clearest explanation anywhere of how indexes work and how to use them well — B-trees, composite-column order, why functions defeat indexes, covering indexes. If you read one thing on indexing, read this.

Official reference (everyone)

  • PostgreSQL Docs: "Indexes" (the whole chapter) — index types (B-tree, Hash, GIN, GiST, SP-GiST, BRIN), multi-column, partial, expression, covering (INCLUDE), unique, and CREATE INDEX CONCURRENTLY. https://www.postgresql.org/docs/current/indexes.html
  • PostgreSQL Docs: "Monitoring → The Statistics Collector" — pg_stat_user_indexes (finding unused indexes, Case Study 2).

Index types in depth (🔬 CS Student · 🏗️ DBA)

  • B-tree internals — articles on B-tree/B+tree structure and height; ties to Chapter 28 (storage).
  • GIN and GiST docs — for JSONB, full-text, arrays (Ch. 16), and ranges/spatial.
  • BRIN indexes — block-range indexes for huge, naturally-ordered tables (time-series); tiny and fast for the right shape.

Tuning practice (🏗️ DBA · 💻 Developer)

  • "Finding and removing unused/duplicate indexes" — the pg_stat_user_indexes and pg_index queries behind Case Study 2.
  • "Index-only scans and covering indexes" — when the database answers from the index alone.
  • "Why isn't my index being used?" — function-on-column, type mismatch, low selectivity, stale statistics (preview of Chapter 24).

Reference (this book)

  • Chapter 24 — Query Optimization: reading EXPLAIN to see index use (the natural sequel).
  • Chapter 16 — Advanced SQL: the GIN-indexed JSONB/full-text features.
  • Appendix G — EXPLAIN and Tuning Reference: index decisions on one page.

Do, don't just read

  • Live the anchor: on generate_data.sql, run a customer_id query, see the Seq Scan, add the index, see the Index Scan and the speedup (Exercise 23.1–23.3).
  • Audit Mercado's indexes with pg_stat_user_indexes after running some queries — see which get used.
  • Build a composite (customer_id, order_date DESC) index and confirm (via EXPLAIN) it eliminates the sort for a "recent orders" query.

Next: Chapter 24 — Query Optimization: reading EXPLAIN plans and making queries fast.