g14wxz/query-explain-plan-debugging
Provides EXPLAIN ANALYZE workflow for identifying missing indexes, sequential scans, and query plan issues.
94
94%
Does it follow best practices?
Impact
Pending
No eval scenarios have been run
Passed
No known issues
index.mddocs/
Query Explain Plan Debugging
Provides EXPLAIN ANALYZE workflow for identifying missing indexes, sequential scans, and query plan issues.
Overview
This tile defines a systematic workflow for debugging slow PostgreSQL queries using EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). The agent executes explain plans via MCP execute_sql, parses the JSON output, and identifies four categories of problems: sequential scans on indexed columns, missing indexes on WHERE/JOIN columns, row-estimate vs actual-row deviations, and nested-loop joins on large tables.
Reference
Explain Command Format
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;Problem Detection Matrix
| Problem | Plan Signal | Fix |
|---|---|---|
| Seq Scan on indexed column | "Node Type": "Seq Scan" with index present | Investigate planner settings, run ANALYZE |
| Missing index | "Node Type": "Seq Scan" on WHERE/JOIN column | CREATE INDEX CONCURRENTLY |
| Stale statistics | Plan Rows vs Actual Rows ratio > 10x | ANALYZE table_name |
| Nested Loop on large set | "Nested Loop" with inner > 1000 rows | Add join-column index or restructure query |
| Low cache-hit ratio | Shared Read Blocks > Shared Hit Blocks | ANALYZE or tune shared_buffers |
Key JSON Plan Fields
| Field | Purpose |
|---|---|
Node Type | Identifies scan/join strategy |
Plan Rows | Planner's row estimate |
Actual Rows | Real rows returned at execution |
Shared Hit Blocks | Pages read from buffer cache |
Shared Read Blocks | Pages read from disk |
Dependencies
- supabase-mcp-verification — MUST pass before executing any SQL via MCP.
- db-diagnostics-inspection — Provides supplementary index and table-health diagnostics used in follow-up analysis.
Composition Position
This tile operates as a diagnostic utility invoked on-demand when a query performance issue is identified. It does not create permanent database objects. Other tiles that create indexes or RPCs invoke this tile to verify their output performs correctly.