postgres-performance

What this skill does

# PostgreSQL Performance — Query & Tuning

## Overview

PostgreSQL's planner is **cost-based**: for each query it estimates the cost of many
candidate plans (in arbitrary units where `1.0` ≈ one sequential page fetch) and runs the
cheapest. Tuning is the craft of (a) **seeing** what plan it chose and why, then (b) pulling the
**levers** that change its decision. There are four levers, and almost every fix is one of them:

1. **EXPLAIN** — the lens. Read the plan; compare the planner's **estimated** rows to the
   **actual** rows. Estimate errors are the root cause of most bad plans.
2. **Indexes** — give the planner a cheaper access path than scanning the whole table.
3. **Statistics** — `ANALYZE` and extended statistics fix the row estimates that drive plan choice.
4. **VACUUM / config** — keep dead tuples and bloat from inflating every scan; tune planner GUCs
   (`random_page_cost`, `work_mem`, `effective_cache_size`) so cost estimates match your hardware.

**Mental model:** a slow query is a symptom. Don't reach for hints (Postgres has none) — find the
*wrong estimate* or the *missing access path* and fix the cause. `EXPLAIN ANALYZE` tells you which.

> **Disambiguation.** This skill is **query & performance tuning**. For the **`psql`** client
> (`\timing`, `\x`, `\di`, running these commands) → **psql** skill. For **writing** the SQL/DDL
> itself (SELECT, CTEs, window functions, join syntax) → **postgres-sql**. For **server config,
> replication, and deep autovacuum GUC strategy** → **postgres-admin** (this skill covers VACUUM
> *mechanics & why* and *planner* GUCs; server-wide sizing lives there). For **installing** the
> profiling extensions named below → **postgres-extensions**.

### A note on version annotations

Inline `(pgNN+)` marks the **minimum major version** that supports a feature, verified against the
PostgreSQL docs/release notes. Anything unannotated is **bedrock** — present since the old 9.x line
— and safe to assume everywhere. **PG18 is current stable; PG19 is beta** (tagged `(pg19+, beta)`).
The full feature → version map with sources is in
[references/version-features.md](references/version-features.md). Confirm your server with
`SELECT version();` or `SHOW server_version;`.

## The slow-query loop

```
 ① FIND the slow query        pg_stat_statements (total/mean time) ─┐  → postgres-extensions
 ② SEE the plan               EXPLAIN (ANALYZE, BUFFERS) <query>    │
 ③ READ it                    estimate vs actual? scan? join? sort spill?
 ④ FIX the cause              index │ ANALYZE/stats │ rewrite │ VACUUM │ GUC
 ⑤ VERIFY                     re-run EXPLAIN ANALYZE; compare timing & rows
```

Always measure with `EXPLAIN (ANALYZE, BUFFERS)` on a realistic data volume — plans on a 100-row
toy table do not predict plans on 100M rows (the planner switches strategies with size).

## EXPLAIN and EXPLAIN ANALYZE

`EXPLAIN` shows the **estimated** plan (instant, no execution). `EXPLAIN ANALYZE` **runs the query**
and adds **actual** timings and row counts so you can check the planner's guesses.

```sql
EXPLAIN <query>;                          -- plan + cost estimates only
EXPLAIN ANALYZE <query>;                   -- actually executes; adds actual time/rows (+ BUFFERS in pg18+)
EXPLAIN (ANALYZE, BUFFERS) <query>;        -- the workhorse: plan + reality + I/O
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) <query>;
```

> ⚠️ `ANALYZE` **executes the statement** — for `INSERT/UPDATE/DELETE/MERGE` wrap it so it rolls back:
> `BEGIN; EXPLAIN ANALYZE <dml>; ROLLBACK;`

### Options (PG19 synopsis)

