Learning Hub

How to Read SQLite EXPLAIN QUERY PLAN and Build a Plan Gate for AI-Generated SQL

Learn to read SQLite’s EXPLAIN QUERY PLAN, fix scans with the right indexes, and build a Python plan gate and step budget that catch slow AI-written SQL, with every number measured on a 500,000-row...

October 1, 2026 45 Min Read
39

Somewhere in your application, a query is reading every row of a table to find a handful of them. In development the table holds a few hundred rows and nobody notices. In production it holds millions, and that one query becomes the slowest thing you run. The cheapest way to catch it early is to ask the database how it plans to execute the query, and SQLite will tell you if you put EXPLAIN QUERY PLAN in front of the statement.

Table Of Content

That habit matters more now that assistants and agents write SQL for us. A model that turns “show me the orders from 15 September” into a query has never seen your indexes, and a query can be perfectly valid and still read half a million rows to answer. In this tutorial you will learn to read a query plan, fix the plans that matter with the right indexes, and then turn what you learned into a small Python plan gate and step budget that you can run on AI-written SQL before it touches a real database.

Everything runs on your own machine with Python’s built-in sqlite3 module. You will build a 500,000-row lab database, run eleven short scripts, write a gate with ten passing tests, and see measured evidence for every rule of thumb, including the cases where an index exists and SQLite still refuses to use it. All timings come from one Windows 11 machine running SQLite 3.50.4, so your milliseconds will differ, but the plan lines and the ratios between timings should match.

Prerequisites

  • Python 3 with the standard sqlite3 module. I ran everything on Python 3.13.14. The code uses nothing exotic (dataclasses, regular expressions, the walrus operator), but 3.13.14 is the only version I tested.
  • pytest for the gate’s test suite (I used 9.1.1).
  • About 100 MB of free disk space. The database is 23 MB before any index, and some steps create several indexes in turn.
  • Basic SQL. You should be comfortable reading SELECT, WHERE, ORDER BY, LIMIT and JOIN. If subqueries feel shaky, our SQL subqueries guide is a good warm-up.
  • Optional: Ollama with the qwen2.5:1.5b and qwen3.5:4b models, only if you want to regenerate the AI-written queries in Step 11. The answers I got are frozen in a file, so you can skip this.

Create a folder, a virtual environment, and check which SQLite version your Python carries. On macOS or Linux activate the environment with source .venv/bin/activate instead of the PowerShell line.

mkdir plan-lab
cd plan-lab
python -m venv .venv
.venv\Scripts\Activate.ps1
pip install pytest
python -c "import sqlite3; print(sqlite3.sqlite_version)"
3.50.4

If yours prints a different version, the plans in this article should still look familiar, but their exact wording may differ. That is not a nuisance to ignore: it is the central risk of Step 9, where we write code that reads plans.

The idea in five minutes: how SQLite chooses a plan

SQL is declarative. You describe the rows you want, and the database’s query planner decides how to fetch them. For each table in a query the planner picks a strategy. The slow strategy is a full table scan: read every row and test the WHERE clause against each one. The fast strategy is an index search: jump straight to the matching rows through a second, sorted structure called an index.

Think of a phone book. It is sorted by last name and then first name, so finding every Garcia is quick, and finding the phone number for one Alex Garcia is quicker still. Finding everyone called Alex, whatever their last name, means reading the whole book. A database index behaves the same way. SQLite’s own query planning guide puts it this way: “A multi-column index follows the same pattern as a single-column index; the indexed columns are added in front of the rowid.” It adds: “The left-most column is the primary key used for ordering the rows in the index. The second column is used to break ties in the left-most column.” Keep that ordering in mind, because Step 3 and Step 6 both depend on it.

You do not have to guess which strategy the planner picked. The EXPLAIN documentation says that putting EXPLAIN QUERY PLAN before a statement “causes the SQL statement to behave as a query and to return information about how the SQL statement would have operated if the EXPLAIN keyword or phrase had been omitted.” In other words, you get the plan without running the query. The EXPLAIN QUERY PLAN guide adds that “The output from EXPLAIN QUERY PLAN shows how the query is actually evaluated, not how it is specified in the SQL statement,” so it is the ground truth about what the planner decided.

Four words appear again and again in plans, so let us fix their meaning now:

  • SCAN means SQLite reads through a whole table or a whole index.
  • SEARCH means SQLite visits only part of it, usually by binary-searching an index.
  • COVERING INDEX means the index alone contains every column the query needs, so the table is never touched.
  • TEMP B-TREE means SQLite builds a temporary sorted structure to satisfy ORDER BY, GROUP BY or DISTINCT.

One warning before we start, and we will come back to it. The EXPLAIN QUERY PLAN guide says: “The data returned by the EXPLAIN QUERY PLAN command is intended for interactive debugging only. The output format may change between SQLite releases. Applications should not depend on the output format of the EXPLAIN QUERY PLAN command.” Reading plans yourself is safe. Writing software that parses them needs extra care, and Step 9 shows what that care looks like.

Step 1: Build the lab database

We need a table big enough that a bad plan hurts. The script below builds shop.db with 20,000 customers and 500,000 orders. It uses a seeded random generator, so every reader gets identical data, and it deliberately creates no indexes and runs no ANALYZE. Two details matter later. Order ids grow with time, like a real append-only orders table. And order statuses are skewed: 62% are delivered while only 1% are refunded, because real data is rarely uniform and the skew will show up in Step 7.

First the shared helpers. You will import them in every later script, so it is worth knowing what each does.

# common.py
"""common.py - helpers shared by every step."""
import sqlite3
import statistics
import time

DB = "shop.db"


def connect(path=DB):
    return sqlite3.connect(path)


def plan_rows(conn, sql, params=()):
    """EXPLAIN QUERY PLAN as (id, parent, detail) tuples."""
    return [(r[0], r[1], r[3]) for r in conn.execute("EXPLAIN QUERY PLAN " + sql, params)]


def plan(conn, sql, params=()):
    """Just the detail text of each plan node."""
    return [detail for _, _, detail in plan_rows(conn, sql, params)]


def show(conn, sql, params=()):
    """Print the query on one line, then its plan indented by tree depth."""
    print(" ".join(sql.split()))
    depth = {}
    for node_id, parent, detail in plan_rows(conn, sql, params):
        depth[node_id] = depth.get(parent, -1) + 1
        print("   " + "  " * depth[node_id] + detail)


def bench(conn, sql, params=(), repeat=7):
    """Median milliseconds to run the query and fetch every row (one warm-up run is discarded)."""
    conn.execute(sql, params).fetchall()
    times = []
    for _ in range(repeat):
        start = time.perf_counter()
        conn.execute(sql, params).fetchall()
        times.append((time.perf_counter() - start) * 1000)
    return statistics.median(times)


def bench_many(conn, sql, param_list, repeat=5):
    """Median milliseconds to run the query once for every parameter set in param_list."""
    for params in param_list:
        conn.execute(sql, params).fetchall()
    times = []
    for _ in range(repeat):
        start = time.perf_counter()
        for params in param_list:
            conn.execute(sql, params).fetchall()
        times.append((time.perf_counter() - start) * 1000)
    return statistics.median(times)


def used_pages(conn):
    return conn.execute("PRAGMA page_count").fetchone()[0] - conn.execute("PRAGMA freelist_count").fetchone()[0]


def create_index(conn, ddl):
    """Run a CREATE INDEX statement. Returns (seconds taken, megabytes of database added)."""
    page_size = conn.execute("PRAGMA page_size").fetchone()[0]
    before = used_pages(conn)
    start = time.perf_counter()
    conn.execute(ddl)
    seconds = time.perf_counter() - start
    return seconds, (used_pages(conn) - before) * page_size / 1e6


def reset(conn):
    """Back to a clean slate: drop every index we created and forget any ANALYZE statistics."""
    for (name,) in conn.execute("SELECT name FROM sqlite_master WHERE type = 'index' AND sql IS NOT NULL").fetchall():
        conn.execute(f'DROP INDEX "{name}"')
    if conn.execute("SELECT 1 FROM sqlite_master WHERE name = 'sqlite_stat1'").fetchone():
        conn.execute("DELETE FROM sqlite_stat1")
    conn.commit()

plan_rows runs EXPLAIN QUERY PLAN and returns the nodes. show prints a query followed by its plan, indented by tree depth. bench runs a query seven times after one discarded warm-up and returns the median in milliseconds; it fetches every row, so we time the work of delivering results and not just starting the query. bench_many does the same for a batch of lookups. create_index runs a CREATE INDEX statement and reports how long it took and how many megabytes it added, measured as pages in use (page_count minus freelist_count) so that reused free pages do not hide the growth. reset drops every index and forgets any saved statistics, so each step starts from the same clean slate.

Note that plan_rows forwards the query’s parameters to EXPLAIN QUERY PLAN. If you forget to, SQLite raises Incorrect number of bindings supplied, because the explained statement still contains its ? placeholders.

Now the database builder:

# build_db.py
"""build_db.py - build shop.db, a deterministic sample database (20,000 customers, 500,000 orders)."""
import random
import sqlite3
import sys
from datetime import datetime, timedelta

N_CUSTOMERS = 20_000
N_ORDERS = 500_000
START = datetime(2025, 1, 1)
END = datetime(2026, 9, 30)
SPAN = int((END - START).total_seconds())

LAST = ["garcia", "smith", "chen", "kumar", "nguyen", "muller", "silva", "rossi", "ivanov", "tanaka"]
FIRST = ["alex", "sam", "maria", "li", "priya", "tom", "ana", "luca", "olga", "kenji"]
DOMAINS = ["example.com", "example.org", "mail.test"]
COUNTRIES = ["US", "DE", "IN", "BR", "JP", "FR", "GB", "CA"]
TIERS = ["free", "pro", "enterprise"]
STATUSES = ["delivered", "shipped", "paid", "pending", "cancelled", "refunded"]
STATUS_WEIGHTS = [62, 14, 10, 6, 7, 1]

