database_indexing v1.0.0

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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.