HomeCasa Prompts

Diagnose a slow SQL query from its EXPLAIN ANALYZE output

Read a slow SQL query plan node by node, pinpoint the bottleneck, and get concrete index or rewrite fixes with their write-side cost and a way to verify them. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 5 customizable variables and you can copy it for free in one click.

Advanced · Code · Pending editorial review

Designed for:

Prompt

Act as a DBA and {{engine}} performance expert.

I have a slow query. Here is the data:
- Query:
{{query}}
- EXPLAIN output (ideally EXPLAIN ANALYZE with BUFFERS, or FORMAT=TREE):
{{plan}}
- Tables, existing indexes and approximate row counts: {{schema}}
- How often it runs and the acceptable time: {{context}}

Deliverables:
1. A plain-language reading of the plan, innermost node first: what each node does, estimated vs actual rows, and how much time it contributes.
2. The main bottleneck and any secondary ones, with the evidence in the plan that proves each (sequential scan, badly wrong estimates, sort spilling to disk, nested loop over many rows, late filter, function on an indexed column, etc.).
3. Fixes ranked by expected impact and effort:
   - Indexes with the exact `CREATE INDEX` (justified column order, partial or covering indexes where useful, and non-blocking creation where the engine supports it).
   - A query rewrite if it improves the plan (e.g., `EXISTS` vs `IN`, keyset pagination, avoiding `OR` across different columns).
   - Statistics or configuration changes (ANALYZE, extended statistics, work memory) only if the plan justifies them.
4. The cost of each index: disk space, impact on INSERT/UPDATE, and whether it duplicates an existing index.
5. How to verify: what to re-run and what change should appear in the plan.

Constraints:
- Base every conclusion on the plan; do not promise percentage or millisecond improvements.
- If I only have EXPLAIN without ANALYZE, or schema details are missing, say so and ask me before recommending indexes.
- Warn me if any action locks tables in production.

How to use it

  1. Run EXPLAIN ANALYZE (add BUFFERS on PostgreSQL) on an environment with realistic data, not an empty one.
  2. Paste the query, the full plan and the table and index definitions.
  3. Apply the highest-impact fix in staging first and compare the new plan.

Usage example

Engine: PostgreSQL 16 · Query: a customer's orders from the last 90 days sorted by date · orders table: 12 million rows · Runs 300 times per minute.

Sample output

1. Reading the plan

  1. Seq Scan on orders — reads ~11.9M rows to keep only one customer's. No index starts with customer_id.
  2. Sort (external merge, Disk: 48MB) — sorts by created_at on disk because it does not fit in work_mem.
  3. Limit — all that work, and only 20 rows are used.

2. Bottleneck

The sequential scan is the main problem; the on-disk sort is a consequence of it. The plan is truncated (Filter: ...), so confirm the filter's actual rows with EXPLAIN (ANALYZE, BUFFERS).

-- Equality column first, then the range/sort column:
-- the index returns rows already ordered and Limit stops at 20
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

ANALYZE orders;

CONCURRENTLY does not block writes, but it takes longer and cannot run inside a transaction (watch out for migration tools that open one by default).

4. Cost

AspectImpact
DiskProportional to 12M two-column entries
WritesOne more index to maintain on every INSERT
DuplicatesDoes not overlap the index on status

5. Verification

Re-run the EXPLAIN: you should see Index Scan using idx_orders_customer_created and the Sort node should be gone…

Key facts

Category
Programming & development · SQL
Output type
Code
Level
Advanced
Designed for
ChatGPT, Claude, Gemini and Microsoft Copilot
Verified in
Pending editorial verification
Variables
Database engine, SQL query, Execution plan, Tables, indexes and volume and Frequency and target
Language
English
Price
Free, no sign-up
Updated
Nov 7, 2026

Frequently asked questions

Why do I need EXPLAIN ANALYZE and not just EXPLAIN?

EXPLAIN only shows estimates. EXPLAIN ANALYZE runs the query and shows actual rows and timings, and the gap between the two is the most useful clue. It really executes, so wrap UPDATE or DELETE in a transaction you roll back.

Does adding more indexes always make the database faster?

No. Each index takes space and slows down writes. That is why the prompt asks for the cost of each one and checks for duplicates of existing indexes.

What is this prompt for?

Read a slow SQL query plan node by node, pinpoint the bottleneck, and get concrete index or rewrite fixes with their write-side cost and a way to verify them.

Which AI tools does it work with?

It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.

How do I customize the prompt?

Click “Customize” and fill in the variables (Database engine, SQL query, Execution plan, Tables, indexes and volume and Frequency and target). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.

Do I need experience to use it?

It is advanced: it is meant for people with experience in the topic who can evaluate and adjust the result.

Is this prompt free?

Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.

Can I trust the AI output?

Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.

Related prompts