Query Explain Rules

FATAL Constraints

• NEVER run EXPLAIN ANALYZE on DELETE, UPDATE, or INSERT statements in production without explicit user approval. EXPLAIN ANALYZE executes the statement.
• NEVER use EXPLAIN without the ANALYZE and FORMAT JSON options. Plain EXPLAIN without ANALYZE provides only estimates, which are insufficient for debugging.
• NEVER suggest CREATE INDEX without CONCURRENTLY on production tables. Non-concurrent index creation locks the table.
• NEVER ignore a sequential scan on a column that has an existing index. This MUST be investigated as a planner anomaly and reported.
• NEVER skip the BUFFERS option. Buffer statistics are mandatory for diagnosing cache-miss issues.

Mandatory Behaviors

• All explain plans MUST be captured as EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) via MCP execute_sql.
• Every sequential scan on an indexed column MUST be reported with the table name, column name, and index name.
• Row-estimate deviations exceeding 10x MUST trigger a recommendation to run ANALYZE on the affected table.
• Nested Loop joins where the inner relation exceeds 1000 actual rows MUST be flagged with an alternative join strategy recommendation.
• After applying any fix, the agent MUST re-run the explain plan and compare execution times before and after.
• All explain-plan analysis MUST be performed through the MCP execute_sql tool, not through direct psql sessions.