A 4-second query turned into a 40 ms index scan.
Read the skill at https://ai-skills-vault.pages.dev/skills/postgres-index-tuning.md and install it as a skill in your agent. Follow it exactly, including its checklists. Then ask me the questions it requires and do the work.
A 4-second query is almost never a missing index. It is a sequential scan because the index exists on the wrong column order, or a sort that spills to disk because work_mem is 4 MB, or statistics so stale the planner thinks 40 rows match when 400,000 do.
This skill reads the plan before adding anything, then adds the smallest index that removes the work - and measures the buffers read, not just the wall clock.
Do NOT use for: hardware sizing, connection pooling problems (that is pgbouncer, not indexes), or a query that is slow because it returns 200,000 rows to the app - that is a product problem.
1.1 Get the exact SQL, with the parameter values, from pg_stat_statements rather than from the application code. The code may have changed; the slow query is what ran.
1.2 Find the top offenders by total time, not by mean time:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms,
rows, round(100.0 * shared_blks_read / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS miss_pct,
left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;1.3 Note the parameters that make it slow. A query fast for one tenant and slow for another is a data-distribution problem, not a query problem.
1.4 Record the baseline: wall time, rows returned, and buffers read. Wall time alone is noisy; buffers read is the number that tells you whether the work changed.
1.5 Reset statistics after a change and let them accumulate for at least a day before judging.
| Metric | Where | Why it matters |
|---|---|---|
| Mean exec time | pg_stat_statements | the number users feel |
| Total exec time | pg_stat_statements | where the hours go |
| Shared blocks read | EXPLAIN BUFFERS | the real work done |
| Rows returned | EXPLAIN ANALYZE | whether the plan is honest |
| Calls | pg_stat_statements | frequency times cost |
Gate: the exact query, its parameters, and a baseline of time plus buffers.
2.1 Always EXPLAIN (ANALYZE, BUFFERS) with real parameters. EXPLAIN alone shows an estimate; ANALYZE shows what happened.
2.2 Read the plan bottom-up: the deepest node runs first, and its row count drives everything above it.
2.3 Look for four things in order: a sequential scan on a large table, a sort that spills, a nested loop with a high loop count, and an estimated-versus-actual row gap above 10x.
| Node | Healthy sign | Warning sign |
|---|---|---|
| Seq Scan | small table, under 10k rows | on a table with millions of rows |
| Index Scan | few rows, low buffers | high loop count in a nested loop |
| Sort | Sort Method: quicksort, Memory: 512kB | external merge Disk: 40MB |
| Hash Join | one hash, modest batches | Batches: 32 means memory too small |
| Nested Loop | inner side 1-50 rows | inner side 50,000 rows, 10,000 loops |
2.4 Compare rows= (estimate) with actual rows=. A 100x gap means stale statistics or a correlated predicate, and no index fixes a bad estimate.
2.5 Check Buffers: shared hit=... read=.... A plan reading 400,000 buffers to return 20 rows is doing 20,000x more work than it needs.
Gate: a written list of the nodes doing the most work, with buffer counts.
3.1 Run ANALYZE table_name; first. It costs seconds and occasionally removes the problem entirely.
3.2 Increase the statistics target for skewed columns: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; then ANALYZE.
3.3 For correlated columns (city and pincode, tenant and created_at), create extended statistics:
CREATE STATISTICS orders_tenant_created (dependencies, ndistinct)
ON tenant_id, created_at FROM orders;
ANALYZE orders;3.4 Check for a huge n_dead_tup in pg_stat_user_tables. Bloat makes the planner read more pages than the live data needs.
3.5 Only after statistics are fresh do you know whether an index is the answer.
Gate: actual rows within 3x of rows= on the main node.
4.1 Equality columns first, then the range column, then the sort column. (tenant_id, status, created_at) serves WHERE tenant_id=? AND status=? ORDER BY created_at DESC.
4.2 Put the column used in the ORDER BY last, in the matching direction, so the sort disappears from the plan.
4.3 Add a covering INCLUDE for the two or three columns the query selects. It turns a heap fetch into an index-only scan:
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC)
INCLUDE (total, currency)
WHERE deleted_at IS NULL;4.4 Use a partial index when the query always filters one way: WHERE deleted_at IS NULL keeps the index small and hot.
4.5 Use an expression index for a computed predicate: CREATE INDEX ON users (lower(email)); for case-insensitive lookups.
4.6 One index serves many queries; two overlapping indexes serve none well. Check pg_indexes for prefixes of your new index before creating it.
| Query shape | Index |
|---|---|
WHERE a=? AND b=? ORDER BY c DESC | (a, b, c DESC) |
WHERE a=? AND c > now()-'7 days' | (a, c) partial on the status filter |
WHERE lower(email)=? | (lower(email)) |
WHERE a=? ORDER BY c LIMIT 20 | (a, c) INCLUDE (selected cols) |
WHERE a=? AND b IN (...) | (a, b) with the planner choosing a bitmap scan |
Gate: one index proposed, with the query it serves named.
5.1 CREATE INDEX CONCURRENTLY on any table taking writes. The plain form blocks writes for the whole build.
5.2 Run it outside a transaction block; CONCURRENTLY fails inside one.
5.3 Expect two table scans and a wait on old snapshots. Set lock_timeout = '5s' so a stuck build fails instead of blocking.
5.4 Verify it is valid afterwards; a failed concurrent build leaves an invalid index that still costs write time:
SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;5.5 Drop any invalid index with DROP INDEX CONCURRENTLY before retrying.
5.6 Raise maintenance_work_mem to 256 MB-1 GB for the session and keep max_parallel_maintenance_workers at 1-2 during peak.
Gate: index valid, no write stall longer than 2 seconds during the build.
6.1 Re-run EXPLAIN (ANALYZE, BUFFERS) with the same parameters. Same query, same data, different plan.
6.2 Compare buffers read, not just time. A drop from 400,000 to 900 buffers is the proof; a time drop on a warm cache is not.
6.3 Confirm the sort node is gone, and that the scan is Index Scan or Index Only Scan rather than a bitmap scan with a large recheck.
6.4 Measure at the 95th percentile of data size: a tenant with 10 rows proves nothing. Test with the biggest tenant you have.
6.5 Keep the before and after plans in the PR. The next person needs to know why the index exists.
| Check | Before | Target |
|---|---|---|
| Plan node | Seq Scan | Index Scan |
| Buffers read | record | 90% lower |
| Sort | external merge | none |
| Wall time | record | 10x faster or under 50 ms |
| Rows rechecked | n/a | under 10% of scanned |
Gate: before and after plans attached, buffers down 90%.
7.1 Every index is a write. Five indexes on a table taking 500 inserts a second is five extra B-tree updates per row.
7.2 Measure insert latency before and after on a table with real write volume. If p95 insert latency rose more than 10%, the index needs to earn its place.
7.3 Audit unused indexes monthly:
SELECT s.relname AS table, s.indexrelname AS index, s.idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND s.relname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(s.indexrelid) DESC;7.4 Drop indexes with zero scans after 30 days, unless they back a constraint or a quarterly report.
7.5 Beware duplicate indexes: (a) and (a, b) are different, but (a, b) and (a, b) from two migrations are not - check before you add.
Gate: index count and write latency recorded together.
8.1 LIKE '%term%' cannot use a B-tree. Use a trigram index (pg_trgm) or a real search index.
8.2 OR across different columns often defeats a single index. Rewrite as a UNION ALL of two indexed queries.
8.3 OFFSET 100000 LIMIT 20 reads and discards 100,000 rows. Use keyset pagination: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20.
8.4 A function on the column kills the index: WHERE date(created_at) = '2026-11-15' needs a range predicate instead.
8.5 COUNT(*) on a filtered set is a scan by definition. Keep a counter table if the UI needs the number.
8.6 A join with no index on the foreign key is a nested loop with a seq scan per row. Index every foreign key.
Gate: each resistant shape has a written workaround or an accepted cost.
9.1 work_mem is per sort or hash node, not per connection. A 4 MB default spills any sort over about 20,000 rows. Raise it to 16-32 MB for the reporting role, not globally.
9.2 random_page_cost at 4.0 is a spinning-disk default. On SSD, 1.1 tells the planner that random reads are cheap, and it starts choosing index scans it should have chosen already.
9.3 effective_cache_size should be about 60-75% of total RAM, so the planner knows what the OS will cache.
9.4 shared_buffers at 25% of RAM is the standard starting point; past 8-16 GB the returns flatten.
9.5 autovacuum_vacuum_scale_factor at 0.2 is too lazy for a 50M-row table. Set 0.01 per table for the big ones.
9.6 Set jit = off on OLTP workloads if plans show JIT compilation overhead on short queries; it helps analytical scans and hurts 2 ms point lookups.
9.7 Change one setting at a time and keep the plan that justified it in the PR. Two settings changed together cannot be attributed.
| Setting | Default | Common fix |
|---|---|---|
| work_mem | 4 MB | 16-32 MB per reporting role |
| random_page_cost | 4.0 | 1.1 on SSD |
| effective_cache_size | 4 GB | 60-75% of RAM |
| shared_buffers | 128 MB | 25% of RAM |
| autovacuum scale factor | 0.2 | 0.01 on large tables |
Gate: any setting change recorded with the plan that justified it.
10.1 Add the query to a weekly check that logs the top 10 by total time, so a regression is visible before a user reports it.
10.2 Alert when any single query's mean time doubles week over week.
10.3 Re-ANALYZE after a bulk load or a backfill, before judging any plan.
10.4 Keep a perf-queries.md with each tuned query, its index, and the buffer counts before and after.
10.5 Re-check plans after a Postgres major upgrade; the planner changes and an index that was ignored may become the chosen path.
Gate: weekly report running, perf-queries.md current.
An orders list endpoint, 12M rows, 4.1 seconds.
| Step | Plan | Buffers | Time |
|---|---|---|---|
| Baseline | Seq Scan + external merge sort | 412,000 | 4.1 s |
| After ANALYZE | Seq Scan, better estimate | 412,000 | 3.9 s |
| Composite index (tenant, status, created_at DESC) | Index Scan + Sort | 31,000 | 420 ms |
| Add INCLUDE (total, currency) | Index Only Scan | 2,400 | 38 ms |
| Partial WHERE deleted_at IS NULL | Index Only Scan, smaller index | 900 | 21 ms |
The first index removed 92% of the buffers; the covering change removed 92% of what was left. The ANALYZE step changed nothing on its own, which is why it comes before the index and not instead of it.
EXPLAIN (ANALYZE, BUFFERS) with real parametersANALYZE run, and statistics target raised on skewed columns| Symptom | Real cause | Fix |
|---|---|---|
| Index exists, planner still seq scans | Estimate says most rows match, so a scan is cheaper | Fix statistics, or use a partial index matching the real filter |
| Query slower after adding an index | Planner switched to a nested loop with a bad estimate | ANALYZE, raise statistics target on the join column |
| Sort still spills to disk | work_mem 4 MB for a 200,000-row sort | Raise work_mem for the role running the report |
| Writes slowed 30% after tuning | Four indexes on a hot insert path | Drop unused indexes, keep one composite |
| Index build blocked writes for 6 minutes | CREATE INDEX without CONCURRENTLY | Rebuild with CONCURRENTLY outside a transaction |
| Index invalid after a failed build | Concurrent build cancelled midway | Drop CONCURRENTLY, rebuild, verify indisvalid |
LIKE '%x%' never uses the index | B-tree cannot serve a leading wildcard | pg_trgm GIN index, or a search engine |
| Page 4,000 of results is slow | OFFSET reads and discards every row | Keyset pagination on (created_at, id) |
| Fast for one tenant, slow for another | Data skew, one tenant has 100x the rows | Test with the largest tenant, consider per-tenant statistics |
Read the skill at the URL above and tune a slow Postgres query for me. Ask me for the exact query with its parameters, the table sizes, the Postgres version and whether the table takes writes. Then ask me to paste EXPLAIN (ANALYZE, BUFFERS) output and read it back to me: which node does the most work, where the estimate is wrong, and how many buffers it reads to return how many rows. Then give me the statistics fixes first, and one index with the exact column order, INCLUDE list and WHERE clause, built CONCURRENTLY. Finish with the before and after buffer counts to expect, the write-cost check, and the unused-index query. Do not add an index before the statistics are fresh.