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.

Workspace: g14wxz
Visibility: Public
Created: 24 days ago
Last updated: 24 days ago
Publish Source: CLI
Badge