---
name: "database_indexing"
description: "Diagnose slow SQL queries by reading EXPLAIN plans and fix them with the right indexes: composite column order, partial and covering indexes, and knowing when an index hurts instead of helping. Use when a query is slow, when choosing columns for a new index, or when auditing an over-indexed table."
metadata: { "includeInPrompt": true }
---

# Database Indexing

## Purpose

Turn "this query is slow" into a verified fix. The loop is: measure with
EXPLAIN, read the plan, add exactly the index the plan asks for, then prove
the fix with a second EXPLAIN. Nothing is added on a hunch.

## Workflow

1. **Reproduce and measure.** Run the slow query wrapped in
   `EXPLAIN (ANALYZE, BUFFERS)`. Never guess from the SQL text alone.
   Read the plan bottom-up: the most indented node executes first. See
   `references/explain-field-guide.md` for how each field reads.

2. **Name the bottleneck.** Feed the plan text to `bin/analyze_plan.py`
   (it flags the common smells) or read it by hand. The usual suspects:
   a Seq Scan with a large `Rows Removed by Filter` (rows read, rows
   thrown away), estimated rows far from actual rows (stale statistics),
   a Sort node on a hot path, a Nested Loop running thousands of times.

3. **Pick the index that matches.** One query pattern, one index:
   - Filter on one column: single-column B-tree.
   - Filter on several columns together: composite index, equality
     columns first, then the range or sort column.
   - Query only ever touches a subset of rows: partial index with
     a WHERE clause.
   - Query selects a few columns repeatedly: covering index with
     INCLUDE so the plan becomes an Index Only Scan.
   - Function applied to the column (e.g. `lower(email)`): expression
     index on exactly that expression.
   - JSONB, arrays, or full text: GIN. Naturally ordered time-series
     data: BRIN. Equality only and nothing else: hash.
   Column order in a composite index is load-bearing: an index on
   `(a, b, c)` serves queries on `a`, `(a, b)`, or `(a, b, c)`, but not
   on `b` or `c` alone.

4. **Create it safely.** On a live table use `CREATE INDEX CONCURRENTLY`
   so writes are not locked out. Give the index a descriptive name
   (`idx_orders_customer_created`) so the next person can tell what it
   is for.

5. **Prove it.** Re-run the same `EXPLAIN (ANALYZE, BUFFERS)`. The plan
   should show an index scan, `Rows Removed by Filter` should collapse,
   and execution time should drop. If the plan did not change, the index
   was not the fix: drop it and go back to step 2.

6. **Audit regularly.** Every index taxes every write. Periodically check
   for indexes that are never scanned and drop them. PostgreSQL keeps
   the evidence in `pg_stat_user_indexes`; the audit queries live in
   `references/explain-field-guide.md`.

## Output Contract

- A diagnosis stated in plan terms (which node, which numbers), not vibes.
- The exact `CREATE INDEX` statement, with a reason for the chosen
  columns and their order.
- Before and after `EXPLAIN (ANALYZE, BUFFERS)` output proving the
  improvement.
- A note on what was considered and rejected (e.g. "partial index did
  not fit because the query has no stable predicate").

## Operating Rules

1. Measure before and after, every time. An index added without a
   before/after comparison is a guess, not a fix.
2. Index real query patterns, not columns. Unused indexes cost writes
   and storage for zero benefit.
3. Never index a column wrapped in a function with a plain index: the
   planner cannot match `WHERE lower(email) = ?` to an index on
   `(email)`. Index the expression.
4. Respect the trade-off: indexes speed reads and slow writes. On a
   write-heavy table, add indexes sparingly and confirm the write cost
   is acceptable.
5. Do not add an index to fix a stale-statistics problem. If estimated
   rows and actual rows differ by an order of magnitude, run `ANALYZE`
   on the table first.
6. Small tables do not need indexes: a sequential scan of a table that
   fits in a few pages is cheaper than index overhead.
7. Foreign key columns in PostgreSQL are not indexed automatically.
   Index them when the child table is joined or the parent row is
   deleted.