SQL Query Optimization With an LLM: Verify Every Suggestion With EXPLAIN Plans

An LLM is good at suggesting SQL fixes and bad at knowing whether they work on your data. The fix for that is boring and reliable: treat every suggestion as a hypothesis, and check it with the database's own plan output, a row-equality comparison and a timing run. If the plan didn't change, the suggestion did nothing. If the rows differ, it's a different query.

This post gives you a prompt that asks for testable suggestions, plus a script that tests four of the most common ones. I ran it on 2026-10-09 against SQLite 3.45.1 with 200,000 invented order rows (seeded random data, nothing real). Where I say "the model suggests", that is illustrative: no live model call was part of this session, and the prompt is an untested template. The plan output and timings are real.

For getting an LLM to write read-only, schema-aware SQL in the first place, start with AI SQL assistant schema prompts and read-only guardrails. This post picks up after you have a query that works but is slow.

What should you give an LLM to optimize a SQL query?

Four things beyond the query itself, because without them the model is guessing about your database:

1. Schema with existing indexes. Output of .schema in SQLite or \d table in psql.
2. Row counts and distinct counts for the filtered and joined columns. A flag with three values and a user id with five thousand need different advice.
3. The plan, from EXPLAIN QUERY PLAN (SQLite) or EXPLAIN (Postgres).
4. What "slow" means: a measured time and a target, and whether the query is read-heavy or sits on a table with constant writes.

Untested template:

You are helping optimize one SQL query. Engine: SQLite 3.45 (adapt for yours).

SCHEMA AND INDEXES:
[paste schema, including every existing index]

ROW COUNTS / DISTINCT VALUES:
orders: 200,000 rows. status: 3 distinct values. customer_id: ~5,000 distinct.

QUERY:
[paste query]

CURRENT PLAN:
[paste EXPLAIN output]

MEASURED: 7 ms best of 7 runs. Table is written to constantly.

Give at most 3 candidate changes. For each:
1. The exact SQL (rewrite or CREATE INDEX).
2. What you expect to change in the plan, in the plan's own wording.
3. The cost of the change (write overhead, disk, behaviour differences).
4. A way to prove the rewritten query returns identical rows.
Say plainly if the current plan is already reasonable. Do not invent
statistics I did not give you.

The last two lines matter. Models like to find something to change, and "this plan is fine" is a legitimate answer you should explicitly allow.

How do you verify a suggestion before shipping it?

Three checks, in this order, because each one rules out a different failure.

Plan changed in the way predicted. In SQLite, the documentation says each table read produces a line starting with SCAN (full-table scan) or SEARCH (only a subset of rows visited), and the line says which index and which WHERE terms are used. USE TEMP B-TREE FOR ORDER BY means an extra sort step. Compare the plan before and after, and compare it with what the model said it expected.

Same rows. A faster query that returns different rows is a bug. For small results, sort and compare in Python, as the script below does. In pure SQL you can run SELECT ... EXCEPT SELECT ... in both directions and expect zero rows each way (untested here, but standard SQL set logic).

Timing, on realistic data, best of several runs. Cold-cache and warm-cache numbers differ, and a single run is noise. Run each query several times and keep the best, and keep the table size close to production. On a 2,000-row test table almost everything looks fast.

What does the verification script look like?

This is the harness. It builds a fresh in-memory database per case so indexes from one case can't leak into the next, then prints plan, row equality and best-of-7 timing.

import random
import sqlite3
import time


def build():
    random.seed(42)
    con = sqlite3.connect(":memory:")
    con.executescript("""
    CREATE TABLE customers (id INTEGER PRIMARY KEY, city TEXT, tier TEXT);
    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, created_at TEXT,
                         status TEXT, amount REAL);""")
    cities = ["Pune", "Delhi", "Mumbai", "Chennai", "Jaipur", "Kochi"]
    con.executemany("INSERT INTO customers VALUES (?,?,?)",
                    [(i, random.choice(cities), random.choice(["free", "pro"])) for i in range(1, 5001)])
    rows = []
    for i in range(1, 200001):
        day = random.randint(1, 365)
        m, d = 1 + (day - 1) // 31, 1 + (day - 1) % 28
        rows.append((i, random.randint(1, 5000), f"2026-{m:02d}-{d:02d} 10:00:00",
                     random.choices(["paid", "refunded", "pending"], [90, 4, 6])[0],
                     round(random.uniform(100, 5000), 2)))
    con.executemany("INSERT INTO orders VALUES (?,?,?,?,?)", rows)
    con.commit()
    return con


def plan(con, sql):
    return [r[3] for r in con.execute("EXPLAIN QUERY PLAN " + sql)]


