SQL Optimization Patterns

SQL optimization with an AI agent works when every suggestion is grounded in the database’s own evidence. A read-only MCP server lets Claude Code, Codex, or Cursor read the real execution plan, table statistics, and index usage. Each change then ships with a before-and-after plan, a parity query that returns zero rows, and a stated write cost.

This recipe is for the developer who owns a slow query and the tech lead who approves the fix. A dashboard query that ran in 40 ms on your laptop now times out in production because the table grew and the planner switched to a sequential scan. You paste the SQL into your agent and get back a confident “add an index on customer_id”. That index already exists, and the planner is ignoring it.

What this SQL tuning recipe gives you

A read-only database MCP setup for Claude Code, Codex, and Cursor, so the agent reads EXPLAIN output instead of guessing.
A prompt that turns an EXPLAIN (ANALYZE, BUFFERS) plan into ranked bottlenecks and at most two justified indexes.
A rewrite prompt that proves the new query returns the same rows, with an EXCEPT ALL parity check in both directions.
An N+1 prompt that collapses a per-row ORM loop into two statements, plus a query-count test that keeps it fixed.
An evidence checklist a reviewer can approve without reading every line of SQL.

Why does the agent’s first index suggestion miss?

An agent that sees only the SQL text pattern-matches: it indexes the WHERE columns, adds a few more “to be safe”, and cannot tell a missing index from stale statistics. The plan holds the answer. It shows which node took the time, how far the row estimate was off, and whether a sort spilled to disk.

So the order of work is fixed:

Measure. Capture the plan on staging with production-sized data. A 50,000-row table and an 80-million-row table get different plans.
Diagnose. Rank nodes by actual time. Check whether the row estimate is off by 10x or more, which usually means stale statistics, not a missing index.
Simulate. Test the candidate index as a hypothetical index (the hypopg extension) before you build anything.
Change. Write one index or one rewrite as a migration file. Do not apply it from the agent session.
Prove. Attach the before-and-after plan, the parity result for rewrites, and the write cost to the pull request.

Connect a read-only database MCP server

The MCP server gives the agent the plan, pg_stats, and pg_stat_user_indexes directly. Use a staging database or a database branch, a read-only role, and a statement timeout. The role, the extensions, and every server option are covered in database MCP servers. This page uses Postgres MCP Pro in restricted mode, which the vendor README defines as read-only transactions with an execution-time limit.

# Terminal, repo root: writes .mcp.json; ${STAGING_DATABASE_URL} is expanded from your shell at launch
claude mcp add -s project postgres -e 'DATABASE_URI=${STAGING_DATABASE_URL}' \
  -- uvx postgres-mcp --access-mode=restricted

# ~/.codex/config.toml: env_vars forwards DATABASE_URI from your shell.
# Export DATABASE_URI first, pointing at the staging read-only URL:
#   export DATABASE_URI="$STAGING_DATABASE_URL"
[mcp_servers.postgres]
command = "uvx"
args = ["postgres-mcp", "--access-mode=restricted"]
env_vars = ["DATABASE_URI"]

Put the entry in the project’s .cursor/mcp.json. Cursor’s MCP docs describe ${env:NAME} interpolation, so the file can hold a reference to an environment variable instead of the secret. Confirm the server connects in Cursor’s MCP settings before you commit the file:

{ "mcpServers": { "postgres": {
  "command": "uvx", "args": ["postgres-mcp", "--access-mode=restricted"],
  "env": { "DATABASE_URI": "${env:STAGING_DATABASE_URL}" }
} } }

Postgres MCP Pro is the PyPI package postgres-mcp (0.3.0, the last release on 2025-05-16), so it runs with uvx, not npx. Its index tools need the pg_stat_statements and hypopg extensions on the database. Pick another server when your stack differs:

Your database	Server	Why
Self-hosted or managed Postgres	Postgres MCP Pro (`crystaldba/postgres-mcp`)	`explain_query` with hypothetical indexes, `get_top_queries`, index recommendations
MySQL, MariaDB, SQL Server, SQLite, or several engines	DBHub (`@bytebase/dbhub`)	One server with read-only mode, row limits, and query timeouts
Many engines behind one config	MCP Toolbox for Databases (Google)	Prebuilt tools per engine, or custom tools in `tools.yaml`
Supabase or Neon	The vendor’s remote server	Supabase takes `read_only=true` in its URL; Neon’s npm package is deprecated in favour of its remote server

If you want Postgres guidance without a live connection, Supabase publishes a skill that covers performance and row-level security: npx skills add supabase/agent-skills --skill supabase-postgres-best-practices. A skill supplies rules; only the MCP server supplies your plan and statistics, so use both for tuning work.

How do you get index advice from a real execution plan?

Start by handing the agent a real plan and forbidding guesses. The grounding lines in the prompt below (check pg_stats, list existing indexes first, cap the count at two) are what separate it from a search-engine answer.

Using the postgres MCP server against staging, run
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the query below and rank the plan
nodes by actual time, not estimated cost.

For each of the top three nodes, report:
1. The node type (seq scan, nested loop, sort, hash) and its actual time.
2. Estimated vs actual rows. If they differ by 10x or more, check pg_stats
   for the columns involved and say whether ANALYZE is the fix instead of
   an index.
3. Rows Removed by Filter, and whether a sort spilled to disk.

Then list the existing indexes on every table in the query (pg_indexes)
and never propose one that duplicates them. Propose at most two indexes,
each as CREATE INDEX CONCURRENTLY, and test each with a hypothetical index
in explain_query before recommending it. Show the simulated plan change.
Do not create anything.

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 '2026-01-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 100;

EXPLAIN ANALYZE executes the statement. On a SELECT against staging that is what you want. On an UPDATE, DELETE, or INSERT, wrap it in BEGIN; … ROLLBACK;, and never run it against production.

Choose the index type from the access pattern

Once the plan points at a scan, ask for the smallest index that serves it. The agent defaults to B-tree everywhere unless you name the alternatives:

-- Partial index: the hot path touches only active rows, so index only those
CREATE INDEX CONCURRENTLY idx_customers_active
  ON customers (customer_id) WHERE status = 'active';

-- GIN: JSONB containment (@>) and full-text search
CREATE INDEX CONCURRENTLY idx_products_attributes
  ON products USING gin (attributes);

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

Unused indexes cost you on every write. The same MCP session can find them: indexes with idx_scan = 0 in pg_stat_user_indexes since the last statistics reset are drop candidates. Confirm the reset date and check replicas before dropping anything, because the counters are per server.

Drive the tuning session in Claude Code, Codex, and Cursor

The MCP config is the same idea in all three tools; the difference is how you keep the agent in read-only analysis until you have the evidence, then let it write one migration file.

Start in plan mode so the agent analyses and proposes without editing files:

claude --permission-mode plan

Paste the plan prompt above. When the proposal carries a simulated plan change you accept, press Shift+Tab to leave plan mode (or accept the plan when Claude Code offers to proceed) and ask it to write the migration file. Claude Code shows each MCP tool call, so you can see which queries it ran.

Run the analysis under the built-in :read-only permission profile (beta, Codex CLI 0.138.0 or later), then a second run under :workspace to produce the migration as a reviewable diff:

# Analysis: no file writes
codex -c default_permissions=":read-only" -a on-request "Using the postgres MCP server, rank the plan nodes for the query in docs/slow-dashboard.sql and simulate at most two indexes. Do not create anything."

# Change: one migration file, reviewed as a diff
codex -c default_permissions=":workspace" -a on-request "Write the accepted index as CREATE INDEX CONCURRENTLY in a new file in db/migrations, with a matching DROP INDEX CONCURRENTLY down migration."

