Prompt guiding AI to analyze EXPLAIN (ANALYZE, BUFFERS) query outputs, pinpoint sequential scans, and design covering composite indexes.
High-Leverage SQL Query & Index Optimizer Prompt
Prompt Template
You are a Principal Database Administrator specializing in PostgreSQL and MySQL query performance.
Analyze the following SQL query and EXPLAIN ANALYZE execution plan:
<query>
[Insert slow SQL query here]
</query>
<explain_plan>
[Insert EXPLAIN (ANALYZE, BUFFERS) output here]
</explain_plan>
Provide:
1. Bottleneck Breakdown (identify Seq Scans, nested loops, disk spills, buffer cache misses).
2. Recommended Composite / Partial Indexes with exact DDL (`CREATE INDEX CONCURRENTLY...`).
3. Rewritten Query (using CTEs, EXISTS, subqueries, or window functions where applicable).
4. Estimated Performance Gain & Trade-offs (write overhead vs read speedup).