def timed(con, sql, n=7):
    best, out = 1e9, None
    for _ in range(n):
        t = time.perf_counter()
        out = con.execute(sql).fetchall()
        best = min(best, time.perf_counter() - t)
    return out, best * 1000


print("sqlite", sqlite3.sqlite_version)

# Case 1: function wrapped around an indexed column
con = build()
con.execute("CREATE INDEX idx_created ON orders(created_at)")
q = "SELECT id, amount FROM orders WHERE substr(created_at, 1, 7) = '2026-03'"
q2 = "SELECT id, amount FROM orders WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'"
print("\n=== Case 1: function on an indexed column (index on created_at exists)")
print("original plan:", plan(con, q))
print("rewrite plan: ", plan(con, q2))
r1, t1 = timed(con, q)
r2, t2 = timed(con, q2)
print(f"same rows: {sorted(r1) == sorted(r2)} ({len(r1)} rows) | original {t1:.2f} ms | rewrite {t2:.2f} ms")

# Case 2: equality filters then sort with LIMIT
q = ("SELECT id, created_at, amount FROM orders WHERE customer_id = 77 AND status = 'paid' "
     "ORDER BY created_at DESC LIMIT 10")
print("\n=== Case 2: equality filters plus ORDER BY ... LIMIT")
con = build()
print("no index:        ", plan(con, q))
base, t0 = timed(con, q)
con.execute("CREATE INDEX idx_cust ON orders(customer_id)")
print("single column:   ", plan(con, q))
r_a, t_a = timed(con, q)
con.execute("DROP INDEX idx_cust")
con.execute("CREATE INDEX idx_cust_status_created ON orders(customer_id, status, created_at)")
print("composite:       ", plan(con, q))
r_b, t_b = timed(con, q)
print(f"same rows: {base == r_a == r_b} | no index {t0:.2f} ms | single {t_a:.3f} ms | composite {t_b:.3f} ms")

# Case 3: index on a low-selectivity column
print("\n=== Case 3: index on status, which has 3 distinct values")
con = build()
q = "SELECT id, amount FROM orders WHERE status = 'paid' AND amount > 4990"
print("no index:  ", plan(con, q))
b, tb = timed(con, q)
con.execute("CREATE INDEX idx_status ON orders(status)")
print("with index:", plan(con, q))
a, ta = timed(con, q)
con.execute("DROP INDEX idx_status")
con.execute("CREATE INDEX idx_status_amount ON orders(status, amount)")
print("(status, amount):", plan(con, q))
c, tc = timed(con, q)
print(f"same rows: {sorted(b) == sorted(a) == sorted(c)} ({len(b)} rows) | none {tb:.2f} ms | status {ta:.2f} ms | (status, amount) {tc:.2f} ms")

# Case 4: join, filter and group
print("\n=== Case 4: join, filter, group by")
con = build()
q = ("SELECT c.city, count(*) FROM orders o JOIN customers c ON c.id = o.customer_id "
     "WHERE o.status = 'refunded' GROUP BY c.city ORDER BY 2 DESC")
print("no extra index:", plan(con, q))
b, tb = timed(con, q)
con.execute("CREATE INDEX idx_status_cust ON orders(status, customer_id)")
print("with (status, customer_id):", plan(con, q))
a, ta = timed(con, q)
print(f"same rows: {b == a} | before {tb:.2f} ms | after {ta:.2f} ms")

Output of the second of two runs (the plans were identical in both runs; the Case 1 timings were not, which I explain below):

sqlite 3.45.1

=== Case 1: function on an indexed column (index on created_at exists)
original plan: ['SCAN orders']
rewrite plan:  ['SEARCH orders USING INDEX idx_created (created_at>? AND created_at<?)']
same rows: True (16882 rows) | original 24.73 ms | rewrite 33.10 ms

=== Case 2: equality filters plus ORDER BY ... LIMIT
no index:         ['SCAN orders', 'USE TEMP B-TREE FOR ORDER BY']
single column:    ['SEARCH orders USING INDEX idx_cust (customer_id=?)', 'USE TEMP B-TREE FOR ORDER BY']
composite:        ['SEARCH orders USING INDEX idx_cust_status_created (customer_id=? AND status=?)']
same rows: True | no index 8.33 ms | single 0.024 ms | composite 0.009 ms

=== Case 3: index on status, which has 3 distinct values
no index:   ['SCAN orders']
with index: ['SEARCH orders USING INDEX idx_status (status=?)']
(status, amount): ['SEARCH orders USING COVERING INDEX idx_status_amount (status=? AND amount>?)']
same rows: True (351 rows) | none 14.23 ms | status 21.76 ms | (status, amount) 0.12 ms

