Wrong Composite Index Order

A production incident, replayed by hand: the per-tenant listing is slow even though the table already has an index. Bring up the same database, prove the existing index cannot serve the query, and fix the column order so the plan flips.

The per-tenant listing endpoint crawls, and the first reply in the channel is "but we already have an index on that table". That is exactly what makes this incident instructive: an index existed — with its columns in the wrong order. This book replays the case on a database you bring up yourself, all in plain SQL files. You reproduce the slow endpoint, catch the query in pg_stat_statements, list the table's indexes to see one is present, then read EXPLAIN (ANALYZE, BUFFERS) to understand WHY a B-tree leading with a low-selectivity column cannot seek for this query. One index with the right column order — equality predicates first, the ORDER BY column last — flips the plan and closes the ticket.

What you'll build

• Reproduce a latency incident where an index already exists
• List a table's indexes and read their column order critically
• See why a B-tree helps only along its leftmost columns
• Order composite index columns: equality first, ORDER BY last
• Verify the fix by comparing plans, not by faith

Contents

1. The stand
2. Bring it up
3. The schema — with the index everyone remembers
4. Apply the schema
5. The data
6. Load it
7. A clean slate for statistics
8. Reproduce the pain
9. Let the traffic flow
10. Ask the database what hurts
11. There IS an index
12. Convict it with a plan
13. Design the right order
14. Apply it
15. Watch the plan flip
16. Close the ticket