SCHEMA = """
CREATE TABLE customers (
    id         INTEGER PRIMARY KEY,
    email      TEXT NOT NULL,
    country    TEXT NOT NULL,
    tier       TEXT NOT NULL,
    created_at TEXT NOT NULL
);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status      TEXT NOT NULL,
    total_cents INTEGER NOT NULL,
    created_at  TEXT NOT NULL
);
"""


def stamp(seconds):
    return (START + timedelta(seconds=seconds)).strftime("%Y-%m-%d %H:%M:%S")


def customer_rows(rng):
    for i in range(1, N_CUSTOMERS + 1):
        email = f"{rng.choice(LAST)}.{rng.choice(FIRST)}{i}@{rng.choice(DOMAINS)}"
        joined = (datetime(2024, 1, 1) + timedelta(seconds=rng.randrange(365 * 86400))).strftime("%Y-%m-%d %H:%M:%S")
        yield (i, email, rng.choice(COUNTRIES), rng.choices(TIERS, [80, 15, 5])[0], joined)


def order_rows(rng):
    # ids grow with time, like a real append-only orders table
    moments = sorted(rng.randrange(SPAN) for _ in range(N_ORDERS))
    for sec in moments:
        yield (
            rng.randint(1, N_CUSTOMERS),
            rng.choices(STATUSES, STATUS_WEIGHTS)[0],
            max(199, int(rng.lognormvariate(8.0, 0.9))),
            stamp(sec),
        )


def main(path="shop.db"):
    rng = random.Random(2026)
    conn = sqlite3.connect(path)
    conn.executescript(SCHEMA)
    conn.executemany("INSERT INTO customers VALUES (?, ?, ?, ?, ?)", customer_rows(rng))
    conn.executemany(
        "INSERT INTO orders (customer_id, status, total_cents, created_at) VALUES (?, ?, ?, ?)",
        order_rows(rng),
    )
    conn.commit()
    for table in ("customers", "orders"):
        print(table, conn.execute(f"SELECT count(*) FROM {table}").fetchone()[0], "rows")
    pages = conn.execute("PRAGMA page_count").fetchone()[0]
    size = conn.execute("PRAGMA page_size").fetchone()[0]
    print("sqlite", sqlite3.sqlite_version, "| page_size", size, "| page_count", pages)
    print("indexes:", conn.execute("SELECT count(*) FROM sqlite_master WHERE type = 'index'").fetchone()[0])
    conn.close()


if __name__ == "__main__":
    main(*sys.argv[1:])

Run it:

python build_db.py
customers 20000 rows
orders 500000 rows
sqlite 3.50.4 | page_size 4096 | page_count 5618
indexes: 0

Check that it worked

Your row counts must be exactly 20000 and 500000, and the last line must say indexes: 0. The page count (5,618 pages of 4,096 bytes, about 23 MB) is also deterministic on the same SQLite version. If the script takes more than a few seconds, something is wrong with disk speed, not with the script.

Step 2: Read your first plan, add an index, read it again

Our first query looks up one customer’s orders. We will explain it, time it, create an index, and explain and time it again.

# step2_first_plan.py
"""Step 2: read a plan, add an index, read the plan again."""
from common import bench, connect, create_index, reset, show

conn = connect()
reset(conn)
QUERY = "SELECT id, total_cents FROM orders WHERE customer_id = ?"

print("--- the raw rows: (id, parent, notused, detail)")
for row in conn.execute("EXPLAIN QUERY PLAN " + QUERY, (4242,)):
    print("   ", row)

print("--- without an index")
show(conn, QUERY, (4242,))
print(f"    {len(conn.execute(QUERY, (4242,)).fetchall())} rows, {bench(conn, QUERY, (4242,)):.3f} ms")

seconds, megabytes = create_index(conn, "CREATE INDEX idx_orders_customer ON orders(customer_id)")
print(f"--- CREATE INDEX took {seconds:.2f} s and added {megabytes:.1f} MB")

show(conn, QUERY, (4242,))
print(f"    {len(conn.execute(QUERY, (4242,)).fetchall())} rows, {bench(conn, QUERY, (4242,)):.3f} ms")
reset(conn)
python step2_first_plan.py
--- the raw rows: (id, parent, notused, detail)
    (2, 0, 216, 'SCAN orders')
--- without an index
SELECT id, total_cents FROM orders WHERE customer_id = ?
   SCAN orders
    27 rows, 23.659 ms
--- CREATE INDEX took 0.16 s and added 5.5 MB
SELECT id, total_cents FROM orders WHERE customer_id = ?
   SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
    27 rows, 0.043 ms

What you are looking at

The first block shows the raw rows from EXPLAIN QUERY PLAN. Each is a tuple of four fields. The guide describes them as “an integer node id, an integer parent id, an auxiliary integer field that is not currently used, and a description of the node.” The first two fields let a plan be a tree: our single node has id 2 and parent 0. The third field is the unused one (it holds 216 here, and you can ignore it). Only the fourth field, the description, matters day to day, and that is what show prints.

Without an index the description is SCAN orders. SQLite had no choice but to read all 500,000 rows to find 27 of them, which took about 23.7 ms on my machine. The guide says SCAN “is used for a full-table scan, including cases where SQLite iterates through all records in a table in an order defined by an index,” while SEARCH “indicates that only a subset of the table rows are visited.”

After CREATE INDEX idx_orders_customer ON orders(customer_id) the description becomes SEARCH orders USING INDEX idx_orders_customer (customer_id=?). Read it left to right: SEARCH (only part of the table), the index used, and in parentheses the WHERE terms that index can answer. The same query now takes about 0.043 ms, roughly 550 times faster. The price was 0.16 seconds to build the index and 5.5 MB of disk, about a quarter of the size of the database.

Check that it worked

You should see SCAN in the first plan and SEARCH in the second, and the same row count (27) both times. If you see SEARCH in both, an index from an earlier experiment survived; run python -c "import common; common.reset(common.connect())" and try again.

Step 3: Two columns, two orders, very different plans

Now a query with two conditions: refunded orders since 1 June 2025. One condition is an equality (status = 'refunded') and one is a range (created_at >= '2025-06-01'). A two-column index could serve both, but the order of its columns matters, so we will try both orders.

# step3_composite.py
"""Step 3: the same two columns in two orders."""
from common import bench, connect, create_index, reset, show

conn = connect()
QUERY = """SELECT id, total_cents FROM orders
           WHERE status = 'refunded' AND created_at >= '2025-06-01'"""

for name, columns in [("idx_range_first", "created_at, status"), ("idx_equality_first", "status, created_at")]:
    reset(conn)
    seconds, megabytes = create_index(conn, f"CREATE INDEX {name} ON orders({columns})")
    print(f"=== CREATE INDEX {name} ON orders({columns})  ({megabytes:.1f} MB)")
    show(conn, QUERY)
    print(f"    {len(conn.execute(QUERY).fetchall())} rows, {bench(conn, QUERY):.3f} ms")
reset(conn)
python step3_composite.py
=== CREATE INDEX idx_range_first ON orders(created_at, status)  (18.7 MB)
SELECT id, total_cents FROM orders WHERE status = 'refunded' AND created_at >= '2025-06-01'
   SEARCH orders USING INDEX idx_range_first (created_at>?)
    3791 rows, 29.016 ms
=== CREATE INDEX idx_equality_first ON orders(status, created_at)  (18.7 MB)
SELECT id, total_cents FROM orders WHERE status = 'refunded' AND created_at >= '2025-06-01'
   SEARCH orders USING INDEX idx_equality_first (status=? AND created_at>?)
    3791 rows, 8.577 ms

Both indexes contain the same data and cost the same 18.7 MB, yet the first query takes 29.0 ms and the second 8.6 ms, about 3.4 times faster. The plan text explains why. With (created_at, status) the index consumes only one term, (created_at>?), so SQLite walks every index entry from June onwards (roughly three quarters of the table) and checks the status as it goes. With (status, created_at) it consumes both, (status=? AND created_at>?): it jumps to the refunded entries and reads only the slice after the date.

SQLite’s optimizer overview states the rule for an index on columns a, b, c and d, used by a query with a=5 AND b IN (1,2,3) AND c>12 AND d='hello': “Only columns a, b, and c of the index would be usable. The d column would not be usable because it occurs to the right of c and c is constrained only by inequalities.” The practical rule is to put equality columns first, then one range column. Columns after a range column no longer narrow the search.

Step 4: A covering index answers from the index alone

A plain index finds the matching rows, but SQLite must still visit the table to read the other columns the query asks for. If the index also stores those columns, the table visit disappears. The planning guide explains: “Because all of the information needed is in the covering index, SQLite never needs to consult the original table in order to find the price.” We will time 300 customer lookups with a plain and a covering index.

# step4_covering.py
"""Step 4: a covering index answers the query from the index alone."""
import random

from common import bench_many, connect, create_index, reset, show

conn = connect()
rng = random.Random(7)
IDS = [(rng.randint(1, 20_000),) for _ in range(300)]
QUERY = "SELECT status, total_cents FROM orders WHERE customer_id = ?"

for ddl in (
    "CREATE INDEX idx_plain ON orders(customer_id)",
    "CREATE INDEX idx_covering ON orders(customer_id, status, total_cents)",
):
    reset(conn)
    seconds, megabytes = create_index(conn, ddl)
    print(f"=== {ddl}  ({megabytes:.1f} MB)")
    show(conn, QUERY, IDS[0])
    print(f"    300 lookups: {bench_many(conn, QUERY, IDS):.1f} ms")
reset(conn)
python step4_covering.py
=== CREATE INDEX idx_plain ON orders(customer_id)  (5.5 MB)
SELECT status, total_cents FROM orders WHERE customer_id = ?
   SEARCH orders USING INDEX idx_plain (customer_id=?)
    300 lookups: 46.0 ms
=== CREATE INDEX idx_covering ON orders(customer_id, status, total_cents)  (11.6 MB)
SELECT status, total_cents FROM orders WHERE customer_id = ?
   SEARCH orders USING COVERING INDEX idx_covering (customer_id=?)
    300 lookups: 11.6 ms

