Explain Query Plan
db_explainRun EXPLAIN ANALYZE on SELECT queries to inspect execution plans, timing, and buffers, revealing bottlenecks and missing index opportunities.
Instructions
Runs EXPLAIN ANALYZE on a SQL SELECT query to analyze its execution plan, performance characteristics, and bottlenecks. Returns the full execution plan with timing, buffer usage, row estimates, and actionable analysis. This tool helps you understand how PostgreSQL processes a query and identifies optimization opportunities like missing indexes, sequential scans, or high-cost operations.
When to use:
"Why is this query slow?"
"Help me optimize this query"
"What indexes would improve this query?"
Before creating indexes, to understand current plan
Parameter guidance:
query: the SQL SELECT query to analyze (required). Example: "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON o.user_id = u.id WHERE o.created_at > '2025-01-01' GROUP BY u.name ORDER BY COUNT(o.id) DESC LIMIT 10"
analyze: set to true for actual timing (default: true)
buffers: set to true to include buffer usage stats (default: true)
Behavioral notes:
Only SELECT queries are accepted — EXPLAIN on writes is not supported.
The query is NOT executed — only the plan is analyzed.
Results include: execution plan (JSON), total cost, actual time, rows, buffers, and analysis text.
Analysis highlights: sequential scans on large tables, low selectivity filters, missing index opportunities, high-cost joins.
No extensions required — uses built-in PostgreSQL EXPLAIN.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | The SQL SELECT query to analyze. Must be a SELECT statement. The query is wrapped in EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) automatically. | |
| analyze | No | Run EXPLAIN ANALYZE for actual timing (default: true). | |
| buffers | No | Include buffer usage statistics (default: true). | |
| database | No | Name of the database to query (from pgautopilot.json). Omit to use the current default database. |