| Option | What it adds | Since |
|--------|--------------|-------|
| `ANALYZE` | Execute and show **actual** time, rows, loops | bedrock |
| `BUFFERS` | Shared/local/temp blocks hit/read/dirtied/written (I/O) | option bedrock; **auto-on with `ANALYZE` (pg18+)** |
| `VERBOSE` | Output columns, schema-qualified names, per-worker detail; +CPU/WAL/avg-read (pg18+) | bedrock |
| `COSTS` | Estimated startup/total cost, rows, width (on by default) | bedrock |
| `TIMING` | Per-node actual time (on with ANALYZE; turn **off** to cut clock overhead) | bedrock |
| `SETTINGS` | Non-default planner GUCs in effect | `(pg12+)` |
| `WAL` | WAL records/bytes/FPIs generated (needs ANALYZE) | `(pg13+)` |
| `GENERIC_PLAN` | Plan a parameterized query with `$1` placeholders (no ANALYZE) | `(pg16+)` |
| `SERIALIZE` | Cost of converting rows to text/binary for the client (`NONE`/`TEXT`/`BINARY`) | `(pg17+)` |
| `MEMORY` | Planner memory consumption | `(pg17+)` |
| `IO` | Asynchronous-I/O (AIO) stats per scan node (needs ANALYZE) | `(pg19+, beta)` |
| `FORMAT` | `TEXT` (default) / `JSON` / `XML` / `YAML` — JSON/YAML are machine-parseable | bedrock |

### Anatomy of an `EXPLAIN ANALYZE` line

```
Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1.00 loops=10)
└── node type & target          ├ startup..total cost  ├ EST rows  ├ width   ├ first-row..all-rows ms  ├ ACTUAL rows  └ executions
```

- **cost=startup..total** — planner's estimate in cost units; a parent's cost includes its children.
- **rows** (in cost parens) = **estimated** rows; **rows=N.NN** in the actual parens = **actual**
  rows, shown to 2 decimals **(pg18+)**. The single most important comparison in the whole plan.
- **loops** — times the node ran. **actual time and actual rows are per-loop averages** → multiply
  by `loops` for totals (e.g. above: 0.003 ms × 10 = 0.03 ms total, 1 × 10 = 10 rows total).
- **Buffers: shared hit=… read=…** — `hit` = found in cache; `read` = fetched from OS/disk. High
  `read` on a hot query is a caching/index problem.

A worked, fully-annotated plan walkthrough is in [references/explain.md](references/explain.md).

## Reading a plan

Read **inside-out / bottom-up**: leaf scan nodes produce rows; join/sort/aggregate nodes above
combine them; the **top** line is the whole query's cost. Indentation = tree depth; `->` marks a child.

### Scan nodes (how a table/index is read)

| Node | Means | Good / bad sign |
|------|-------|-----------------|
| **Seq Scan** | Read every page of the table | Fine for small tables or when most rows match; **bad** when a selective `WHERE` should have used an index |
| **Index Scan** | Walk an index, then fetch each matching heap row | Good for **few** rows; random heap I/O makes it lose to Seq Scan for many rows |
| **Index Only Scan** | Answer entirely from the index (no heap visit) | Best case — needs a covering index **and** an all-visible page (`Heap Fetches: 0` is the win) |
| **Bitmap Heap Scan** + **Bitmap Index Scan** | Collect TIDs from index(es), sort by page, fetch in physical order | The middle ground for a medium row count; `BitmapAnd`/`BitmapOr` combine indexes |

### Join nodes

| Node | Best when | Watch for |
|------|-----------|-----------|
| **Nested Loop** | One side tiny, inner has an index | Disaster if both sides are big and **actual** loops ≫ estimated (a row underestimate) |
| **Hash Join** | Large, unsorted, equality join | Builds a hash of the smaller side; `Batches > 1` = spilled to disk → raise `work_mem` |
| **Merge Join** | Both inputs already sorted on the key | Pays for a Sort if inputs aren't pre-sorted |

### Other common nodes

`Sort` / **`Incremental Sort` (pg13+)** (uses a pre-sorted prefix; great with `LIMIT`) · `Hash`/
`HashAggregate`/`GroupAggregate` · `Partial Aggregate` + `Finalize Aggregate` (parallel two-stage) ·
`Gather` / **`Gather Merge` (pg10+)** (collect parallel-worker rows) · `Append`/`MergeAppend`
(partitions/UNION) · `Materialize` · `Memoize` (pg14+, caches inner results) · `Limit`.

### The three signals that explain almost every slow query

1. **Estimate vs actual rows.** `rows=10` estimated but `rows=10000` actual → the planner picked the
   plan for the wrong size (nested loop that should've been a hash join, no index used, etc.).
   **Fix:** `ANALYZE`, raise statistics target, or add **extended statistics** for