The plan wording changes from USING INDEX to USING COVERING INDEX, and 300 lookups drop from 46.0 ms to 11.6 ms, about four times faster. The trade-off is size: the covering index is 11.6 MB against 5.5 MB because it stores two extra columns for every row. Covering indexes are worth it for hot, narrow queries, not as a default for every query you write.

Step 5: Let an index do the sorting

A query with ORDER BY needs rows in order. Without a suitable index SQLite must collect rows and sort them in a temporary structure. The guide notes that “Using an index is almost always much more efficient than performing a sort.” We will test two common cases: the newest 20 orders overall, and one customer’s newest 10 orders.

# step5_order_by.py
"""Step 5: let an index do the sorting."""
import random

from common import bench, bench_many, connect, create_index, reset, show

conn = connect()
NEWEST = "SELECT id, created_at FROM orders ORDER BY created_at DESC LIMIT 20"
PAGE = "SELECT id, created_at FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10"
rng = random.Random(7)
IDS = [(rng.randint(1, 20_000),) for _ in range(300)]

print("##### the 20 newest orders")
reset(conn)
show(conn, NEWEST)
print(f"    {bench(conn, NEWEST):.3f} ms")
create_index(conn, "CREATE INDEX idx_created ON orders(created_at)")
show(conn, NEWEST)
print(f"    {bench(conn, NEWEST):.3f} ms")

print("##### one customer's 10 newest orders, 300 customers")
for ddl in (
    "CREATE INDEX idx_customer ON orders(customer_id)",
    "CREATE INDEX idx_customer_created ON orders(customer_id, created_at)",
):
    reset(conn)
    create_index(conn, ddl)
    print("===", ddl)
    show(conn, PAGE, IDS[0])
    print(f"    300 lookups: {bench_many(conn, PAGE, IDS):.1f} ms")
reset(conn)
python step5_order_by.py
##### the 20 newest orders
SELECT id, created_at FROM orders ORDER BY created_at DESC LIMIT 20
   SCAN orders
   USE TEMP B-TREE FOR ORDER BY
    94.961 ms
SELECT id, created_at FROM orders ORDER BY created_at DESC LIMIT 20
   SCAN orders USING COVERING INDEX idx_created
    0.041 ms
##### one customer's 10 newest orders, 300 customers
=== CREATE INDEX idx_customer ON orders(customer_id)
SELECT id, created_at FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10
   SEARCH orders USING INDEX idx_customer (customer_id=?)
   USE TEMP B-TREE FOR ORDER BY
    300 lookups: 45.4 ms
=== CREATE INDEX idx_customer_created ON orders(customer_id, created_at)
SELECT id, created_at FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10
   SEARCH orders USING COVERING INDEX idx_customer_created (customer_id=?)
    300 lookups: 10.0 ms

Without an index, the newest-20 query scans all 500,000 rows and sorts them (USE TEMP B-TREE FOR ORDER BY) just to keep 20, taking 95 ms. With an index on created_at, SQLite can read the index from the newest end, stop after 20 entries, and finish in 0.041 ms, more than 2,000 times faster.

Look closely at the second plan: SCAN orders USING COVERING INDEX idx_created. It still says SCAN, because SQLite is walking an index in order, but it stops after 20 entries because of LIMIT 20. This is a good scan. Remember it, because the gate in Step 9 has to tell this kind of scan from a bad one.

For the per-customer query, an index on customer_id alone still needs a temporary sort. An index on (customer_id, created_at) serves the filter and the order at once, so the sort node vanishes and 300 lookups fall from 45.4 ms to 10.0 ms. It is the same equality-first rule from Step 3, now with the ORDER BY column in second place.

Step 6: When the index exists but the plan still says SCAN

The most frustrating plan is the one where you did create the index and SQLite ignores it. Three causes account for most cases.

# step6_ignored.py
"""Step 6: the index exists, but the plan still says SCAN."""
from common import bench, connect, create_index, reset, show

conn = connect()


def run(sql, params=()):
    show(conn, sql, params)
    print(f"    {len(conn.execute(sql, params).fetchall())} rows, {bench(conn, sql, params):.3f} ms")


print("##### 6a. a function wrapped around the indexed column")
reset(conn)
create_index(conn, "CREATE INDEX idx_created ON orders(created_at)")
run("SELECT id, total_cents FROM orders WHERE substr(created_at, 1, 10) = '2026-09-15'")
print("--- fix 1: rewrite as a range")
run("SELECT id, total_cents FROM orders WHERE created_at >= '2026-09-15' AND created_at < '2026-09-16'")
print("--- fix 2: index the expression itself")
reset(conn)
create_index(conn, "CREATE INDEX idx_day ON orders(substr(created_at, 1, 10))")
run("SELECT id, total_cents FROM orders WHERE substr(created_at, 1, 10) = '2026-09-15'")

print("##### 6b. LIKE and collation")
reset(conn)
create_index(conn, "CREATE INDEX idx_email ON customers(email)")
print("--- plain index (BINARY collation)")
run("SELECT id FROM customers WHERE email LIKE 'garcia.alex%'")
run("SELECT id FROM customers WHERE email GLOB 'garcia.alex*'")
run("SELECT id FROM customers WHERE email >= 'garcia.alex' AND email < 'garcia.aley'")
run("SELECT id FROM customers WHERE email LIKE '%@example.org'")
reset(conn)
create_index(conn, "CREATE INDEX idx_email_nocase ON customers(email COLLATE NOCASE)")
print("--- index declared COLLATE NOCASE")
run("SELECT id FROM customers WHERE email LIKE 'garcia.alex%'")

print("##### 6c. OR needs an index for every branch")
OR_QUERY = "SELECT id FROM orders WHERE customer_id = 4242 OR status = 'refunded'"
reset(conn)
create_index(conn, "CREATE INDEX idx_customer ON orders(customer_id)")
run(OR_QUERY)
create_index(conn, "CREATE INDEX idx_status ON orders(status)")
run(OR_QUERY)
reset(conn)
python step6_ignored.py

6a. A function wrapped around the indexed column

##### 6a. a function wrapped around the indexed column
SELECT id, total_cents FROM orders WHERE substr(created_at, 1, 10) = '2026-09-15'
   SCAN orders
    770 rows, 33.266 ms
--- fix 1: rewrite as a range
SELECT id, total_cents FROM orders WHERE created_at >= '2026-09-15' AND created_at < '2026-09-16'
   SEARCH orders USING INDEX idx_created (created_at>? AND created_at<?)
    770 rows, 0.284 ms
--- fix 2: index the expression itself
SELECT id, total_cents FROM orders WHERE substr(created_at, 1, 10) = '2026-09-15'
   SEARCH orders USING INDEX idx_day (<expr>=?)
    770 rows, 0.289 ms

The index on created_at holds timestamps, not the first ten characters of them, so substr(created_at, 1, 10) = '2026-09-15' cannot use it. The plan is a plain SCAN orders at 33.3 ms. There are two fixes. Rewrite the filter as a range on the raw column, which uses the existing index and takes 0.28 ms. Or index the expression itself, which makes the original query fast (0.29 ms) and shows up in the plan as (<expr>=?).

The expression index documentation adds a limit worth remembering: the planner uses an expression index when the expression appears in the query “exactly as it is written in the CREATE INDEX statement,” and “The query planner does not do algebra.” An index on x+y will not serve y+x.

6b. LIKE, collation, and the leading wildcard

##### 6b. LIKE and collation
--- plain index (BINARY collation)
SELECT id FROM customers WHERE email LIKE 'garcia.alex%'
   SCAN customers USING COVERING INDEX idx_email
    187 rows, 0.525 ms
SELECT id FROM customers WHERE email GLOB 'garcia.alex*'
   SEARCH customers USING COVERING INDEX idx_email (email>? AND email<?)
    187 rows, 0.078 ms
SELECT id FROM customers WHERE email >= 'garcia.alex' AND email < 'garcia.aley'
   SEARCH customers USING COVERING INDEX idx_email (email>? AND email<?)
    187 rows, 0.073 ms
SELECT id FROM customers WHERE email LIKE '%@example.org'
   SCAN customers USING COVERING INDEX idx_email
    6622 rows, 2.370 ms
--- index declared COLLATE NOCASE
SELECT id FROM customers WHERE email LIKE 'garcia.alex%'
   SEARCH customers USING COVERING INDEX idx_email_nocase (email>? AND email<?)
    187 rows, 0.074 ms

Searching for emails that start with garcia.alex is a prefix search, which an index can answer. Yet LIKE with a plain index produces SCAN customers USING COVERING INDEX idx_email, while GLOB and the hand-written range both produce a SEARCH with (email>? AND email<?), about seven times faster (0.078 ms against 0.525 ms). Be aware that GLOB and the range compare case-sensitively while LIKE does not, so they are not drop-in replacements if your data mixes cases. The reason is in the optimizer overview: “The LIKE operator is case insensitive by default because this is what the SQL standard requires,” and “For the LIKE operator, if case_sensitive_like mode is enabled then the column must be indexed using the built-in BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must be indexed using the built-in NOCASE collating sequence.” Our index uses the default BINARY collation while LIKE is case-insensitive, so the optimization is off. Declaring the index COLLATE NOCASE turns it on: the last plan above is a SEARCH at 0.074 ms.

A pattern that starts with a wildcard, like '%@example.org', can never use the index, because the sorted order says nothing about how a value ends. It scans every entry whatever the collation, and returns 6,622 rows in 2.4 ms on this small table.

6c. OR needs an index for every branch

##### 6c. OR needs an index for every branch
SELECT id FROM orders WHERE customer_id = 4242 OR status = 'refunded'
   SCAN orders
    5020 rows, 32.182 ms
SELECT id FROM orders WHERE customer_id = 4242 OR status = 'refunded'
   MULTI-INDEX OR
     INDEX 1
       SEARCH orders USING INDEX idx_customer (customer_id=?)
     INDEX 2
       SEARCH orders USING INDEX idx_status (status=?)
    5020 rows, 1.288 ms