=== Case 4: join, filter, group by
no extra index: ['SCAN o', 'SEARCH c USING INTEGER PRIMARY KEY (rowid=?)', 'USE TEMP B-TREE FOR GROUP BY', 'USE TEMP B-TREE FOR ORDER BY']
with (status, customer_id): ['SEARCH o USING COVERING INDEX idx_status_cust (status=?)', 'SEARCH c USING INTEGER PRIMARY KEY (rowid=?)', 'USE TEMP B-TREE FOR GROUP BY', 'USE TEMP B-TREE FOR ORDER BY']
same rows: True | before 12.89 ms | after 2.09 ms

Which LLM suggestions hold up, and which ones don't?

Here is how each case reads, with the kind of suggestion a model would plausibly make (illustrative, not a captured reply).

Three lessons sit in that table.

A better-looking plan is not a faster query. Case 1 did exactly what the theory says: the function around the column forced a full scan, and the range predicate let SQLite use the index. But the month matches 16,882 of 200,000 rows, about 8 percent, and fetching that many rows by index costs about the same as reading the table. My first run showed the rewrite faster (26.94 vs 19.87 ms) and my second showed it slower (24.73 vs 33.10 ms). The honest summary is "no measurable difference at this selectivity". If the range matched 100 rows, the story would differ. Always check how many rows the filter returns before you believe an index story.

Low-selectivity indexes can lose. Case 3 is the classic trap. status has three values and 90 percent of rows are paid, so the index points at most of the table and the extra lookups cost more than a scan. Adding the second column, amount, made the index selective for this query and let SQLite answer from the index alone (the plan says COVERING INDEX). Same table, same column, opposite outcomes. This is something a model can't see unless you give it the distribution.

Column order in a composite index is a decision, not a detail. In Case 2 the composite (customer_id, status, created_at) lets SQLite filter on two equality terms and read rows already in created_at order, which is why USE TEMP B-TREE FOR ORDER BY disappears from the plan. Put equality columns first and the sort column last. A model will often get this right; you still verify it by looking for the sort line.

What about Postgres and MySQL?

I only ran SQLite here, so I can't paste Postgres output for you. What I did check is the Postgres documentation for EXPLAIN, read on 2026-10-09: plain EXPLAIN shows the plan the planner generates, and EXPLAIN ANALYZE actually executes the statement and adds real timings and row counts. The docs warn that side effects of INSERT, UPDATE, DELETE and similar statements still happen, and suggest wrapping them in BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;. For an LLM-assisted workflow that matters twice: never let an agent run EXPLAIN ANALYZE on writes against a live database, and prefer a replica or a restored copy for any timing work.

The node names differ (Postgres talks about sequential scans and index scans rather than SCAN and SEARCH), and Postgres adds estimated versus actual row counts, which are the first thing to compare when a plan looks wrong. The three-step verification (plan, rows, timing) carries over unchanged. If you build this into a tool, the data analyst agent walkthrough shows how to expose database access as narrow tools rather than a raw connection.

What does an index cost that the benchmark doesn't show?

Every index must be updated on each insert and on updates to indexed columns, and it takes disk space. I did not measure write overhead in this test, so I can't give you a number, and I won't invent one. Before adding an index suggested by a model, answer these:

• Is this query frequent or on a critical path, or only slow once a week?
• How write-heavy is the table? A log table with thousands of inserts per second pays more for each extra index than a reference table.
• Does an existing index already cover most of this one? Extending an existing index by one column often beats adding a new one.
• Can you drop the old single-column index afterwards? In Case 2 the composite makes idx_cust redundant for this query, since it begins with customer_id.

A review checklist for LLM-suggested SQL changes

1. Did you give the model schema, indexes, row counts, distinct counts and the current plan?
2. Did it state the expected plan change in the engine's own wording?
3. Did the actual plan change that way (SCAN to SEARCH, sort line gone)?
4. Are the result rows identical, checked by code and not by eye?
5. Is the timing better on data of realistic size, best of several runs?
6. How many rows does the filter match? If it is a big share of the table, expect no gain.
7. What does the change cost on writes, and does it make an existing index redundant?
8. Did you try the change on a copy first, with a way to revert (DROP INDEX, git for the query)?

For a ready-made set of SQL and EDA prompts to start from, see AI prompts for data analysts in India.

Limits of this test

SQLite's planner is simpler than Postgres or MySQL's, and its output format is its own. The data is uniform random, which flatters indexes in some cases and hides skew problems in others. Timings come from one machine, in memory, so disk effects are absent. Treat the structure of the checks as portable and the specific numbers as illustrations from a single run.