Slow queries

Symptom: a page, job or report got slower, or the database is busy with no obvious cause.

Find the queries

With pg_stat_statements installed:

select round(total_exec_time) as total_ms, calls, round(mean_exec_time::numeric, 2) as mean_ms, left(query, 100) as query
from pg_stat_statements order by total_exec_time desc limit 10;

Sort by total_exec_time for the biggest overall cost, mean_exec_time for the slowest per call, and calls to spot N+1 patterns (thousands of tiny identical queries).

Read the plan

explain (analyze, buffers) select ...;

EXPLAIN ANALYZE really runs the statement. For writes, wrap it: begin; explain (analyze) update ...; rollback;.

You see	It usually means
`Seq Scan` on a big table with a `Filter`	Missing index for that filter
Estimated rows far from actual rows	Stale statistics: `ANALYZE`. Correlated columns: `CREATE STATISTICS`
`Sort Method: external merge Disk`	The sort spilled: raise `work_mem` for that role or query
`Nested Loop` with a huge `loops` count	Bad join order or missing index on the inner side
`Buffers: read` much larger than `hit`	Data isn't cached: index it, or the working set exceeds memory

Fix

create index concurrently on the filtered or joined columns. A partial index (where status = 'open') is smaller and faster when you query a small slice.
On SSD storage, set random_page_cost = 1.1 so the planner stops avoiding indexes.
A query that got slow right after a deploy or a bulk load is usually stale statistics: run ANALYZE.
Turn on auto_explain (auto_explain.log_min_duration = '500ms') to capture plans of slow queries as they happen.