Query Execution Plan Analysis AI Prompt

Analyze this query's execution plan and identify optimization opportunities.

Database: {{database}}
Query: {{query}}
Table sizes: {{table_sizes}}
Current runtime: {{runtime}}

1. Reading the EXPLAIN output (PostgreSQL):
   Run: EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query>;

   Key nodes to identify:
   - Seq Scan: reading all rows — acceptable for small tables, problematic for large ones
   - Index Scan: using an index — usually good
   - Index Only Scan: no heap access needed (covering index) — best case
   - Hash Join / Merge Join: efficient for large joins
   - Nested Loop: efficient when inner side is small or indexed; slow for large outer sets
   - Sort: expensive on large datasets if no index supports the ORDER BY
   - Hash Aggregate: GROUP BY without index; may spill to disk if hash table exceeds work_mem

2. Cost interpretation:
   - Cost is in arbitrary units (not milliseconds)
   - cost=startup..total: startup is cost to return first row; total is cost for all rows
   - Rows: estimated row count (if vastly different from actual: statistics are stale → ANALYZE)
   - Buffers hit=N: N pages from buffer cache (fast); Buffers read=N: N pages from disk (slow)

3. Common anti-patterns and fixes:

   Seq scan on a large table:
   Fix: add an index on the filter column

   Bad row estimate (actual >> estimated):
   Fix: ANALYZE the table; consider statistics targets: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

   Nested Loop on large tables:
   Fix: ensure join columns are indexed; consider enable_nestloop=off temporarily to force hash join

   Sort without index:
   Fix: add index matching the ORDER BY columns (with the same sort direction)

   Function on indexed column (prevents index use):
   WHERE LOWER(email) = 'test@example.com'  -- cannot use index on email
   Fix: use a functional index: CREATE INDEX ON users (LOWER(email));

4. Work memory for sorts and hash joins:
   SET work_mem = '256MB';  -- only for the current session, for a specific expensive query
   Large sorts and hash aggregates may spill to disk if work_mem is too low.
   Check: 'Batches: 4' in hash join node means memory spilled to disk.

Return: annotated EXPLAIN output, identified bottlenecks, specific fixes with DDL/SQL, and expected improvement.