## TL;DR
Find the slow query first (pg_stat_statements or the slow query log), run EXPLAIN ANALYZE on it in a read-only transaction, and look for the usual suspects: sequential scan on a big table, missing index, or a bad plan from stale statistics. Fix with an index or a query rewrite, and verify the plan changed before calling it done. Never run experimental DDL on production without testing the lock behavior first.

## Error / query
```text
how to debug a slow database query in production
```

## Use this skill when
- p95 latency spiked and the database is the prime suspect
- An alert names a specific slow query or a specific endpoint backed by one query
- You need to decide between adding an index, rewriting the query, or scaling the DB
- A deploy made queries slower and you need the before/after plan

## Not for this skill when
- The whole database is slow (connections exhausted, disk full); fix the host first
- The slowness is in the app (N+1 queries, chatty ORM); the database is the victim, not the cause
- You are designing schema for a new feature; that is design work, not debugging

## Steps

### Step 1: Find the slowest queries by total time
```bash
psql -c "SELECT query, calls, round(total_exec_time::numeric,2) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
```
Expected: the top queries by total time; the slowest one by mean time on high calls is your target.

### Step 2: Get the execution plan without running side effects
```bash
psql -c "BEGIN READ ONLY; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 100; ROLLBACK;"
```
Expected: the plan with actual timings; look for "Seq Scan" on large tables, the single most common cause.

### Step 3: Check whether the table statistics are stale
```bash
psql -c "SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE relname = 'orders';"
```
Expected: recent analyze timestamps; if they are old or null, run ANALYZE and re-check the plan before adding indexes.

### Step 4: Check for missing indexes on the filter columns
```bash
psql -c "SELECT indexname FROM pg_indexes WHERE tablename = 'orders';"
```
Expected: the index list; if there is no index on the WHERE/ORDER BY columns from step 2, that is very likely the fix.

### Step 5: Verify the fix changed the plan and the timing
```bash
psql -c "BEGIN READ ONLY; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 100; ROLLBACK;" | grep -E 'Index|Seq Scan|Execution Time'
```
Expected: "Index Scan" replacing "Seq Scan" and a much lower execution time; confirm in production metrics, not just EXPLAIN.

## Variant phrasings

### "Slow query in MySQL"
Use the slow query log plus EXPLAIN FORMAT=JSON; the same seq-scan-versus-index logic applies.

### "Query was fast yesterday, slow today"
Stale statistics or a plan flip after an ANALYZE or a data growth threshold; compare the current plan to yesterday's and check last_analyze.

### "How to find N+1 queries"
They show up in pg_stat_statements as huge call counts with tiny mean times; fix in the app (eager loading), not with indexes.

## Why it happens
Queries slow down when the planner picks a bad path: no usable index, statistics that no longer describe the data, or a data volume that crossed the threshold where the old plan stops working. The plan tells you which one.

## Edge cases and pitfalls
- CREATE INDEX on a big production table takes an ACCESS EXCLUSIVE lock without CONCURRENTLY; use CREATE INDEX CONCURRENTLY.
- EXPLAIN ANALYZE actually runs the query; wrap writes in a rolled-back transaction or test on a replica.
- Parameter sniffing: the plan for one parameter value can be terrible for another; test with representative values.
- Do not add an index per slow query blindly; every index slows writes and the fifth overlapping index helps nobody.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ahnAGWJ67akKmRehQ2vg_A