The planning guide states: “Multi-column indices only work if the constraint terms in the WHERE clause of the query are connected by AND.” With an index only on customer_id, a query that ORs it with a status test must scan the table (32.2 ms). Add an index on status and SQLite switches to MULTI-INDEX OR, searching each index and combining the results in 1.29 ms, 25 times faster. This plan is also the first one in this tutorial with nested lines, so notice the tree: INDEX 1 and INDEX 2 are children of the MULTI-INDEX OR node, which is exactly what the parent id in the raw rows encodes. show indents by that depth.

Step 7: Indexes are not free

It is tempting to index everything after seeing those numbers. Do not. Each index costs disk, slows every write, and sometimes does not help at all.

# step7_costs.py
"""Step 7: what an index costs - writes, disk, and queries it cannot help."""
import random
import sqlite3
import statistics
import time

from build_db import SCHEMA, STATUSES
from common import bench, connect, create_index, reset, show

print("##### 7a. insert cost, 100,000 rows into an in-memory copy of the orders table")
rng = random.Random(3)
ROWS = [
    (rng.randint(1, 20_000), rng.choice(STATUSES), rng.randint(199, 20_000), f"2026-{rng.randint(1, 9):02d}-{rng.randint(1, 28):02d} 12:00:00")
    for _ in range(100_000)
]
INDEXES = [
    "CREATE INDEX i1 ON orders(customer_id)",
    "CREATE INDEX i2 ON orders(status, created_at)",
    "CREATE INDEX i3 ON orders(created_at)",
    "CREATE INDEX i4 ON orders(customer_id, created_at)",
    "CREATE INDEX i5 ON orders(customer_id, status, total_cents)",
]
baseline = None
for count in (0, 1, 3, 5):
    runs = []
    for _ in range(3):
        mem = sqlite3.connect(":memory:")
        mem.executescript(SCHEMA)
        for ddl in INDEXES[:count]:
            mem.execute(ddl)
        start = time.perf_counter()
        mem.executemany("INSERT INTO orders (customer_id, status, total_cents, created_at) VALUES (?, ?, ?, ?)", ROWS)
        mem.commit()
        runs.append(time.perf_counter() - start)
        mem.close()
    seconds = statistics.median(runs)
    baseline = baseline or seconds
    print(f"    {count} indexes: {seconds:.3f} s  ({seconds / baseline:.1f}x the no-index time)")

print("##### 7b. an index on a column with few distinct values")
conn = connect()
reset(conn)
QUERY = "SELECT id, total_cents FROM orders WHERE status = 'delivered'"
show(conn, QUERY)
print(f"    {len(conn.execute(QUERY).fetchall())} rows, {bench(conn, QUERY):.1f} ms")
create_index(conn, "CREATE INDEX idx_status ON orders(status)")
show(conn, QUERY)
print(f"    {len(conn.execute(QUERY).fetchall())} rows, {bench(conn, QUERY):.1f} ms")

print("##### 7c. a partial index covers only the rows you query")
reset(conn)
_, full_mb = create_index(conn, "CREATE INDEX idx_full ON orders(status, created_at)")
conn.execute("DROP INDEX idx_full")
_, partial_mb = create_index(conn, "CREATE INDEX idx_pending ON orders(created_at) WHERE status = 'pending'")
print(f"    full index {full_mb:.1f} MB, partial index {partial_mb:.1f} MB")
TAIL = "ORDER BY created_at LIMIT 20"
for sql, params in [
    (f"SELECT id, created_at FROM orders WHERE status = 'pending' {TAIL}", ()),
    (f"SELECT id, created_at FROM orders WHERE status = ? {TAIL}", ("pending",)),
    (f"SELECT id, created_at FROM orders WHERE status IN ('pending') {TAIL}", ()),
    (f"SELECT id, created_at FROM orders WHERE status = 'paid' {TAIL}", ()),
]:
    show(conn, sql, params)
    print(f"    {bench(conn, sql, params):.3f} ms")
reset(conn)
python step7_costs.py

7a. Every index slows every insert

##### 7a. insert cost, 100,000 rows into an in-memory copy of the orders table
    0 indexes: 0.045 s  (1.0x the no-index time)
    1 indexes: 0.083 s  (1.8x the no-index time)
    3 indexes: 0.214 s  (4.7x the no-index time)
    5 indexes: 0.324 s  (7.1x the no-index time)

Inserting 100,000 rows into an in-memory copy of the orders table took 0.045 s with no indexes, 0.083 s with one, 0.214 s with three and 0.324 s with five: more than seven times slower. Every insert must update every index. The test runs in memory so the numbers isolate CPU work; I did not measure a disk-backed table, where the I/O cost would come on top.

7b. An index on a column with few distinct values

##### 7b. an index on a column with few distinct values
SELECT id, total_cents FROM orders WHERE status = 'delivered'
   SCAN orders
    310661 rows, 166.7 ms
SELECT id, total_cents FROM orders WHERE status = 'delivered'
   SEARCH orders USING INDEX idx_status (status=?)
    310661 rows, 199.8 ms

A query for status = 'delivered' returns 310,661 rows, 62% of the table. With an index, the plan says SEARCH, yet it ran in 199.8 ms against 166.7 ms for the plain scan, about 20% slower, most likely because following 310,661 index entries back to table rows is more work than reading the table in order. A plan that says SEARCH is not a promise of speed; it only says SQLite is using an index. We will meet this again in Step 11.

7c. A partial index covers only the rows you query

##### 7c. a partial index covers only the rows you query
    full index 18.7 MB, partial index 0.8 MB
SELECT id, created_at FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20
   SCAN orders USING COVERING INDEX idx_pending
    0.081 ms
SELECT id, created_at FROM orders WHERE status = ? ORDER BY created_at LIMIT 20
   SCAN orders USING COVERING INDEX idx_pending
    0.091 ms
SELECT id, created_at FROM orders WHERE status IN ('pending') ORDER BY created_at LIMIT 20
   SCAN orders
   USE TEMP B-TREE FOR ORDER BY
    35.579 ms
SELECT id, created_at FROM orders WHERE status = 'paid' ORDER BY created_at LIMIT 20
   SCAN orders
   USE TEMP B-TREE FOR ORDER BY
    41.886 ms

If you only ever look up one kind of row, index only those rows. Here the work queue of pending orders is 6% of the table. A full index on (status, created_at) takes 18.7 MB, while CREATE INDEX ... ON orders(created_at) WHERE status = 'pending' takes 0.8 MB, about 23 times less, and writes to rows that are not pending no longer have to maintain it.

The catch is in the four plans. status = 'pending' uses the partial index (0.081 ms). So does status = ? with the value bound to 'pending' (0.091 ms), although the partial index page does not discuss bound parameters in queries, so treat that as an observation about SQLite 3.50.4 and test it on your own version. But status IN ('pending') falls back to a scan plus a sort (35.6 ms), and status = 'paid' cannot use the index at all (41.9 ms). The documentation is blunt: “The terms in W and X must match exactly. SQLite does not do algebra to try to get them to look the same.” Here W is the query’s WHERE clause and X is the index’s.

Step 8: The same query gets a different plan after ANALYZE

SQLite plans from whatever it knows about your data. By default it knows little. The ANALYZE command gathers statistics about your tables and indexes, such as row counts and how many rows share each leading value, and stores them in a table called sqlite_stat1. Our next script builds an index on (status, created_at) and then asks for recent orders by date alone, with no mention of status.

# step8_analyze.py
"""Step 8: the same schema, the same query, a different plan after ANALYZE."""
from common import bench, connect, create_index, reset, show

conn = connect()
reset(conn)
create_index(conn, "CREATE INDEX idx_status_created ON orders(status, created_at)")
QUERY = "SELECT id FROM orders WHERE created_at >= '2026-09-29'"

print("--- before ANALYZE")
show(conn, QUERY)
print(f"    {bench(conn, QUERY):.3f} ms")

conn.execute("ANALYZE")
print("--- sqlite_stat1 after ANALYZE")
for row in conn.execute("SELECT tbl, idx, stat FROM sqlite_stat1 ORDER BY tbl"):
    print("   ", row)

print("--- after ANALYZE")
show(conn, QUERY)
print(f"    {bench(conn, QUERY):.3f} ms")
reset(conn)
python step8_analyze.py
--- before ANALYZE
SELECT id FROM orders WHERE created_at >= '2026-09-29'
   SCAN orders USING COVERING INDEX idx_status_created
    22.982 ms
--- sqlite_stat1 after ANALYZE
    ('customers', None, '20000')
    ('orders', 'idx_status_created', '500000 83334 1')
--- after ANALYZE
SELECT id FROM orders WHERE created_at >= '2026-09-29'
   SEARCH orders USING COVERING INDEX idx_status_created (ANY(status) AND created_at>?)
    0.215 ms

Before ANALYZE the plan is a full scan of the index (23.0 ms). After it, the plan becomes SEARCH ... (ANY(status) AND created_at>?) and takes 0.215 ms, about 100 times faster, with no change to the schema or the query. SQLite has switched to a skip-scan: it hops from one status value to the next and range-searches the date within each. The optimizer overview introduces it with “However, in some cases, SQLite is able to use an index even if the first few columns of the index are omitted from the WHERE clause but later columns are included,” and says why statistics matter: “The only way that SQLite can know that there are many duplicates in the left-most columns of an index is if the ANALYZE command has been run on the database.” It concludes: “Hence, a skip-scan is never used on a database that has not been analyzed.” Without statistics, in its words, “the default guess is that there are an average of 10 duplicates for every value in the left-most column of the index.”

The saved statistics are worth reading once. The file format documentation explains the stat column: “The first integer in this list is the approximate number of rows in the index.” “The second integer is the approximate number of rows in the index that have the same value in the first column of the index.” “The third integer is the number of rows in the index that have the same value for the first two columns.” So '500000 83334 1' says: 500,000 entries, about 83,334 per distinct status (500,000 divided by our six statuses), and one entry per status and timestamp pair.