For a scripted run, codex exec -c default_permissions=":read-only" "…" prints the analysis without an interactive session. The legacy --sandbox read-only and --sandbox workspace-write flags still work in codex-cli 0.157.1, but they do not compose with permission profiles, so use one mechanism or the other, never both.

Start in Plan Mode, which plans before it writes any code, so the analysis stays read-only. With the postgres server enabled, attach the migrations folder and the slow query file as context with @ and paste the plan prompt. When you accept a simulated plan change, switch the mode from Plan back to Agent and ask it to write the migration file, then review the diff before you accept it.

Rewrite a correlated subquery without changing its results

Some slow queries are a shape problem, not an index problem. A correlated subquery that re-runs for every row is the classic case, and a window function is the fix. The dangerous part is not the rewrite; it is a rewrite that returns slightly different rows. Make the agent prove parity.

Rewrite this correlated subquery as a window function. Then write a parity
check: (rewrite EXCEPT ALL original) UNION ALL (original EXCEPT ALL rewrite)
wrapped in SELECT count(*). It must return 0 on staging. Run it through the
postgres MCP server and show me the count and both EXPLAIN ANALYZE timings.

Keep the default RANGE frame so rows that share an order_date get the same
running total, exactly as the <= in the subquery does. Explain in two
sentences why a ROWS frame would change the result.

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;

Use EXCEPT ALL, not EXCEPT: plain EXCEPT removes duplicates, so a rewrite that returns a row twice still passes. Commit the parity query next to the rewrite as a test fixture, so the next refactor has to pass it too.

Collapse an N+1 into two statements

An ORM loop that touches a relation for each parent row fires one query per row, so an orders page with 500 orders makes 501 round-trips. The fix is to load all children in one query keyed by parent ID and group them in memory. The anchor stack here is Drizzle on Postgres; the ORM patterns page covers Prisma and TypeORM.

This Drizzle code triggers an N+1, one query per order to load its items:

const orders = await db.select().from(ordersTable).where(eq(ordersTable.userId, userId));
for (const order of orders) {
  order.items = await db.select().from(itemsTable).where(eq(itemsTable.orderId, order.id));
}

Rewrite it to load all items in one query with inArray(itemsTable.orderId, ids),
then group them by orderId in memory so every order keeps its items array.
Preserve the exact return shape callers expect, and return early without a
second query when the user has no orders.

Then add an integration test that counts statements through a custom Drizzle
logger (logQuery) and asserts exactly 2 statements for a user with 50 orders.
Do not use a join that repeats order columns once per item.

The query-count test is the verification: it goes red the day someone reintroduces the loop, without anyone reading the diff. It also catches the other bad rewrite agents produce, a single join that sends every order row once per item over the wire.

Denormalize a hot read path without drifting

When a join is hot and its rows rarely change, a maintained summary table beats re-aggregating on every request. The trigger that keeps it correct is the part agents forget, and a stale summary is worse than a slow query. Tell the agent your read/write ratio, or it will tune for reads alone.

This schema is about 95% reads. Propose a customer_order_summary table
(lifetime order count, lifetime value, last order date) maintained by an
AFTER INSERT OR UPDATE OR DELETE trigger on orders. Write the table DDL,
the trigger function, and a batched one-time backfill.

Then write a reconciliation query that recomputes the summary from orders
and returns every customer_id where the stored and recomputed values
differ. It must return 0 rows after the backfill. Tell me how a bulk COPY
into orders interacts with the trigger, and what the nightly drift check
should alert on.

Which SQL functions differ between engines?

Agents mix dialects. A connected MCP server lets the agent check that a function exists before it suggests it, but only if you ask. The traps that come up most:

You need	PostgreSQL	MySQL	BigQuery, Snowflake
Approximate distinct count	No built-in: the `hll` extension (`hll_cardinality(hll_add_agg(hll_hash_bigint(id)))`) or an exact `COUNT(DISTINCT …)`	No built-in: exact `COUNT(DISTINCT …)` or a counter table	`APPROX_COUNT_DISTINCT(id)` is native
A plan with real timings	`EXPLAIN (ANALYZE, BUFFERS)`	`EXPLAIN ANALYZE` (MySQL 8.0.18 and later); it also executes the query	Each engine’s query profile
An index build that does not block writes	`CREATE INDEX CONCURRENTLY`	InnoDB online DDL: `ALGORITHM=INPLACE, LOCK=NONE`	Not applicable
Test a drop before dropping	Hypothetical indexes via `hypopg`	Make the index `INVISIBLE`, watch, then drop	Not applicable

When the agent names an extension or function you have not seen, such as a plausible pg_fastsearch, make it query pg_available_extensions or the catalogue through the MCP server before the suggestion goes any further.

What evidence should an SQL change carry to review?

A reviewer approves the evidence, not the prose around it. Ask the agent to assemble it in the pull request description; the DDL itself is a few lines and still gets read, because schema changes are one of the classes where someone still reads the code.

Change	Evidence that proves it	Who signs off
New index	Before-and-after `EXPLAIN (ANALYZE, BUFFERS)` from staging, the tables whose writes it slows, and its size	The query owner; the tech lead for tables with heavy writes
Query rewrite	Parity count of 0 with `EXCEPT ALL` both ways, committed as a test, and both timings	The query owner
N+1 fix	Query-count test in CI and the two SQL statements it issues	The query owner
Index drop	Zero scans since a known reset on every replica, or an invisible or hypothetical test period	The tech lead
Summary table	Reconciliation query returns 0 rows after the backfill, committed as a test, plus a nightly drift alert	The tech lead

After deploy, compare mean execution time for the same statement in pg_stat_statements. The rollback for an index is DROP INDEX CONCURRENTLY, which belongs in the down migration. Lock safety for the migration itself (a CONCURRENTLY build cannot run inside a transaction block, and a linter such as Squawk catches the plain form) is covered in database migration patterns, along with the expand, backfill, and contract sequence for column renames. The general PR format is in the evidence bundle.

When SQL optimization advice goes wrong in production

The planner ignores the new index. Usually stale statistics: run ANALYZE orders and re-check the plan. If it still ignores it, the predicate may not be sargable; WHERE date_trunc('day', created_at) = … cannot use a plain index on created_at. Rewrite it as a range or add an expression index.
A CONCURRENTLY build fails halfway. Postgres leaves an INVALID index that still slows writes. Find it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, drop it with DROP INDEX CONCURRENTLY, and retry.
Plain CREATE INDEX on a busy table. It blocks writes for the whole build. Stop the migration, and require CONCURRENTLY through a migration linter in CI.
Index spam. Asked to “optimize”, agents index every WHERE and ORDER BY column. Cap the count at two in the prompt and require a plan-backed reason for each.
MATERIALIZED CTEs on small inputs. Forced materialization blocks predicate push-down. Materialize only when the CTE is large and referenced more than once; otherwise remove the keyword and compare plans.
Interpolated SQL. Generated query builders sometimes concatenate user input into the query text. Reject any value that is not a bound parameter, and let a lint rule or test fail the build on it.
The MCP server can write. Restricted mode and a read-only role are two separate locks; keep both. If a session did write, restore from the staging snapshot and rotate the credential it used.

Where to go next with SQL tuning

Database MCP servers Roles, extensions, and install details for Postgres MCP Pro, DBHub, MCP Toolbox, Supabase, and Neon.

Database migration patterns Ship the index or schema change safely: expand, backfill, contract, lint gate, and rollback per phase.

ORM patterns Catch N+1 queries, bad transactions, and deprecated APIs in Prisma and TypeORM code.

Performance testing Turn a one-off query fix into a load test that fails CI when latency regresses.

For the whole database workflow from schema design to release, start at database development with agents. The other database recipes are listed in the database recipes overview.