Postgres EXPLAIN Slow Query Analyzer

---
name: postgres-explain-slow-query-analyzer
description: Reads Postgres EXPLAIN and EXPLAIN ANALYZE plans (text or JSON) and finds why a query is slow - sequential scans on large relations, nested loops with huge row estimates, misestimates (estimated vs actual), high buffer reads, Sort/Hash that spill to disk, missing LIMIT pushdown and expensive nodes by actual time. Ranks fixes (index, rewrite, stats). Offline stdlib Python analyzer.
---

# Postgres EXPLAIN Slow Query Analyzer

You help developers and DBAs read Postgres query plans. A slow query is rarely fixed by staring at the SQL alone: the plan shows whether Postgres chose a sequential scan, a nested loop that exploded, a bad row estimate, a Sort that spilled to disk, or a Hash Join that read gigabytes of buffers. You take an `EXPLAIN` or `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT|JSON)` plan, name the expensive nodes, explain why they hurt, and rank concrete fixes. This skill is about reading plans, not rewriting SQL from scratch without evidence.

You do not run destructive statements. Prefer `EXPLAIN (ANALYZE, BUFFERS)` on a replica or in a transaction you can roll back; warn that ANALYZE actually runs the query.

## Files in this skill

- `scripts/explain_analyze.py` - parses text or JSON EXPLAIN / EXPLAIN ANALYZE output; scores nodes by actual time or cost; reports EX01-EX12 (seq scans, nested-loop blowups, misestimates, disk sorts/hashes, high shared reads, never-executed nodes, parallel imbalance); ranks suggested fixes (Python 3.8+ standard library only)
- `references/plan-nodes-and-buffers.md` - how to read node types, costs, rows, loops, buffers and temp files
- `references/checks-and-codes.md` - every finding code, severity and what to do, plus input tips
- `templates/slow-query-review.md` - the review note you hand back with the plan excerpt and ranked fixes
- `examples/orders_by_email.explain.txt` - a text EXPLAIN ANALYZE of a slow lookup (seq scan + nested loop)
- `examples/review-report.md` - the real analyzer output on the example

## Workflow

### 1. Collect the plan
Ask for Postgres version, the SQL, and an `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` (or JSON). If ANALYZE is unsafe on production, take `EXPLAIN (BUFFERS)` without ANALYZE and say timings are estimates only. Capture table sizes (`pg_stat_user_tables`, `pg_relation_size`) when a Seq Scan looks suspicious.

### 2. Run the analyzer
```
python3 scripts/explain_analyze.py examples/orders_by_email.explain.txt
python3 scripts/explain_analyze.py plan.json --json-out
python3 scripts/explain_analyze.py plan.txt --min-ms 5
python3 scripts/explain_analyze.py --self-test
```
Exit 0 = no high findings, 1 = high findings, 2 = bad input. Without a shell, walk the plan tree bottom-up using `references/plan-nodes-and-buffers.md` and apply the codes in `references/checks-and-codes.md`.

### 3. Read the result
- High first: EX01 Seq Scan on a large relation with a selective filter, EX03 Nested Loop with huge actual rows x loops, EX05 misestimate ratio over 100x on a hot node, EX07 Sort/Hash that spilled to temp files.
- Medium: EX02 index-less join keys, EX04 Filter removing most rows after a scan, EX06 high shared read buffers, EX08 never-executed branches that still look costly on paper.
- Low/info: EX09 parallel workers uneven, EX10 planning time high vs execution, EX11 LIMIT not pushed down, EX12 CTE MATERIALIZED forcing full compute.

### 4. Rank fixes
Prefer evidence-backed changes: add or adjust an index that matches the Filter/Join Filter, update statistics (`ANALYZE`), rewrite a correlated subquery that became a Nested Loop, raise `work_mem` only when a Sort/Hash spilled and the set fits memory, or change a CTE to `NOT MATERIALIZED` when Postgres 12+ materialised it needlessly. Do not invent indexes without naming columns from the plan.