For real applications the ANALYZE documentation recommends: “Applications with short-lived database connections should run ‘PRAGMA optimize;’ once, just prior to closing each database connection.” Long-lived connections should run PRAGMA optimize=0x10002; when they open and PRAGMA optimize; periodically. The lesson for plan checking is just as important: a plan you inspect on an un-analyzed development database may differ from the plan production uses. The same page describes a way to capture statistics from a representative database and load them everywhere, which is how you make development, CI and production agree. The script ends with reset(conn), which clears the statistics again so later steps start clean.

Step 9: Turn plan reading into a gate

You can now read a plan. The next question is how to apply that skill automatically, for example to every query an agent wants to run. The idea of a plan gate is simple: ask SQLite for the plan, look for the bad shapes you just learned, and refuse the query (or ask a human) when you find one.

The EXPLAIN documentation warns that the output “is intended for interactive analysis and troubleshooting only” and that its details “are subject to change from one release of SQLite to the next,” and the EXPLAIN QUERY PLAN guide notes the format changed substantially in version 3.24.0 (2018-06-04) with further minor changes in 3.36.0 (2021-06-18). So this gate is a smoke alarm, not a contract. We take three precautions. It is conservative and reports what it did not understand. It pins the SQLite version it was tested on. And its test suite includes a canary test that fails loudly if a SQLite upgrade rewords the plan lines the gate depends on.

The gate

# plan_gate.py
"""plan_gate.py - reject a query whose EXPLAIN QUERY PLAN shows a big scan, a sort or an automatic index.

EXPLAIN QUERY PLAN output is meant for people and may change between SQLite releases, so this gate
is a smoke alarm: pin the SQLite version you tested and keep test_plan_gate.py in CI.
"""
import re
import sqlite3
import sys
from dataclasses import dataclass
from pathlib import Path

from common import plan_rows

TESTED_ON = "3.50"
SCAN = re.compile(r"^SCAN (?P<name>\S+)(?: USING (?:COVERING )?INDEX (?P<index>\S+))?$")
AUTOMATIC = re.compile(r"^SEARCH (?P<name>\S+) USING AUTOMATIC (?:COVERING )?INDEX")
SORT = re.compile(r"^USE TEMP B-TREE FOR (?:ORDER BY|GROUP BY|DISTINCT)")
FROM_JOIN = re.compile(r"\b(?:from|join)\s+([A-Za-z_]\w*)(?:\s+(?:as\s+)?([A-Za-z_]\w*))?", re.I)
NOT_ALIASES = {"where", "join", "left", "right", "inner", "outer", "cross", "natural", "full", "on", "using",
               "group", "order", "limit", "union", "except", "intersect", "having", "window", "indexed", "not"}
HINTS = {
    "full-table-scan": "no index serves this query: check functions on columns, leading wildcards and missing indexes",
    "index-scan": "reads every entry of an index: fine for ORDER BY ... LIMIT, wasteful as a filter",
    "temp-btree": "SQLite sorts rows in a temporary b-tree: an index that matches the ORDER BY avoids it",
    "automatic-index": "SQLite builds a throwaway index on every run: create a real index on the join column",
    "unresolved-scan": "could not map this name to a table (CTE, subquery or an alias the gate cannot parse)",
}


@dataclass(frozen=True)
class Finding:
    rule: str
    severity: str  # "fail" blocks the query, "warn" asks for a human look
    node: str
    hint: str


def real_tables(conn):
    rows = conn.execute("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'")
    return {name.lower(): name for (name,) in rows}


def alias_map(sql, tables):
    """alias -> table for the common FROM/JOIN shapes (a regex, not a SQL parser)."""
    return {alias: table for table, alias in FROM_JOIN.findall(sql)
            if table.lower() in tables and alias and alias.lower() not in NOT_ALIASES}


def check(conn, sql, params=(), min_rows=10_000):
    """Return the findings for one SELECT. Tables with fewer than min_rows rows may be scanned freely."""
    if not sql.lstrip().lower().startswith(("select", "with")):
        raise ValueError("the plan gate only accepts SELECT statements")
    tables = real_tables(conn)
    aliases = alias_map(sql, tables)
    counts = {}

    def table_of(name):
        return tables.get(aliases.get(name, name).lower())

    def is_big(table):
        if table not in counts:
            counts[table] = conn.execute(f'SELECT count(*) FROM "{table}"').fetchone()[0]
        return counts[table] >= min_rows

    findings = []
    for _, _, detail in plan_rows(conn, sql, params):
        if match := AUTOMATIC.match(detail):
            table = table_of(match["name"])
            if table is None or is_big(table):
                findings.append(Finding("automatic-index", "fail", detail, HINTS["automatic-index"]))
        elif match := SCAN.match(detail):
            table = table_of(match["name"])
            if table is None:
                findings.append(Finding("unresolved-scan", "warn", detail, HINTS["unresolved-scan"]))
            elif is_big(table):
                rule, severity = ("index-scan", "warn") if match["index"] else ("full-table-scan", "fail")
                findings.append(Finding(rule, severity, detail, HINTS[rule]))
        elif SORT.match(detail):
            findings.append(Finding("temp-btree", "warn", detail, HINTS["temp-btree"]))
    return findings


def main(argv):
    strict = "--strict" in argv
    args = [a for a in argv if a != "--strict"]
    if len(args) != 2:
        print("usage: python plan_gate.py DATABASE \"SELECT ...\" [--strict]")
        return 2
    if not sqlite3.sqlite_version.startswith(TESTED_ON):
        print(f"note: tested on SQLite {TESTED_ON}.x, running {sqlite3.sqlite_version}; run test_plan_gate.py", file=sys.stderr)
    conn = sqlite3.connect(Path(args[0]).resolve().as_uri() + "?mode=ro", uri=True)
    conn.execute("PRAGMA query_only = ON")
    try:
        findings = check(conn, args[1])
    except (sqlite3.Error, ValueError) as exc:
        print(f"rejected: {exc}")
        return 2
    for f in findings:
        print(f"{f.severity.upper():4} {f.rule}: {f.node}\n     {f.hint}")
    if not findings:
        print("ok: no big scans, sorts or automatic indexes")
    return 1 if any(f.severity == "fail" for f in findings) or (strict and findings) else 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

Here is how it works, piece by piece.

The three regular expressions match the plan lines we care about: SCAN, SEARCH ... USING AUTOMATIC INDEX and USE TEMP B-TREE. An automatic index is one SQLite may build on the fly, lasting only for a single statement, when no suitable index exists. Seeing it means a missing index on a join column, and we confirm it in the tests with a self-join on the unindexed email column: SEARCH b USING AUTOMATIC COVERING INDEX (email=?).

Plans print the alias of a table, not its name: you saw SCAN o in the demo output below. alias_map therefore reads FROM and JOIN clauses with a regular expression to map aliases back to real tables. It is not a SQL parser and handles only the common shapes, so a name it cannot resolve becomes an unresolved-scan warning rather than a silent pass.

check only accepts statements starting with SELECT or WITH, counts the rows of each scanned table once, and ignores scans of tables smaller than min_rows (10,000 by default), because scanning a 20-row lookup table is fine. Then it applies the rules in this table.

Rule Severity Plan line it matches What it means
full-table-scan fail SCAN t on a big table No index serves the query. Fix the index or the filter.
index-scan warn SCAN t USING [COVERING] INDEX i Reads every index entry. Fine for ORDER BY ... LIMIT, wasteful as a filter.
temp-btree warn USE TEMP B-TREE FOR ... A sort or grouping without a matching index.
automatic-index fail SEARCH t USING AUTOMATIC INDEX SQLite is building a throwaway index on every run.
unresolved-scan warn SCAN x for an unknown name A CTE, a subquery or an alias the gate could not map.

Why is index-scan only a warning? Because of Step 5. The newest-20 query scans an index and is perfect, while a LIKE '%...' filter also scans an index and is wasteful. A plan alone cannot tell them apart, so the gate flags the shape and lets a human, or the step budget in Step 10, decide.

The command-line entry point opens the database read-only through a URI with mode=ro and also sets PRAGMA query_only = ON. It exits 0 for a clean or warn-only query, 1 when a rule fails (or for any finding with --strict), and 2 when the input was rejected. The SQLite documentation for URI filenames notes that “the database can be opened read-only by using ‘mode=ro’ as a query parameter,” and the pragma documentation says “The query_only pragma prevents data changes on database files when enabled” but also “However, the database is not truly read-only,” which is why we use both.

The tests

# test_plan_gate.py
"""test_plan_gate.py - run with: python -m pytest -q test_plan_gate.py"""
import random
import re
import sqlite3

import pytest

from build_db import FIRST, LAST, SCHEMA, STATUSES
from common import plan
from plan_gate import check

MIN = 1_000  # treat any table with 1,000+ rows as "big" in these tests


@pytest.fixture()
def db():
    conn = sqlite3.connect(":memory:")
    conn.executescript(SCHEMA)
    conn.execute("CREATE TABLE countries (code TEXT PRIMARY KEY, name TEXT)")
    conn.executemany("INSERT INTO countries VALUES (?, ?)", [("US", "United States"), ("DE", "Germany"), ("IN", "India")])
    rng = random.Random(5)
    conn.executemany(
        "INSERT INTO orders (customer_id, status, total_cents, created_at) VALUES (?, ?, ?, ?)",
        [(rng.randint(1, 3000), rng.choice(STATUSES), rng.randint(199, 9999),
          f"2026-{rng.randint(1, 9):02d}-{rng.randint(1, 28):02d} 10:00:00") for _ in range(20_000)],
    )
    conn.executemany(
        "INSERT INTO customers VALUES (?, ?, 'US', 'free', '2025-01-01 00:00:00')",
        [(i, f"{rng.choice(LAST)}.{rng.choice(FIRST)}{i}@example.com") for i in range(1, 3001)],
    )
    return conn


