magnus919_agent-skills/postgres/references/02-indexes-and-query-plans.md

Indexes and Query Plans

Last Updated: 2026-08-03

This reference covers measuring and operating indexes and diagnosing query plans. Designing which index to add for a new workload is schema design and routes to data-architect; this skill owns reading the evidence and operating what exists.

Evidence first: statistics views

-- Which indexes are actually used, most used first
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC LIMIT 10;

-- Indexes with no recorded scans (write cost with no read benefit)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;

-- Tables scanned sequentially a lot (candidate for a missing index)
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC LIMIT 10;

-- Indexes marked unusable (dropped on next vacuum)
SELECT c.relname AS index_name, i.indrelid::regclass AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

The bundled pgdiag script runs these probes in its index_usage, unused_indexes, invalid_indexes, and seq_scan_heavy checks.

Reading a plan

• EXPLAIN shows the planner's estimate; EXPLAIN (ANALYZE, BUFFERS) executes the statement and reports actual rows, timing, and buffer usage. Read the estimated-versus-actual row counts: a large mismatch means the planner is working from stale or missing statistics.
• EXPLAIN (FORMAT JSON) is the machine-readable form: scripts/pgdiag --plan-for "SELECT ..." --json.
• Node shapes to recognize: Seq Scan (whole-table read), Index Scan / Index Only Scan (index seek), Bitmap Heap Scan (index + heap filter), Nested Loop/Hash Join/Merge Join, Sort, Materialize.

Common findings and next steps

Finding	Likely cause	Check before acting
Seq Scan on a filtered large table	Missing index or planner cost settings	pg_stat_user_tables.seq_tup_read; actual row estimate vs random_page_cost/effective_cache_size
Estimated rows far from actual	Stale pg_statistic	Has autovacuum/autoanalyze run since the last big change?
Index with idx_scan = 0 for weeks	Unused index (write overhead)	Confirm the access path is genuinely gone, then a deliberate drop in a maintenance window
Invalid index	Interrupted build / catalog issue	Repair deliberately; never assume it is serving queries
Index Only Scan not used	Visibility map not set	Check pg_class.relallvisible; the heap pages are probably dirty

Query-plan hygiene

• Test plans on the real workload shape, not on tiny samples: statistics scale with data, and a plan that is right on a 1k-row table is often wrong at 10M.
• Change planner parameters one at a time and re-measure with EXPLAIN (ANALYZE, BUFFERS) before and after.
• pg_stat_statements (extension) is the best aggregate evidence for which statements deserve plan work. See 06-extensions.md for enabling it.
• Never "fix" a slow query by disabling index scans globally (enable_indexscan=off): treat that as a diagnostic probe, not a fix.

Verification

After any index change: re-run the affected query's EXPLAIN (ANALYZE, BUFFERS) and compare total time and rows, and re-check idx_scan trends on the next reporting window. A plan change is verified by measurement, not by assertion.