### 5. Deliver
Return the verdict, the top expensive nodes with actual time and buffers, the finding list, and a short ranked fix list using `templates/slow-query-review.md`.

## Example result
On the sample plan the Nested Loop drove 98% of time: an outer Seq Scan on `customers` filtered by `lower(email)` (no expression index) fed an inner Index Scan on `orders_customer_id_idx` that ran 48,210 loops. Estimated rows on the outer scan were 42 vs actual 48,210 (misestimate). Shared read buffers were high on `orders`. Ranked fixes: expression index on `lower(email)`, then confirm join selectivity; `ANALYZE customers`.

## Rules

- Base every claim on a node in the plan (type, cost/actual, rows, loops, buffers).
- Distinguish estimated-only EXPLAIN from ANALYZE; never treat costs as milliseconds.
- Do not recommend disabling seq scans globally or setting random_page_cost to fantasy values without measuring.
- Warn that EXPLAIN ANALYZE runs the query (triggers, writes, locks).
- This is a plan-reading aid, not a substitute for `pg_stat_statements` monitoring or a full schema review.
FILE:examples/orders_by_email.explain.txt
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1)
  Buffers: shared hit=1204 read=88440
  ->  Seq Scan on public.customers c  (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1)
        Filter: (lower((email)::text) = 'ada@example.com'::text)
        Rows Removed by Filter: 151790
        Buffers: shared hit=20 read=1830
  ->  Index Scan using orders_customer_id_idx on public.orders o  (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210)
        Index Cond: (customer_id = c.id)
        Filter: (created_at >= (now() - '90 days'::interval))
        Rows Removed by Filter: 3
        Buffers: shared hit=1184 read=86610
Planning Time: 0.412 ms
Execution Time: 912.610 ms
FILE:examples/review-report.md
# Postgres EXPLAIN analysis

Source: `examples/orders_by_email.explain.txt`
Execution Time: 912.61 ms
Planning Time: 0.412 ms

**Verdict: NEEDS WORK** - 3 high finding(s).

## Top nodes by time (or cost)

| Node | Relation | Actual ms (x loops) | Cost | Rows est -> act | Loops | Shared read |
|------|----------|---------------------|------|-----------------|-------|-------------|
| Nested Loop | - | 912.4 | 184220 | 12 -> 96 | 1 | 88440 |
| Index Scan | public.orders | 771.4 | 4 | 8 -> 0 | 48210 | 86610 |
| Seq Scan | public.customers | 88.2 | 1850 | 42 -> 48210 | 1 | 1830 |

## Findings

| Sev | Code | Detail |
|-----|------|--------|
| high | EX01 | Seq Scan on public.customers with selective Filter ((lower((email)::text) = 'ada@example.com'::text)); removed 151790 rows |
| high | EX03 | Nested Loop child Index Scan ran loops=48210 (actual rows/loop~0) consuming ~771.4 ms |
| high | EX05 | Misestimate on Seq Scan public.customers: est rows=42 actual=48210 (ratio~1148x) |
| medium | EX02 | Nested Loop has inner/outer Seq Scan on public.customers |
| medium | EX06 | Nested Loop ? shared read=88440 blocks |
| medium | EX06 | Index Scan public.orders shared read=86610 blocks |

## Ranked fixes (suggested)

1. Add a btree/expression index that matches the Seq Scan Filter (check function wrappers like lower()).
2. Reduce Nested Loop outer rows or index the inner join key; re-check join order after ANALYZE.
3. Run ANALYZE on the touched tables; consider extended statistics for correlated columns.
4. Lower shared reads via a better index or by filtering earlier so Nested Loop does not re-read heaps.

FILE:references/checks-and-codes.md
# Checks and codes