def rules(findings):
    return sorted(f.rule for f in findings)


def test_unindexed_filter_is_a_full_table_scan(db):
    found = check(db, "SELECT id FROM orders WHERE customer_id = ?", (7,), min_rows=MIN)
    assert rules(found) == ["full-table-scan"] and found[0].severity == "fail"


def test_indexed_filter_is_clean(db):
    db.execute("CREATE INDEX idx_customer ON orders(customer_id)")
    assert check(db, "SELECT id FROM orders WHERE customer_id = ?", (7,), min_rows=MIN) == []


def test_function_on_column_fails_until_the_expression_is_indexed(db):
    db.execute("CREATE INDEX idx_created ON orders(created_at)")
    sql = "SELECT id, total_cents FROM orders WHERE substr(created_at, 1, 10) = '2026-09-15'"
    assert rules(check(db, sql, min_rows=MIN)) == ["full-table-scan"]
    db.execute("CREATE INDEX idx_day ON orders(substr(created_at, 1, 10))")
    assert check(db, sql, min_rows=MIN) == []


def test_like_on_a_binary_index_warns_and_nocase_fixes_it(db):
    db.execute("CREATE INDEX idx_email ON customers(email)")
    sql = "SELECT id FROM customers WHERE email LIKE 'garcia.%'"
    assert rules(check(db, sql, min_rows=MIN)) == ["index-scan"]
    db.execute("DROP INDEX idx_email")
    db.execute("CREATE INDEX idx_email ON customers(email COLLATE NOCASE)")
    assert check(db, sql, min_rows=MIN) == []


def test_order_by_without_an_index_is_a_scan_plus_a_sort(db):
    found = check(db, "SELECT id FROM orders ORDER BY created_at DESC LIMIT 20", min_rows=MIN)
    assert rules(found) == ["full-table-scan", "temp-btree"]


def test_small_tables_may_be_scanned(db):
    assert check(db, "SELECT name FROM countries WHERE name = 'India'", min_rows=MIN) == []


def test_aliases_resolve_to_their_tables(db):
    found = check(db, "SELECT o.id FROM orders AS o WHERE o.customer_id = 7", min_rows=MIN)
    assert rules(found) == ["full-table-scan"] and found[0].node == "SCAN o"


def test_self_join_on_an_unindexed_column_builds_an_automatic_index(db):
    found = check(db, "SELECT a.id FROM customers a JOIN customers b ON b.email = a.email", min_rows=MIN)
    assert "automatic-index" in rules(found)


def test_statements_that_write_are_rejected_and_do_nothing(db):
    with pytest.raises(ValueError):
        check(db, "DELETE FROM orders")
    assert db.execute("SELECT count(*) FROM orders").fetchone()[0] == 20_000


def test_plan_wording_is_what_the_gate_parses(db):
    """If a SQLite upgrade rewords the plan, this test fails before the gate silently stops working."""
    assert plan(db, "SELECT id FROM orders WHERE customer_id = 1") == ["SCAN orders"]
    db.execute("CREATE INDEX idx_customer ON orders(customer_id)")
    assert re.fullmatch(r"SEARCH orders USING INDEX idx_customer \(customer_id=\?\)",
                        plan(db, "SELECT id, status FROM orders WHERE customer_id = 1")[0])
    assert plan(db, "SELECT id FROM orders ORDER BY total_cents")[-1] == "USE TEMP B-TREE FOR ORDER BY"
python -m pytest -q test_plan_gate.py
..........                                                               [100%]
10 passed in 0.40s

The ten tests each pin one behavior from the earlier steps: an unindexed filter fails and an indexed one is clean; a function on a column fails until the expression is indexed; LIKE on a BINARY index warns until the index is NOCASE; a sort without an index is reported; small tables may be scanned; aliases resolve to their tables; a self-join builds an automatic index; a DELETE is rejected and changes nothing; and the last one, test_plan_wording_is_what_the_gate_parses, is the canary. If a future SQLite rewords SCAN orders or USE TEMP B-TREE FOR ORDER BY, that test fails in CI before the gate quietly stops finding problems.

Run it on a production-like index set

Now we point the gate at shop.db with the kind of indexes a production copy might have. This small module defines them.

# prod_indexes.py
"""prod_indexes.py - the indexes a production copy of shop.db would have."""
from common import reset

PROD_INDEXES = [
    "CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at)",
    "CREATE INDEX idx_orders_status_created ON orders(status, created_at)",
    "CREATE INDEX idx_orders_created ON orders(created_at)",
    "CREATE INDEX idx_customers_email ON customers(email COLLATE NOCASE)",
]


def use_prod_indexes(conn):
    """Start from a clean slate, then create the production index set."""
    reset(conn)
    for ddl in PROD_INDEXES:
        conn.execute(ddl)
    conn.commit()
# step9_gate_demo.py
"""Step 9: run the plan gate against a production-like set of indexes."""
import subprocess
import sys

from common import connect
from prod_indexes import PROD_INDEXES, use_prod_indexes
from plan_gate import check

conn = connect()
use_prod_indexes(conn)
print("indexes:", ", ".join(d.split()[2] for d in PROD_INDEXES))
CASES = [
    ("customer's orders", "SELECT id, status FROM orders WHERE customer_id = 4242"),
    ("orders on one day (range)", "SELECT id, total_cents FROM orders WHERE created_at >= '2026-09-15' AND created_at < '2026-09-16'"),
    ("orders on one day (date())", "SELECT id, total_cents FROM orders WHERE date(created_at) = '2026-09-15'"),
    ("big orders", "SELECT id, total_cents FROM orders WHERE total_cents > 100000"),
    ("every delivered order", "SELECT id, total_cents FROM orders WHERE status = 'delivered'"),
    ("emails ending in a domain", "SELECT id FROM customers WHERE email LIKE '%@example.org'"),
    ("revenue per country", "SELECT c.country, SUM(o.total_cents) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.country"),
    ("20 newest orders", "SELECT id FROM orders ORDER BY created_at DESC LIMIT 20"),
    ("a DELETE", "DELETE FROM orders WHERE status = 'cancelled'"),
]
for label, sql in CASES:
    try:
        findings = check(conn, sql)
    except ValueError as exc:
        print(f"REJECTED {label}: {exc}")
        continue
    verdict = "FAIL" if any(f.severity == "fail" for f in findings) else "WARN" if findings else "OK"
    print(f"{verdict:8} {label}")
    for f in findings:
        print(f"         {f.severity}: {f.rule} <- {f.node}")
conn.close()

print("--- the command line, with exit codes")
for sql in (CASES[0][1], CASES[3][1]):
    done = subprocess.run([sys.executable, "plan_gate.py", "shop.db", sql], capture_output=True, text=True)
    print(f"$ python plan_gate.py shop.db \"{sql}\"")
    print("   " + done.stdout.strip().replace("\n", "\n   "))
    print(f"   exit code {done.returncode}")
python step9_gate_demo.py
indexes: idx_orders_customer_created, idx_orders_status_created, idx_orders_created, idx_customers_email
OK       customer's orders
OK       orders on one day (range)
FAIL     orders on one day (date())
         fail: full-table-scan <- SCAN orders
FAIL     big orders
         fail: full-table-scan <- SCAN orders
OK       every delivered order
WARN     emails ending in a domain
         warn: index-scan <- SCAN customers USING COVERING INDEX idx_customers_email
FAIL     revenue per country
         fail: full-table-scan <- SCAN o
         warn: temp-btree <- USE TEMP B-TREE FOR GROUP BY
WARN     20 newest orders
         warn: index-scan <- SCAN orders USING COVERING INDEX idx_orders_created
REJECTED a DELETE: the plan gate only accepts SELECT statements

The first two OK lines are queries the indexes serve. The date() filter and the total_cents > 100000 filter fail with a full scan, the first because of the function on the column (Step 6a) and the second because nothing indexes total_cents. The LIKE '%@example.org' query warns that it scans an index. The revenue-per-country query fails on SCAN o (the alias of orders, which the gate resolved) and also warns about a temporary sort for GROUP BY; no index fixes a query that must read every order, so that one belongs on a replica or a summary table. The newest-20 query only warns, as designed, and the DELETE is rejected before any plan is requested.

Notice every delivered order: it is OK. Its plan is an index SEARCH, so the gate passes it, yet we saw in Step 7b that it touches 62% of the table. The gate reads the shape of the plan, not the size of the result. Step 10 closes that gap.

The rest of the output shows the command-line interface and its exit codes, which is what a CI job or an agent harness would check:

--- the command line, with exit codes
$ python plan_gate.py shop.db "SELECT id, status FROM orders WHERE customer_id = 4242"
   ok: no big scans, sorts or automatic indexes
   exit code 0
$ python plan_gate.py shop.db "SELECT id, total_cents FROM orders WHERE total_cents > 100000"
   FAIL full-table-scan: SCAN orders
        no index serves this query: check functions on columns, leading wildcards and missing indexes
   exit code 1

Step 10: Add a step budget for what a plan cannot see

A plan tells you how SQLite will search, not how much data it will touch. For that we use a second, dynamic layer: run the query under a budget and abort it when it runs too long or returns too much. Python’s sqlite3 module provides the hook. In the documentation’s words, set_progress_handler will “Register callable progress_handler to be invoked for every n instructions of the SQLite virtual machine,” and “Returning a non-zero value from the handler function will terminate the currently executing query and cause it to raise a DatabaseError exception.”

# budget.py
"""budget.py - stop a query that runs past a virtual-machine step budget or returns too many rows."""
import sqlite3


class BudgetExceeded(RuntimeError):
    def __init__(self, message, kind):
        super().__init__(message)
        self.kind = kind  # "steps" or "rows"


