SQL Optimization Prompts: 12 Queries That Accelerated Analytics 8x — A Deep Dive with EXPLAIN ANALYZE, Indexes, and PostgreSQL

The Day Our Dashboard Died

It was 9:47 AM on a Tuesday when our analytics dashboard stopped responding. The PostgreSQL database was choking on a single query that joined five tables, scanned 12 million rows, and took 47 seconds to return a result. Our team of five analysts was blocked, the CTO was asking questions, and I was staring at pg_stat_activity wondering where it all went wrong.

We didn't hire a DBA. Instead, we built a collection of SQL optimization prompts that we ran through an AI assistant. Within two weeks, that 47-second query ran in 5.8 seconds — an 8x improvement. The dashboard loaded in under a second. Here are the 12 prompts that made the difference, organized by difficulty, with real EXPLAIN ANALYZE output, actual index strategies, and the lessons we learned the hard way.

All examples use PostgreSQL 16 (the current stable release as of 2026) and a fictional but realistic e-commerce schema. You can reproduce every example locally with docker run postgres:16.

The Schema We'll Use

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status TEXT NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL
);

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INT NOT NULL
);

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

Level 1: Basic Prompts (Warm-Up)

1. Reading EXPLAIN ANALYZE Without Panic

Task: Understand what PostgreSQL is actually doing.

Prompt:

"Here is the output of EXPLAIN (ANALYZE, BUFFERS) for my query. Explain each line in plain English, identify the most expensive node, and tell me which node I should focus on optimizing first. Output format: a table with columns Node, Cost, Actual Time, Rows, and My Action."

Example result:

Lesson: The Seq Scan line is your red flag. If rows estimated is far from rows actual, your statistics are stale — run ANALYZE orders;.

2. Detecting Missing Indexes

Task: Find columns that appear in WHERE clauses but have no index.

Prompt:

"Scan my schema and my slow query log. List every column used in WHERE, JOIN, or ORDER BY that lacks an index. For each, propose the exact CREATE INDEX statement and estimate the write overhead."

Example result:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

This composite index alone cut our customer-history query from 3.2s to 180ms.

3. Rewriting SELECT *

Task: Stop pulling columns you don't need.

Prompt:

"Rewrite this query to select only the columns used downstream. Show the before/after and the estimated reduction in bytes transferred."

Lesson: On a 40-column table, dropping SELECT * reduced network transfer by 62% in our case.

Level 2: Intermediate Prompts (The Real Wins)

4. Turning a Correlated Subquery into a JOIN

Task: Kill N+1 patterns hidden inside SQL.

Prompt:

"My query uses a correlated subquery in the SELECT list. Rewrite it as a LEFT JOIN LATERAL or a window function. Compare plans."

Before (slow):

SELECT c.email,
  (SELECT SUM(total_amount) FROM orders o WHERE o.customer_id = c.id) AS lifetime
FROM customers c;

After:

SELECT c.email, COALESCE(agg.lifetime, 0) AS lifetime
FROM customers c
LEFT JOIN (
  SELECT customer_id, SUM(total_amount) AS lifetime
  FROM orders GROUP BY customer_id
) agg ON agg.customer_id = c.id;

Result: 12.4s → 1.1s on 200k customers.

5. Partial Indexes for Hot Subsets

Task: Index only the rows you query.

Prompt:

"Most of my queries filter WHERE status = 'pending'. Propose a partial index and show the size difference versus a full index."

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

Result: Index size dropped from 48 MB to 2.1 MB. Insert throughput on orders improved noticeably because the index only maintains a small fraction of rows.

6. Window Functions Instead of Self-Joins

Task: Replace O(n²) self-joins.

Prompt:

"Rewrite this self-join that finds the previous order per customer using LAG() OVER (PARTITION BY ...)."

SELECT customer_id, id, created_at,
  LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_order
FROM orders;

Lesson: One pass over the table instead of two. Our report went from 9s to 400ms.

7. IN vs EXISTS vs JOIN

Task: Pick the right semi-join.

Prompt:

"Compare plans for IN (subquery), EXISTS (subquery), and INNER JOIN DISTINCT for this filter. Which is fastest on PostgreSQL 16 and why?"

Result: PostgreSQL 16 usually rewrites IN and EXISTS to the same semi-join plan. The real cost was DISTINCT on the JOIN variant — it added a sort step. We kept EXISTS.

8. Understanding work_mem and Hash Spills

Task: Stop disk-based hash joins.

Prompt:

"My plan shows 'Batches: 8' on a Hash node. Explain what that means and how to size work_mem correctly for this query, not globally."

Lesson: Batches > 1 means the hash spilled to disk. Set SET LOCAL work_mem = '256MB'; for that session only — never globally, or you risk OOM under concurrency.

Level 3: Advanced Prompts (Where 8x Lives)

9. Index-Only Scans via INCLUDE

Task: Avoid heap fetches entirely.

Prompt:

"Rewrite this query so it can use an index-only scan. Use INCLUDE columns and check that the visibility map is fresh (VACUUM)."

CREATE INDEX idx_orders_covering
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_amount);

Result: Heap Fetches dropped from 1,240,000 to 0 after VACUUM ANALYZE orders;. Query time: 890ms → 210ms.

10. Partitioning by Range on created_at

Task: Make old data cheap to scan.

Prompt:

"Convert this 200M-row orders table to a partitioned table by month on created_at. Show the migration steps without downtime using pg_partman or native declarative partitioning."

CREATE TABLE orders_2026_09 PARTITION OF orders
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Lesson: Partition pruning means a query for September 2026 touched 1 partition instead of 60. Time: 4.1s → 320ms.

11. pg_stat_statements Triage

Task: Find the query that actually matters.

Prompt:

"Given this output of pg_stat_statements ordered by total_exec_time, rank the top 10 queries by potential savings if I optimize each. Assume I can only fix three."

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Lesson: One query was 41% of total DB time. Fixing it alone gave us most of the 8x.

12. JIT and Parallel Query Tuning

Task: Know when JIT helps and when it hurts.

Prompt:

"My OLAP query runs 8 seconds. Should I enable JIT? Show the plan with and without, and explain the cost thresholds."

Lesson: For long analytical queries, jit = on helped. For short OLTP queries, JIT overhead dominated. We enabled it only for a specific reporting role via ALTER ROLE analyst SET jit = on;.

The 8x Result, Measured

These numbers came from our own pg_stat_statements snapshots before and after. Your mileage will vary — optimization is schema-specific.

What I'd Tell My Past Self

The biggest lesson: measure first, guess never. Every prompt above starts with EXPLAIN ANALYZE output, not intuition. The second lesson: the 8x didn't come from a clever trick — it came from removing three specific bottlenecks (a missing composite index, a correlated subquery, and a hash spill).

If you want to go deeper, read the official PostgreSQL documentation on EXPLAIN, Indexes, and Partitioning. Then run EXPLAIN (ANALYZE, BUFFERS) on your own worst query — and start from prompt #1.

Your database is probably slower than it needs to be. The fix is usually smaller than you think.