| Code | Severity | Meaning | What to do |
|------|----------|---------|------------|
| EX01 | high | Seq Scan on a relation with a selective Filter and large actual rows or high actual time | Expression or btree index matching the filter; avoid wrapping the column in a function without a matching expression index |
| EX02 | medium | Join condition looks unindexed (Nested Loop inner Seq Scan or high loops with Filter on join key) | Index the inner join key; confirm data types match (no cast that kills the index) |
| EX03 | high | Nested Loop with very high loops * actual rows (row explosion) | Reduce outer rows first; consider Hash/Merge Join; fix misestimates driving NL choice |
| EX04 | medium | Filter removes most rows after a scan (rows in >> rows out) | Push predicate into an index condition; rewrite so the planner can use an index |
| EX05 | high | Misestimate: actual/estimate or estimate/actual >= 100 on a node that matters | ANALYZE; extended stats for correlated columns; avoid non-immutable functions in predicates |
| EX06 | medium | High shared read buffers on a hot node | Caching/index to reduce heap fetches; check if Nested Loop re-reads the same pages |
| EX07 | high | Sort or Hash spilled to temp files (external merge / Batches > 1 with temp written) | Reduce rows before sort/hash; raise work_mem carefully for this query session; add supporting index to avoid sort |
| EX08 | low | Node never executed (actual loops=0) but dominates estimated cost | Ignore for runtime; useful when comparing plans without ANALYZE |
| EX09 | low | Parallel query with uneven worker contribution | Check parallel_* settings and whether gather is worth it |
| EX10 | info | Planning time large vs execution time | Prepared statements; simplify one-off planning; partition pruning issues |
| EX11 | medium | LIMIT above a full Sort or full aggregate of a large set | Rewrite so order/filter allows an index scan with early stop |
| EX12 | medium | CTE Scan with MATERIALIZED forcing full evaluation | WITH ... AS NOT MATERIALIZED (PG12+) or inline the CTE |

## Input tips
- Text plans: paste the full tree; do not strip indentation.
- JSON plans: Postgres `FORMAT JSON` array with one plan object; the script accepts a raw array or `{Plan: ...}`.
- Optional header lines like `QUERY PLAN` or dashed rules are ignored.
FILE:references/plan-nodes-and-buffers.md
# Plan nodes and buffers

## Capturing a useful plan
```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) <query>;
-- or
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;
```
On production prefer a hot standby or wrap writes in a transaction you roll back. `ANALYZE` executes the statement.

## Reading a text node line
Example: `Seq Scan on public.customers  (cost=0.00..1550.00 rows=42 width=64) (actual time=0.020..85.110 rows=48210 loops=1)`
- Node type and relation
- `cost=startup..total` (planner units, not ms)
- `rows` = estimated rows; under ANALYZE also `actual time` and `rows` and `loops`
- Total time for that node ~= (actual time total) * loops for the subtree contribution pattern; leaf time is clearer than parent when parents include children

## Common node types
- **Seq Scan**: read the whole heap. Fine for tiny tables or when most rows match; painful with a selective Filter on a large table.
- **Index Scan / Index Only Scan / Bitmap Heap Scan**: good when selective. Bitmap Heap with many rechecks can still hurt.
- **Nested Loop**: for each outer row, run inner. Great for few outer rows + indexed inner; catastrophic when outer is large.
- **Hash Join / Merge Join**: better for larger sets; watch Hash that spills (`Batches` > 1, temp written).
- **Sort**: `Sort Method: external merge` means spill to disk; consider `work_mem` or fewer rows earlier.
- **Aggregate / GroupAggregate / HashAggregate**: check whether grouping columns are indexed or pre-filtered.
- **CTE Scan**: Postgres 12+ may MATERIALIZE CTEs; can block filter pushdown.
- **Limit**: should sit above a cheap path; if Limit is above a full sort of everything, push criteria down.

## Buffers (BUFFERS option)
- `shared hit` = found in shared buffers (cheap)
- `shared read` = read from OS/disk into buffers (expensive under cache miss)
- `temp read/written` = work_mem spill files
A node with huge `shared read` on a repeated Nested Loop inner side often needs a better outer filter or index.

## Row misestimates
Comp

출처 · prompts.chat 공개 프롬프트 모음 · 작성 Fatih Kadir Akın — CC0(저작권 포기)로 공개된 원문을 그대로 싣습니다. 한국어 정리 · 실행 결과는 아직 없습니다.