def run_with_budget(conn, sql, params=(), max_steps=1_000_000, max_rows=1_000):
    """Return (rows, steps) or raise BudgetExceeded. steps counts SQLite virtual-machine instructions."""
    steps = 0

    def tick():
        nonlocal steps
        steps += 1_000  # SQLite calls us once per 1,000 virtual-machine instructions
        return steps >= max_steps  # a true value makes SQLite abort the running statement

    conn.set_progress_handler(tick, 1_000)
    try:
        rows = conn.execute(sql, params).fetchmany(max_rows + 1)
    except sqlite3.OperationalError as exc:
        if "interrupted" in str(exc):
            raise BudgetExceeded(f"stopped after about {steps:,} steps", "steps") from exc
        raise
    finally:
        conn.set_progress_handler(None, 0)
    if len(rows) > max_rows:
        raise BudgetExceeded(f"more than {max_rows:,} rows", "rows")
    return rows, steps

The handler tick fires once per 1,000 virtual-machine instructions and adds 1,000 to a counter. Once the counter reaches max_steps it returns true, which makes SQLite abort the statement; we catch the resulting OperationalError and turn it into BudgetExceeded with kind steps. fetchmany(max_rows + 1) reads one more row than allowed, so the function can detect an oversized result without pulling a million rows into memory; that raises BudgetExceeded with kind rows. The finally block always removes the handler.

# step10_budget.py
"""Step 10: a step budget catches what a plan cannot see."""
import sqlite3
import time
from pathlib import Path

from budget import BudgetExceeded, run_with_budget
from common import connect
from prod_indexes import use_prod_indexes

setup = connect()
use_prod_indexes(setup)
setup.close()
conn = sqlite3.connect(Path("shop.db").resolve().as_uri() + "?mode=ro", uri=True)
conn.execute("PRAGMA query_only = ON")

CASES = [
    ("indexed lookup", "SELECT id, status FROM orders WHERE customer_id = 4242"),
    ("20 newest orders", "SELECT id FROM orders ORDER BY created_at DESC LIMIT 20"),
    ("date() filter, no usable index", "SELECT id, total_cents FROM orders WHERE date(created_at) = '2026-09-15'"),
    ("index SEARCH, but 62% of the table", "SELECT id, total_cents FROM orders WHERE status = 'delivered'"),
]
for label, sql in CASES:
    start = time.perf_counter()
    try:
        rows, steps = run_with_budget(conn, sql)
        outcome = f"{len(rows)} rows, " + (f"about {steps:,} steps" if steps else "under 1,000 steps")
    except BudgetExceeded as exc:
        outcome = f"BudgetExceeded: {exc}"
    print(f"{label:36} {outcome}  ({(time.perf_counter() - start) * 1000:.0f} ms)")

print("--- the connection is read-only")
try:
    conn.execute("DELETE FROM orders")
except sqlite3.OperationalError as exc:
    print("   ", exc)
python step10_budget.py
indexed lookup                       27 rows, under 1,000 steps  (0 ms)
20 newest orders                     20 rows, under 1,000 steps  (0 ms)
date() filter, no usable index       BudgetExceeded: stopped after about 1,000,000 steps  (23 ms)
index SEARCH, but 62% of the table   BudgetExceeded: more than 1,000 rows  (1 ms)
--- the connection is read-only
    attempt to write a readonly database

The indexed lookup and the newest-20 query finish in under 1,000 steps, so the counter never ticks (it reports 0). The date() filter has no usable index, so it exhausts the one-million-step budget and is stopped after 23 ms, instead of grinding on for the full scan. The last case is the one the gate passed: an index SEARCH returning 62% of the table is stopped by the row cap after one millisecond. The final lines show that the connection really is read-only: the DELETE fails with attempt to write a readonly database.

Common mistake

Do not pick the budget numbers from this article. One million steps and 1,000 rows suit this lab. Measure your own representative queries (the step count printed for a healthy query is a good starting point) and set the limits with headroom above them, or you will block legitimate queries.

Step 11: Try it on SQL written by AI models

Now the realistic test. I asked two small local models to write SQLite for eight plain-English questions about the shop database, giving each the table definitions and no other hint. The generator below is optional; it needs Ollama running. It uses temperature 0 and a fixed seed so each model gives a stable answer, and the answers are saved to ai_queries.json.

# gen_ai_queries.py
"""gen_ai_queries.py - ask local models to write SQL for plain-English questions; freeze the answers."""
import json
import re
import urllib.request

from build_db import SCHEMA

QUESTIONS = [
    "How many orders has customer 4242 placed?",
    "Show the 10 most recent orders.",
    "List the refunded orders placed in September 2026.",
    "Find all orders placed on 15 September 2026.",
    "Which customers have an email address at example.org?",
    "Which customers have an email address that starts with garcia?",
    "What is the total order value for each country?",
    "How many delivered orders were worth more than 1,000 dollars?",
]
MODELS = ["qwen2.5:1.5b", "qwen3.5:4b"]
PROMPT = "You write SQLite queries.\nSchema:\n{schema}\nAnswer with one SQLite SELECT statement and nothing else.\nQuestion: {question}"


def ask(model, question):
    body = {
        "model": model,
        "stream": False,
        "options": {"temperature": 0, "seed": 0},
        "messages": [{"role": "user", "content": PROMPT.format(schema=SCHEMA.strip(), question=question)}],
    }
    if model.startswith("qwen3"):
        body["think"] = False  # skip the hidden reasoning pass
    request = urllib.request.Request(
        "http://127.0.0.1:11434/api/chat", json.dumps(body).encode(), {"Content-Type": "application/json"}
    )
    with urllib.request.urlopen(request, timeout=600) as response:
        text = json.load(response)["message"]["content"]
    fenced = re.search(r"```(?:sql)?\s*(.*?)```", text, re.S | re.I)
    return " ".join((fenced.group(1) if fenced else text).split())


if __name__ == "__main__":
    answers = [{"model": m, "question": q, "sql": ask(m, q)} for m in MODELS for q in QUESTIONS]
    with open("ai_queries.json", "w", encoding="utf-8", newline="\n") as fh:
        json.dump(answers, fh, indent=1)
    for a in answers:
        print(f"[{a['model']}] {a['question']}\n    {a['sql']}")

Here are the frozen answers I got. If you skip the generator, save this as ai_queries.json and everything after it reproduces exactly.

[
 {
  "model": "qwen2.5:1.5b",
  "question": "How many orders has customer 4242 placed?",
  "sql": "SELECT COUNT(id) FROM orders WHERE customer_id = 4242;"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "Show the 10 most recent orders.",
  "sql": "SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "List the refunded orders placed in September 2026.",
  "sql": "SELECT o.id, o.status, o.total_cents, o.created_at FROM orders o JOIN customers c ON o.customer_id = c.id WHERE strftime('%Y-%m', o.created_at) = '2026-09' AND o.status = 'refunded';"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "Find all orders placed on 15 September 2026.",
  "sql": "SELECT * FROM orders WHERE created_at = '2026-09-15'"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "Which customers have an email address at example.org?",
  "sql": "SELECT email FROM customers WHERE email LIKE '%example.org%'"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "Which customers have an email address that starts with garcia?",
  "sql": "SELECT email FROM customers WHERE email LIKE 'garcia%'"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "What is the total order value for each country?",
  "sql": "SELECT country, SUM(total_cents) FROM orders JOIN customers ON orders.customer_id = customers.id GROUP BY country;"
 },
 {
  "model": "qwen2.5:1.5b",
  "question": "How many delivered orders were worth more than 1,000 dollars?",
  "sql": "SELECT COUNT(id) FROM orders WHERE status = 'delivered' AND total_cents > 1000;"
 },
 {
  "model": "qwen3.5:4b",
  "question": "How many orders has customer 4242 placed?",
  "sql": "SELECT COUNT(*) FROM orders WHERE customer_id = 4242;"
 },
 {
  "model": "qwen3.5:4b",
  "question": "Show the 10 most recent orders.",
  "sql": "SELECT o.id, c.email, c.country, o.status, o.total_cents, o.created_at FROM orders o JOIN customers c ON o.customer_id = c.id ORDER BY o.created_at DESC LIMIT 10;"
 },
 {
  "model": "qwen3.5:4b",
  "question": "List the refunded orders placed in September 2026.",
  "sql": "SELECT o.id AS order_id, o.total_cents, c.email, c.country, c.tier FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'refunded' AND strftime('%Y-%m', o.created_at) = '2026-09';"
 },
 {
  "model": "qwen3.5:4b",
  "question": "Find all orders placed on 15 September 2026.",
  "sql": "SELECT * FROM orders WHERE date(created_at) = '2026-09-15';"
 },
 {
  "model": "qwen3.5:4b",
  "question": "Which customers have an email address at example.org?",
  "sql": "SELECT DISTINCT c.email FROM customers AS c;"
 },
 {
  "model": "qwen3.5:4b",
  "question": "Which customers have an email address that starts with garcia?",
  "sql": "SELECT * FROM customers WHERE email LIKE 'garcia%';"
 },
 {
  "model": "qwen3.5:4b",
  "question": "What is the total order value for each country?",
  "sql": "SELECT c.country, SUM(o.total_cents) AS total_order_value FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.country;"
 },
 {
  "model": "qwen3.5:4b",
  "question": "How many delivered orders were worth more than 1,000 dollars?",
  "sql": "SELECT COUNT(*) FROM orders WHERE status = 'delivered' AND total_cents > 100000;"
 }
]

Read a few of them before running anything. Both models wrote valid SQL for every question. But look at question 4 for qwen2.5:1.5b: created_at = '2026-09-15' compares a timestamp column, which includes the time of day, with a bare date. And for question 8 it wrote total_cents > 1000 for “more than 1,000 dollars,” forgetting that the column is in cents. Meanwhile qwen3.5:4b answered question 5 with SELECT DISTINCT c.email FROM customers AS c;, which has no filter at all.

The next script runs every query through the gate and the budget, and also against an answer key, one trusted query per question that I wrote by hand, so we can tell whether each answer is right as well as cheap.

# step11_ai_gate.py
"""Step 11: run frozen, model-written SQL through the plan gate, the step budget and an answer key."""
import json
import sqlite3
from pathlib import Path

