Jonas Weber·
Fed a 4-second query's EXPLAIN ANALYZE into this prompt and got a walkthrough of exactly where the time went. Two index changes later: 80ms.
Walks through a Postgres query plan node by node, identifies where time and rows explode, and proposes ranked fixes.
EXPLAIN ANALYZE Interpreter
You are a Postgres performance specialist. I will paste a query and its full EXPLAIN (ANALYZE, BUFFERS) output. Interpret the plan for me — I can read SQL fluently but query plans only roughly.
Query context:
- Postgres version: {{pg_version}}
- Table sizes involved: {{table_sizes}}
- How often this query runs: {{frequency}}
- Acceptable latency: {{latency_target}}
Your analysis:
1. **Time accounting.** Walk the plan tree and attribute the actual time: which 1-2 nodes consume most of the total, in milliseconds and as a percentage? State it plainly ("92% of the time is the Seq Scan on events").
2. **Row estimate audit.** Compare estimated vs actual rows at each significant node. Flag any node where the estimate is off by more than 10x, and explain the consequence (wrong join strategy, wrong scan type). If misestimates dominate, recommend the ANALYZE/statistics fix before any index.
3. **Buffer story.** Interpret the BUFFERS numbers: is this query IO-bound (high read) or CPU-bound (high hit, big row counts)? What does that imply for the fix?
4. **Ranked fixes.** Propose fixes in order of expected impact. For each: the exact DDL or query rewrite, the mechanism (why it helps), the expected new plan shape, and any cost (write amplification, index size, lock during creation — always CONCURRENTLY where relevant). Include non-index options when applicable: query rewrite, LIMIT pushdown, partial index, covering index, or admitting the query needs a materialized view.
5. **Verification.** The exact command to re-check, and what success looks like against my latency target.
Do not guess beyond the plan: if something needs `pg_stat_statements` or table stats to confirm, list it under "data I would want next".
Query + plan:
{{query_and_plan}}
Ergebnisse
## Plan Analysis: dashboard activity query (4.2s actual)
### 1. Time Accounting
- **Seq Scan on `events` — 3,890ms of 4,210ms total (92%).** Scans 41M rows to return 18k. This is the whole story.
- Sort node: 240ms (6%) — a symptom; it sorts what the scan feeds it.
- Everything else: noise.
### 2. Row Estimate Audit
| Node | Est. rows | Actual | Ratio |
|---|---|---|---|
| Seq Scan filter on `events` | 210,000 | 18,400 | 11x over 🔴 |
| Hash Join to `users` | 195,000 | 18,400 | 10x over 🔴 |
The planner thinks the `(org_id, created_at)` filter matches 210k rows. The 11x overestimate is why it prefers a Seq Scan — it believes an index scan would touch too much of the table. Correlated columns: `org_id` and `created_at` are not independent (new orgs produce most new events). **Fix estimates first:**
```sql
CREATE STATISTICS events_org_time (dependencies) ON org_id, created_at FROM events;
ANALYZE events;
```
### 3. Buffer Story
`shared read=1,204,332, hit=88,102` → ~9.2GB read from disk per execution. Heavily **IO-bound**. At 30 executions/minute on the dashboard this alone explains your NVMe read graph. An index that avoids the full scan pays for itself immediately.
### 4. Ranked Fixes
1. **Composite index (highest impact):**
```sql
CREATE INDEX CONCURRENTLY idx_events_org_created
ON events (org_id, created_at DESC);
```
Mechanism: turns the scan into an Index Scan matching your `WHERE org_id = $1 AND created_at > $2 ORDER BY created_at DESC` shape — the DESC ordering also deletes the Sort node. Cost: ~1.1GB index, slight write amplification on a hot insert table.
2. **Extended statistics** (above) — cheap, fixes the planner's worldview, do it regardless.
3. **Covering variant** — add `INCLUDE (event_type, payload_id)` if the Index Scan still heap-fetches heavily (check `Heap Fetches` after fix 1). Skip if fix 1 gets you under target.
### 5. Verification
```sql
EXPLAIN (ANALYZE, BUFFERS) <query>;
```
Success: Index Scan node, no Sort node, total < 100ms, shared read < 5,000. Against your 200ms target that is comfortable.
**Data I would want next:** `pg_stat_statements` top-10 by `total_exec_time` — if this query is #1, consider whether the dashboard can cache it for 30s regardless.
Modell: DeepSeek R1
39 Likes23 SavesScore: 29
2 Kommentare
Luca Brunner·
Estimates-before-indexes as the default order is correct and almost nobody does it. Extended statistics are criminally underused.
Tobias Keller·
The buffer story section is what turns a plan dump into something you can reason about. Saved.