1
---
2
name: postgres-explain-slow-query-analyzer
3
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.
4
---
5
6
# Postgres EXPLAIN Slow Query Analyzer
7
8
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.
9
10
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.
11
12
## Files in this skill
13
14
- `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)
15
- `references/plan-nodes-and-buffers.md` - how to read node types, costs, rows, loops, buffers and temp files
16
- `references/checks-and-codes.md` - every finding code, severity and what to do, plus input tips
17
- `templates/slow-query-review.md` - the review note you hand back with the plan excerpt and ranked fixes
18
- `examples/orders_by_email.explain.txt` - a text EXPLAIN ANALYZE of a slow lookup (seq scan + nested loop)
19
- `examples/review-report.md` - the real analyzer output on the example
20
21
## Workflow
22
23
### 1. Collect the plan
24
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.
25
26
### 2. Run the analyzer
27
```
28
python3 scripts/explain_analyze.py examples/orders_by_email.explain.txt
29
python3 scripts/explain_analyze.py plan.json --json-out
30
python3 scripts/explain_analyze.py plan.txt --min-ms 5
31
python3 scripts/explain_analyze.py --self-test
32
```
33
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`.
34
35
### 3. Read the result
36
- 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.
37
- 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.
38
- Low/info: EX09 parallel workers uneven, EX10 planning time high vs execution, EX11 LIMIT not pushed down, EX12 CTE MATERIALIZED forcing full compute.
39
40
### 4. Rank fixes
41
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.
42
43
### 5. Deliver
44
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`.
45
46
## Example result
47
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`.
48
49
## Rules
50
51
- Base every claim on a node in the plan (type, cost/actual, rows, loops, buffers).
52
- Distinguish estimated-only EXPLAIN from ANALYZE; never treat costs as milliseconds.
53
- Do not recommend disabling seq scans globally or setting random_page_cost to fantasy values without measuring.
54
- Warn that EXPLAIN ANALYZE runs the query (triggers, writes, locks).
55
- This is a plan-reading aid, not a substitute for `pg_stat_statements` monitoring or a full schema review.
56