Coding & ArchitectureADVANCED

Complex SQL Query Performance Optimizer & Explainer

“Diagnose slow PostgreSQL/MySQL queries, generate optimized indexes, remove sequential scans, and explain execution plans step-by-step.”

Curated by David Chen
Updated: Aug 24, 2026
Run Time: 2 mins
Verified: ChatGPT, Claude, Gemini

Architecture & Behavioral Blueprint

This prompt implements an end-to-end framework specifically designed for Database Administration & Backend Engineering. It enforces role authority, step-by-step structural reasoning, and negative constraints to prevent generic, repetitive AI filler.

Target Use CaseDatabase Administration & Backend Engineering
Engine CompatibilityChatGPT • Claude • Gemini
Complexity LevelADVANCED

The Complete AI Prompt

1-Click Clipboard Ready
System / Prompt Matrix
Act as a Senior Database Administrator and PostgreSQL Performance Optimization Specialist. I have a database query that is running slowly or consuming excessive CPU/memory. Database Engine & Version: [e.g. PostgreSQL 16 / MySQL 8.0] Table Schema & Approximate Row Count: [PASTE TABLE DEFINITIONS & ROW COUNTS] The Problematic Query: [PASTE SLOW SQL QUERY HERE] Optional EXPLAIN (ANALYZE, BUFFERS) output: [PASTE EXPLAIN PLAN IF AVAILABLE] Execute the following performance review: 1. ROOT CAUSE BOTTLENECK ANALYSIS: - Identify why this query performs poorly (e.g., table scans, missing composite indexes, N+1 query patterns, improper JOIN order, correlated subqueries, memory spill to disk). 2. OPTIMIZED SQL QUERY REWRITE: - Provide the refactored, highly optimized SQL query. - Use CTEs (Common Table Expressions), window functions, or subqueries where appropriate for clarity and speed. 3. INDEXING STRATEGY (DDL): - Generate the exact CREATE INDEX commands (including composite indexes, partial indexes, or covering indexes with INCLUDE clauses). - Explain the order of indexed columns according to the query's WHERE, JOIN, and ORDER BY clauses. 4. STEP-BY-STEP EXPLANATION: - Explain what the refactored query does differently and why it drastically reduces I/O cost.
185 words
Dispatch:XLinkedInWhatsApp

Execution Protocol (Step-by-Step)

01

Paste your slow query

Include table column names and the database engine (e.g. Postgres 16).

02

Review index suggestions

Apply composite index recommendations to your database staging environment.

Verified Output Walkthrough

Sample Input Arguments

Query joining 2.4M orders with 500k customers with date range filter taking 1,850ms.

Verified Benchmark Response
### Optimized Index Recommendation ```sql CREATE INDEX CONCURRENTLY idx_orders_customer_date ON orders (customer_id, created_at DESC) INCLUDE (total_amount, status); ```
Growfyx Engineering Notes
  • Always test new indexes with EXPLAIN (ANALYZE, BUFFERS) in a staging environment before pushing to production.
Editor Recommendation★ 4.9/5 Rating

Claude 3.5 Sonnet / ChatGPT Plus — Recommended Model Runner

Run this prompt with zero rate limits and maximum intelligence using advanced frontier LLMs.

*Affiliate partner disclosureTry Claude 3.5 Sonnet / ChatGPT Plus

Frequently Asked Questions

Why use INCLUDE clauses in PostgreSQL indexes?

Covering indexes allow Index-Only Scans without reading the heap table, saving disk I/O.