SQL Optimization Patterns

A dashboard query that ran in 40ms on your laptop is timing out at 12 seconds in production. The table grew from 50k rows to 80M, the optimizer quietly switched to a sequential scan, and your APM is now red. You paste the query into your AI assistant and get back a confident “add an index on customer_id” — except that index already exists and the planner is ignoring it.

The fix is to stop guessing. When you wire a database MCP server into Cursor, Claude Code, or Codex, the assistant reads your actual execution plan, your actual table statistics, and your actual index usage — then proposes changes you can verify against real numbers instead of folklore.

What You’ll Walk Away With

• A connected Postgres MCP server so the assistant can read live schema and EXPLAIN output instead of hallucinating
• A copy-paste prompt that turns an EXPLAIN (ANALYZE, BUFFERS) dump into a ranked list of bottlenecks
• A prompt that recommends indexes from real query patterns — and rejects redundant ones
• A subquery-to-window-function rewrite prompt that preserves results
• A clear sense of when the AI’s advice will mislead you (small tables, MATERIALIZED CTEs, EXPLAIN ANALYZE side effects)

Connect a Database MCP Server First

Without an MCP server, the assistant is reasoning about a schema it can’t see. With one, it can run EXPLAIN, inspect pg_stat_user_indexes, and check column statistics directly. Setup is identical across Cursor, Claude Code, and Codex — they all read the same MCP config; only the file location differs.

For local development and schema-aware work, the Prisma Postgres MCP ships in the Prisma CLI and needs no extra install:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "prisma", "mcp"]
    }
  }
}

Note

The old @modelcontextprotocol/server-postgres package is deprecated and no longer maintained — don’t reach for it. For production databases where you want Row Level Security respected, use the Supabase MCP (npx -y @supabase/mcp-server-supabase). For Amazon Aurora/RDS, AWS ships an official server: uvx awslabs.postgres-mcp-server@latest (Python-based, so the command is uvx, not npx). MySQL has no first-party MCP; the community mongodb-mcp-server-style options exist, but for MySQL most teams paste EXPLAIN output directly into the prompt instead.

Drop the config into .cursor/mcp.json (Cursor), register it with claude mcp add (Claude Code), or add it to ~/.codex/config.toml under [mcp_servers.postgres] (Codex). After that the workflow below is the same everywhere.

Read the Execution Plan Like a Senior DBA

The single highest-leverage move is feeding the assistant a real EXPLAIN (ANALYZE, BUFFERS) plan and asking it to rank problems by cost. Generic “optimize this query” prompts produce generic advice; a plan-grounded prompt produces specifics.

Copy-paste prompt for execution-plan analysis

Using the Postgres MCP server, run EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) on the query below against a staging copy, then rank the problems by actual time, not estimated cost.

For each bottleneck tell me: (1) the node (seq scan, nested loop, sort, hash) and how long it took, (2) why the planner chose it — check pg_stats for the relevant columns and tell me if the row estimate is off by more than 10x, (3) the single highest-impact fix, and (4) the exact DDL or query rewrite to apply.

Do NOT suggest an index that already exists — query pg_indexes for the table first. Flag any node where Rows Removed by Filter exceeds the rows returned.

SELECT c.customer_name,
       COUNT(o.order_id)                  AS order_count,
       SUM(oi.quantity * oi.unit_price)   AS total_spent
FROM customers c
JOIN orders o       ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id   = oi.order_id
WHERE o.order_date >= DATE '2024-01-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 100;

The grounding constraints — “check pg_stats”, “don’t suggest existing indexes”, “flag Rows Removed by Filter” — are what separate this from a search-engine answer. A 10x row-estimate miss almost always means stale statistics (ANALYZE the table) rather than a missing index, and the assistant will only catch that if you make it look.

Recommend Indexes From Real Usage

Once the plan points at a scan, the next question is which index — and whether you already have one doing the job. This is where reading pg_stat_user_indexes beats intuition: teams routinely carry half a dozen unused indexes that slow every write.

Cursor

In Agent mode, with the Postgres MCP connected, attach your schema and migrations folder as context and let the agent both diagnose and write the migration:

@migrations Using the Postgres MCP, query pg_stat_user_indexes and
pg_stat_user_tables for the orders and order_items tables. Then:

- List indexes with idx_scan = 0 over the last stats window (candidates to drop)
- Propose the minimal set of indexes for the WHERE/JOIN/ORDER BY in
  the slow dashboard query, preferring one composite index over several
  single-column ones where the leading column is selective
- Write the CREATE INDEX CONCURRENTLY statements as a new migration file
- Estimate the write-amplification cost of each new index

Claude Code

Point Claude Code at your migrations directory (a directory, not a file) and drive it from the terminal:

claude --add-dir db/migrations

Then in the session:

Using the Postgres MCP server, analyze index usage on orders and
order_items via pg_stat_user_indexes. Recommend indexes for the slow
dashboard query, reject any that duplicate an existing index, and write
them as a timestamped migration using CREATE INDEX CONCURRENTLY so we
don't lock the table in production.

Codex

Codex reads the same MCP config from ~/.codex/config.toml. Run it headless to produce the migration as a reviewable diff:

codex exec --ask-for-approval on-request \
  "Using the Postgres MCP server, query pg_stat_user_indexes for orders
   and order_items, propose the minimal index set for the slow dashboard
   query, and write CREATE INDEX CONCURRENTLY statements to a new file in
   db/migrations. Reject indexes that duplicate existing ones."