from budget import BudgetExceeded, run_with_budget
from common import connect
from prod_indexes import use_prod_indexes
from plan_gate import check

ANSWER_KEY = [  # one trusted query per question, in the same order as gen_ai_queries.QUESTIONS
    "SELECT count(*) FROM orders WHERE customer_id = 4242",
    "SELECT id FROM orders ORDER BY created_at DESC LIMIT 10",
    "SELECT id FROM orders WHERE status = 'refunded' AND created_at >= '2026-09-01' AND created_at < '2026-10-01'",
    "SELECT id FROM orders WHERE created_at >= '2026-09-15' AND created_at < '2026-09-16'",
    "SELECT id FROM customers WHERE email LIKE '%@example.org'",
    "SELECT id FROM customers WHERE email LIKE 'garcia%'",
    "SELECT c.country, SUM(o.total_cents) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.country",
    "SELECT count(*) FROM orders WHERE status = 'delivered' AND total_cents > 100000",
]

setup = connect()
use_prod_indexes(setup)
setup.close()
conn = sqlite3.connect(Path("shop.db").resolve().as_uri() + "?mode=ro", uri=True)
conn.execute("PRAGMA query_only = ON")


def shape(rows):
    """A single number is compared by value; anything else by row count."""
    return rows[0][0] if len(rows) == 1 and len(rows[0]) == 1 else len(rows)


tally = {}
for i, item in enumerate(json.load(open("ai_queries.json", encoding="utf-8"))):
    n = i % len(ANSWER_KEY)
    try:
        findings = check(conn, item["sql"])
        gate = "FAIL" if any(f.severity == "fail" for f in findings) else "WARN" if findings else "OK"
    except (sqlite3.Error, ValueError):
        gate = "ERROR"
    try:
        run_with_budget(conn, item["sql"])
        budget = "ok"
    except BudgetExceeded as exc:
        budget = f"STOP:{exc.kind}"
    expected = shape(conn.execute(ANSWER_KEY[n]).fetchall())
    actual = shape(conn.execute(item["sql"]).fetchall())
    right = actual == expected
    print(f"{item['model']:13} Q{n + 1}  gate {gate:5}  budget {budget:10}  " + ("answer ok" if right else f"WRONG ANSWER ({actual} vs {expected})"))
    row = tally.setdefault(item["model"], {"gate": [], "stopped": 0, "wrong": 0, "slipped": []})
    row["gate"].append(gate)
    row["stopped"] += budget != "ok"
    row["wrong"] += not right
    if not right and gate in ("OK", "WARN") and budget == "ok":
        row["slipped"].append(f"Q{n + 1}")

print("--- summary")
for model, row in tally.items():
    gates = {g: row["gate"].count(g) for g in ("OK", "WARN", "FAIL", "ERROR")}
    print(f"{model:13} gate {gates}  budget stops {row['stopped']}  wrong answers {row['wrong']}  wrong but past both layers: {row['slipped'] or 'none'}")
python step11_ai_gate.py
qwen2.5:1.5b  Q1  gate OK     budget ok          answer ok
qwen2.5:1.5b  Q2  gate WARN   budget ok          answer ok
qwen2.5:1.5b  Q3  gate OK     budget ok          answer ok
qwen2.5:1.5b  Q4  gate OK     budget ok          WRONG ANSWER (0 vs 770)
qwen2.5:1.5b  Q5  gate WARN   budget STOP:rows   answer ok
qwen2.5:1.5b  Q6  gate OK     budget STOP:rows   answer ok
qwen2.5:1.5b  Q7  gate FAIL   budget STOP:steps  answer ok
qwen2.5:1.5b  Q8  gate OK     budget STOP:steps  WRONG ANSWER (275897 vs 14)
qwen3.5:4b    Q1  gate OK     budget ok          answer ok
qwen3.5:4b    Q2  gate WARN   budget ok          answer ok
qwen3.5:4b    Q3  gate OK     budget ok          answer ok
qwen3.5:4b    Q4  gate FAIL   budget STOP:steps  answer ok
qwen3.5:4b    Q5  gate WARN   budget STOP:rows   WRONG ANSWER (20000 vs 6622)
qwen3.5:4b    Q6  gate OK     budget STOP:rows   answer ok
qwen3.5:4b    Q7  gate FAIL   budget STOP:steps  answer ok
qwen3.5:4b    Q8  gate OK     budget STOP:steps  answer ok
--- summary
qwen2.5:1.5b  gate {'OK': 5, 'WARN': 2, 'FAIL': 1, 'ERROR': 0}  budget stops 4  wrong answers 2  wrong but past both layers: ['Q4']
qwen3.5:4b    gate {'OK': 4, 'WARN': 2, 'FAIL': 2, 'ERROR': 0}  budget stops 5  wrong answers 1  wrong but past both layers: none

What each layer caught

The gate failed the revenue-per-country query from both models (a real full scan that no index can fix) and, for qwen3.5:4b, the date(created_at) filter in question 4. It warned on the newest-orders and email queries, as designed.

The budget caught what the gate let through. Both models’ question 8 passes the gate, because status = 'delivered' is a clean index SEARCH, yet the filter on total_cents forces SQLite to visit about 310,000 table rows, so the step budget stops it. Questions 5 and 6 are stopped by the row cap, because they return thousands of rows. For most of those runs that is the correct answer, so whether it is acceptable is a policy decision for you, not a bug in the query.

Neither layer caught the bare-date comparison in qwen2.5:1.5b’s question 4. Checking that query separately, its plan is SEARCH orders USING INDEX idx_orders_created (created_at=?), it runs in about 0.03 ms, and it returns 0 rows instead of 770. That is the most important result in this tutorial: a plan gate measures cost, not correctness. A query can be fast and wrong. The cents-versus-dollars slip in question 8 was only stopped incidentally, because the wrong threshold made the query expensive; the answer key shows it is wrong too (275,897 instead of 14). The DISTINCT c.email query was flagged by both layers and is also wrong (20,000 rows instead of 6,622), but the only check that proves it wrong is the answer key.

Treat these numbers as an illustration, not a benchmark. They come from two small local models, eight questions and a single deterministic run each. A different model, prompt or Ollama version would produce different mistakes. What generalizes is the layering: use the gate to stop bad plans, the budget to stop expensive ones, and tests or review to check meaning. If you want to build that third layer for generated code, our guide to reviewing AI-generated Python code and the guardrails for AI-generated Terraform tutorial apply the same gate-before-run idea to other kinds of output.

Common mistakes and gotchas

Judging a plan on an empty development database

Plans depend on table sizes and statistics. A table scan of 200 rows is harmless, so a bad plan can hide for months. Step 8 showed a plan flip from a 23 ms scan to a 0.2 ms skip-scan without any schema change. Check plans against production-sized data, and make sure statistics match (run ANALYZE or PRAGMA optimize, or load saved statistics in CI).

Believing SEARCH means fast

SEARCH means an index is used. Step 7b was 20% slower than a scan because the query returned 62% of the table, and Step 11’s question 8 passed the gate while visiting hundreds of thousands of rows. Look at how many rows the query will touch, not only at the first word of the plan.

Adding an index for every query

Five indexes made inserts more than seven times slower in Step 7a and a full index was 23 times bigger than a partial one in 7c. Add an index when a measured plan and a real query justify it, and prefer one multi-column index shaped like your queries over several single-column ones.

Parsing EXPLAIN QUERY PLAN in production code

The SQLite authors say not to. If you build a gate anyway, pin the version, keep a canary test on the wording, treat anything unrecognized as a warning, and rerun the tests whenever Python or SQLite is upgraded.

Forgetting that LIKE is special

A prefix LIKE on a default index silently scans (Step 6b). Declare the index COLLATE NOCASE, use GLOB, or write the range yourself. Remember that a leading wildcard can never use an index, and that SQLite’s case-insensitivity only covers ASCII letters.

Verify the whole thing end to end

Run every script in order from a clean folder. The first command rebuilds the database; the last one runs the gate’s tests. On PowerShell use the first form and on macOS or Linux the second.

foreach ($f in "build_db","step2_first_plan","step3_composite","step4_covering","step5_order_by","step6_ignored","step7_costs","step8_analyze") { python "$f.py" }
python -m pytest -q test_plan_gate.py
foreach ($f in "step9_gate_demo","step10_budget","step11_ai_gate") { python "$f.py" }
for f in build_db step2_first_plan step3_composite step4_covering step5_order_by step6_ignored step7_costs step8_analyze; do python $f.py; done
python -m pytest -q test_plan_gate.py
for f in step9_gate_demo step10_budget step11_ai_gate; do python $f.py; done

You are done when the row counts are 20,000 and 500,000, every plan line matches what you saw above, pytest reports 10 passed, the Step 9 table shows the same OK, WARN, FAIL and REJECTED verdicts, and Step 11 ends with a summary line per model. Expect the timings to differ by machine and by run. The ratios (a scan is hundreds of times slower than an index search, five indexes slow inserts several times over) should hold.

Where to go next

You can now read an EXPLAIN QUERY PLAN, choose index column order deliberately, recognize why a plan ignores an index, and apply those skills automatically with a gate and a budget. To keep learning:

  • Read SQLite’s query planning guide and optimizer overview end to end. The first is gentle and illustrated, the second is the reference for every rule used here.
  • Load statistics from a representative database into your CI database, as the ANALYZE page describes, so the gate sees production plans.
  • Pair the gate with parameterized queries. A gate checks performance; it does not stop injection or prove the query means what the user asked.
  • Measure real query latency in production with OpenTelemetry slow-query metrics, which is the runtime counterpart to this tutorial’s static check.
  • SQLite appears across our other Python tutorials, including a write-ahead log built from scratch and hybrid search with SQLite FTS5. Run the plan gate over their queries as an exercise.
  • Most SQL databases have their own EXPLAIN command with different output. The habit transfers even though the wording does not.

Sources and documentation

Tags:

AI Coding AgentspytestPythonQuery OptimizationSQLSQLite

Share

No Comment! Be the first one.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts