Phy Sql Explainer

Analyze PostgreSQL, MySQL, or SQLite EXPLAIN ANALYZE output to identify bottlenecks, stale stats, missing indexes, N+1 issues, and suggest fixes.

SQL Explainer

Diagnose slow SQL queries in seconds. Paste your EXPLAIN ANALYZE output and get a plain-English breakdown: which nodes are killing performance, why estimates are off, and exactly which indexes or rewrites will fix it.

Works with PostgreSQL, MySQL, SQLite. No API keys. No config.

---

Trigger Phrases

• "slow query", "why is my query slow", "explain this query"
• "explain analyze", "query plan", "execution plan"
• "optimize sql", "add index", "missing index"
• "query taking too long", "database slow"
• "/sql-explainer"

---

How to Provide Input

Give the agent any combination of:

text

# Option 1: Just paste EXPLAIN ANALYZE output
/sql-explainer
[paste EXPLAIN ANALYZE output here]

# Option 2: Query + EXPLAIN output
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'

EXPLAIN ANALYZE output:
[paste here]

# Option 3: Just the query (agent will run EXPLAIN ANALYZE if DB is accessible)
/sql-explainer SELECT * FROM orders WHERE user_id = 123

# Option 4: Include table schema for better index suggestions
Table: orders (user_id INT, status VARCHAR, created_at TIMESTAMPTZ, total DECIMAL)
Indexes: PRIMARY KEY (id), INDEX (created_at)
Query: SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'

---

Step 1: Run EXPLAIN ANALYZE

If the user provides a query but no EXPLAIN output, run it:

PostgreSQL

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

MySQL

sql

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- Or for runtime stats:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

SQLite

sql

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

---

Step 2: Parse the Plan Tree

Read the EXPLAIN output as a tree. For each node, extract:

Field	What to Look For
Node Type	Seq Scan = no index used; Index Scan = good; Bitmap Heap Scan = OK for bulk
Cost	(cost=startup..total) — total cost is the bottleneck metric
Rows	rows=N (estimated) vs actual rows=M — big gap = stale statistics
Loops	Nested Loop with loops=1000 = N+1 problem
Buffers	shared read=N = disk reads; shared hit=N = cache; high read = cold cache or missing index
Time	actual time=start..end — where is the wall clock going?

Critical Signals to Flag

text

🔴 Seq Scan on large table (> 10K rows estimated)
   → Table is being scanned fully — missing index

🔴 Rows estimated << actual rows (off by 10x+)
   → Stale statistics → run ANALYZE tablename
   → Correlated columns not captured by statistics

🔴 Nested Loop with high loops count (> 100)
   → N+1 query pattern — join condition may be missing index on inner side

🔴 Hash Join with high hash batches (> 1)
   → Spilling to disk — work_mem too low, or query needs refactoring

🟠 Sort with "Sort Method: external merge Disk"
   → Sort spilled to disk → increase work_mem or add covering index

🟠 Filter with high "rows removed by filter" ratio (> 80%)
   → Index exists but predicate not selective enough — consider partial index

🟡 Index Scan with many "heap fetches"
   → Index-Only Scan would be faster — add covering index

---

Step 3: Identify Root Cause

After reading the tree, classify the problem:

Category A: Missing Index

Symptoms:

• Seq Scan on a large table
• Filter condition in WHERE clause not covered by any index

Diagnosis:

sql

-- Check what indexes exist on the table
\d tablename  -- PostgreSQL
SHOW INDEXES FROM tablename;  -- MySQL

Typical patterns:

Query Pattern	Recommended Index
WHERE col = value	CREATE INDEX ON t(col)
WHERE col1 = v AND col2 = v	Composite: CREATE INDEX ON t(col1, col2)
WHERE col = v ORDER BY created_at	CREATE INDEX ON t(col, created_at)
WHERE status IN ('a','b') AND user_id = v	CREATE INDEX ON t(user_id, status)
WHERE email LIKE 'prefix%'	B-tree works; WHERE email LIKE '%suffix' needs pg_trgm GIN
WHERE body @@ to_tsquery('word')	GIN: CREATE INDEX ON t USING gin(to_tsvector('english', body))
WHERE deleted_at IS NULL (most rows)	Partial: CREATE INDEX ON t(user_id) WHERE deleted_at IS NULL

---

Category B: Stale Statistics

Symptoms:

• Planner estimates rows=5 but actual is rows=50000
• Plan looks like it should be fast but isn't

Fix:

sql

ANALYZE tablename;  -- PostgreSQL (updates statistics)
ANALYZE TABLE tablename;  -- MySQL

For correlated columns:

sql

-- PostgreSQL 14+ extended statistics
CREATE STATISTICS s1 ON col1, col2 FROM tablename;
ANALYZE tablename;

---

Category C: N+1 / Nested Loop

Symptoms:

• Nested Loop with loops=1000+
• Hash Join or inner query repeated many times
• Code fetches one row, then queries for related data in a loop

Fix:

• Add index on the foreign key / join column in the inner table
• Rewrite to use a single JOIN instead of a loop:

sql

-- Before (N+1): fetch users, then query orders for each
SELECT * FROM users WHERE id = $1;
-- × N times:
SELECT * FROM orders WHERE user_id = $1;

-- After: single JOIN
SELECT u.*, o.*
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = $1;

---

Category D: Query Rewrite Needed

Symptoms:

• IN (SELECT ...) subquery producing full scan
• OR condition preventing index use
• SELECT * fetching unnecessary columns (prevents Index-Only Scan)

Common rewrites:

sql

-- ❌ Slow: correlated subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'US');

-- ✅ Fast: JOIN
SELECT o.* FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'US';

-- ❌ Slow: OR prevents index use
SELECT * FROM events WHERE type = 'click' OR type = 'view';

-- ✅ Fast: UNION ALL
SELECT * FROM events WHERE type = 'click'
UNION ALL
SELECT * FROM events WHERE type = 'view';

-- ❌ Slow: function on indexed column
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-18';

-- ✅ Fast: range scan uses index
SELECT * FROM orders
WHERE created_at >= '2026-03-18' AND created_at < '2026-03-19';

-- ❌ Slow: LIKE with leading wildcard
SELECT * FROM products WHERE name LIKE '%widget%';

-- ✅ Fast: full-text search
SELECT * FROM products WHERE name_vector @@ to_tsquery('widget');
-- Requires: CREATE INDEX ON products USING gin(to_tsvector('english', name));

---

Category E: Configuration Limits

Symptoms:

• Sort Method: external merge Disk
• Hash Batches: 4 (spilling to disk)
• Large aggregations are slow

Fixes (PostgreSQL):

sql

-- Increase per-query memory (default 4MB is often too low)
SET work_mem = '64MB';  -- session level
-- In postgresql.conf for permanent change:
-- work_mem = 64MB

-- Check current value
SHOW work_mem;

---

Step 4: Output Report

Always produce this report structure:

markdown

## SQL Explainer Report
Query: [first 60 chars of query...]
Database: PostgreSQL / MySQL / SQLite
Execution Time: [from EXPLAIN ANALYZE, if available]

### Diagnosis

| Severity | Node | Problem | Impact |
|----------|------|---------|--------|
| 🔴 Critical | Seq Scan on orders (50K rows) | No index on (user_id, status) | 2.1s → <5ms |
| 🟠 High | Stale statistics | Planner estimated 3 rows, got 48K | Wrong plan chosen |
| 🟡 Medium | SELECT * | 42 columns fetched, 3 used | Prevents Index-Only Scan |

---

### Root Cause

[1-2 sentence plain-English explanation of why the query is slow]

---

### Fix Plan (Prioritized)

**Fix 1 — Add composite index (estimated: 2.1s → <5ms)**
```sql
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders(user_id, status)
WHERE deleted_at IS NULL;  -- partial index if most rows are soft-deleted

Why: Eliminates Seq Scan. CONCURRENTLY means no table lock in production.

Fix 2 — Update statistics

sql

ANALYZE orders;

Why: Planner estimated 3 rows, got 48K. Wrong estimate → wrong plan.

Fix 3 — Select only needed columns

sql

-- Before:
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- After (enables Index-Only Scan):
SELECT id, total, created_at FROM orders WHERE user_id = 123 AND status = 'pending';

---

Expected After Fix

Metric	Before	After
Execution time	2.1s	<5ms
Node type	Seq Scan	Index Only Scan
Rows scanned	50,000	3
Disk reads	412	1

---

Quick Copy-Paste Fix

sql

-- Run these in order:
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status) WHERE deleted_at IS NULL;
ANALYZE orders;

text

---

## Quick Mode

If user just wants fast feedback:

Quick Check: [query first 40 chars...]

🔴 Missing index on orders(user_id, status) — Seq Scan detected 🟠 Stale stats — run ANALYZE orders 🟡 SELECT * fetching 42 cols

Fix: CREATE INDEX ON orders(user_id, status); Full analysis: /sql-explainer --full [paste EXPLAIN ANALYZE]

text

---

## Index Type Reference

| Use Case | Index Type | Syntax |
|----------|------------|--------|
| Equality, range, ORDER BY | B-tree (default) | `CREATE INDEX ON t(col)` |
| JSON, arrays, full-text | GIN | `CREATE INDEX ON t USING gin(col)` |
| Geometric, PostGIS | GiST | `CREATE INDEX ON t USING gist(col)` |
| Exclude overlapping ranges | BRIN | `CREATE INDEX ON t USING brin(col)` (huge tables only) |
| Many repeated values (few distinct) | — | Don't index; use partial index instead |
| Only index subset of rows | Partial | `CREATE INDEX ON t(col) WHERE condition` |
| Avoid heap fetch entirely | Covering | `CREATE INDEX ON t(a) INCLUDE (b, c)` |

---

## What EXPLAIN ANALYZE Nodes Mean

| Node | Meaning | Red Flag? |
|------|---------|-----------|
| `Seq Scan` | Full table scan | ✅ On tables > 10K rows |
| `Index Scan` | Used an index, then fetched heap rows | Usually fine |
| `Index Only Scan` | Used index, no heap fetch needed | Best case |
| `Bitmap Index Scan` + `Bitmap Heap Scan` | Multiple index ranges merged | OK for bulk |
| `Nested Loop` | For each outer row, scan inner | ✅ if `loops` >> 10 |
| `Hash Join` | Build hash table of smaller side | ✅ if `Hash Batches > 1` |
| `Merge Join` | Both sides pre-sorted | Fine if Sort cost is low |
| `Sort` | Explicit sort step | ✅ if `external merge Disk` |
| `Aggregate` | GROUP BY / COUNT etc. | Fine usually |
| `Limit` | Stops early | Watch `startup cost` |

---

## Why This Doesn't Exist Elsewhere

Most database GUIs (pgAdmin, TablePlus, DBeaver) show you the EXPLAIN output as a tree or graph — they visualize it. But they don't tell you **what to do about it**.

This skill reads the plan like a senior DBA would: identifies the specific bottleneck, explains why it's happening (stale stats, missing index, wrong join type), and gives you the exact SQL to fix it — optimized for your specific query pattern, not a generic "add an index" suggestion.

---

## Author

**[Canlah AI](https://canlah.ai)** — Run performance marketing without breaking your brand.

- GitHub: [github.com/PHY041](https://github.com/PHY041)
- All Skills: [clawhub.ai/PHY041](https://clawhub.ai/PHY041)