query-performancelisted

# Query performance Almost every "the database is slow" turns out to be one of five things: a missing index, an index that can't be used, N+1 queries, fetching far more rows than needed, or connection pool exhaustion. Diagnose before optimizing — the fix for each is different, and guessing wastes a day. Examples are Postgres; the reasoning transfers, the syntax does not. ## Measure first ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT ...; ``` `EXPLAIN` alone shows the plan the planner *intends*. `ANALYZE` runs it and shows reality — which is what you need, because the gap between the two is the bug. Read it from the **innermost node outward**, looking for: - **Estimated vs actual rows.** `rows=10` vs `actual rows=48000` means the planner is working from bad statistics; it picked a nested loop that would have been fine for 10 rows and is catastrophic for 48000. Fix with `ANALYZE <table>`, or by making the predicate something the planner can estimate. - **Seq Scan on a large table** inside a loop, or where you expected an index. - **The node with the largest actual time**, not the one that looks scariest. Optimising anything else is wasted. - **Rows removed by filter** — you read a million rows to return ten. The index isn't selective enough, or is missing. - **Sort spilling to disk** (`external merge`) — either add an index that provides the order, or raise `work_mem` for that workload. Find the queries worth looking at with `pg_stat_statements`, ordered by **tot