codex exec keeps the run non-interactive and leaves the change as a diff you review before applying — ideal for wiring into a worktree or CI check.

Copy-paste prompt for index recommendations

From the pg_stat_user_indexes and pg_stats data you just read, recommend the smallest set of indexes that covers this query’s WHERE, JOIN, and ORDER BY clauses. Prefer a single composite index when the leading column is selective (check n_distinct). For each recommendation, give me the CREATE INDEX CONCURRENTLY statement, the expected plan change, and one sentence on the write cost. Then list any existing index with zero scans that I should consider dropping. If a partial index (WHERE status = 'active') would be smaller and still cover the hot path, prefer it.

Rewrite Patterns the Optimizer Can’t Save You From

Some slow queries aren’t an indexing problem — they’re a shape problem. Correlated subqueries that re-run per row are the classic case, and the fix is a window function. Always ask the assistant to prove the rewrite returns identical rows.

Copy-paste prompt for subquery-to-window rewrite

Rewrite this correlated subquery as a window function and prove the results are identical. Show me the rewritten query, then write a verification query using EXCEPT in both directions that returns zero rows when the outputs match. Explain why the window version avoids the per-row re-execution.

SELECT customer_id, order_date, order_amount,
       (SELECT SUM(o2.order_amount)
          FROM orders o2
         WHERE o2.customer_id = o1.customer_id
           AND o2.order_date <= o1.order_date) AS running_total
FROM orders o1;

The assistant should return the obvious SUM(...) OVER (PARTITION BY customer_id ORDER BY order_date) form — but the EXCEPT-both-ways verification is the part that matters. Window-frame defaults differ from a naive subquery (RANGE vs ROWS, tie handling on equal order_date), and silently changing results is the most expensive kind of “optimization.”

For PostgreSQL 18 specifically, push for the right index type per access pattern rather than B-tree everywhere:

-- Partial index for the hot path (active rows only) — smaller, faster
CREATE INDEX CONCURRENTLY idx_active_customers
  ON customers (customer_id) WHERE status = 'active';

-- GIN for JSONB containment / full-text search
CREATE INDEX CONCURRENTLY idx_product_attributes
  ON products USING gin (attributes);

-- BRIN for naturally time-ordered, append-only data — tiny on disk
CREATE INDEX CONCURRENTLY idx_events_created_at
  ON events USING brin (created_at);

Strategic denormalization for read-heavy paths

When a join is hot and the underlying rows rarely change, a maintained summary table beats re-aggregating on every request. Ask the assistant to generate both the table and the trigger that keeps it correct — the trigger is the part people forget, and a stale summary is worse than a slow query.

Copy-paste prompt for denormalization

This schema is 95% reads. Propose a denormalized customer_order_summary table (lifetime orders, lifetime value, last order date) maintained by a trigger on orders. Write the table DDL, the AFTER INSERT OR UPDATE OR DELETE trigger function, and a one-time backfill query. Then tell me the failure mode if the trigger and a bulk COPY race, and how to reconcile drift with a nightly check.

Approximate Counts: Know Your Engine

A subtle accuracy trap: APPROX_COUNT_DISTINCT() is not a native PostgreSQL or MySQL function. It exists in BigQuery, Snowflake, and Spark, so AI suggestions that include it will throw function approx_count_distinct does not exist on stock Postgres.

• BigQuery / Snowflake: APPROX_COUNT_DISTINCT(customer_id) is native — use it.
• PostgreSQL: install the hll extension and use hll_cardinality(hll_add_agg(hll_hash_bigint(customer_id))), or accept an exact COUNT(DISTINCT ...) backed by a covering index.
• MySQL: there is no built-in approximate distinct; use exact COUNT(DISTINCT ...) or maintain a counter table.

This is exactly the kind of cross-engine error a connected MCP server prevents — the assistant can confirm the function exists before suggesting it.

When This Breaks

Failure modes that bite in production

• The optimizer ignores your shiny new index. Usually stale statistics — run ANALYZE <table> and re-check the plan. If the estimate is still off, the planner may be discarding the index because the predicate isn’t sargable (e.g. WHERE date_trunc('day', created_at) = ... can’t use a plain index on created_at).
• MATERIALIZED CTEs hurt on small inputs. Forcing materialization defeats predicate push-down. On a 500-row CTE the optimizer was better off inlining it — only materialize when the CTE is large and referenced multiple times.
• EXPLAIN ANALYZE actually runs the query. On an UPDATE/DELETE/INSERT it commits side effects. Wrap it in BEGIN; ... ROLLBACK; on a staging copy, never against production data.
• CREATE INDEX without CONCURRENTLY locks the table. On a busy production table that’s an outage. Always use CONCURRENTLY (it can’t run inside a transaction block, so it won’t fit in a single migration step — plan for that).
• The MCP server can write. If you grant write access, a misread prompt can DROP INDEX for real. Point MCP credentials at a read replica or a staging copy for analysis work.

What’s Next

Migration Patterns Generate and review safe, reversible migrations — including CONCURRENTLY index builds.

ORM Patterns Stop the ORM from generating N+1 queries and unindexed scans behind your back.

NoSQL Patterns Model and query document and key-value stores with AI when relational isn't the fit.

Debugging Patterns Turn red APM dashboards and useless logs into root-cause fixes with AI.