Catching Missing Indexes in Tests When Your Table Has Many Rows

The Problem

A common bug slips past test suites entirely. Developers add a lookup by email, it works fine during development, and six months later the table grows to two million rows. The lookup now has no index, and the endpoint becomes painfully slow - taking four seconds versus four milliseconds on the tiny test dataset. The tests stay green because the test database only contains twenty rows, making the slow query indistinguishable from fast.

The natural fix is to assert on the query plan. However, this approach fails for a deeper reason: asking whether a sequential scan occurred carries no information about whether an index is actually missing. As the article demonstrates, even with enable_seqscan turned off, a filtered sequential scan can persist when the planner has no alternative.

Why the Naive Approach Fails

When you set enable_seqscan = off, PostgreSQL will avoid sequential scans unless absolutely forced. In such cases, a filtered sequential scan may remain - especially for queries with filters on non-indexed columns. This creates a false sense of security: the check appears to catch the problem, yet it never triggers on small datasets.

Attempting to mitigate this by only failing on larger tables (by reading reltuples from pg_class) also backfires. In a typical test suite where every table has only a few dozen seeded rows, the threshold is never reached. The resulting check passes unconditionally, meaning the test suite believes itself protected while remaining vulnerable.

Both approaches share the same root cause: you're asking a question whose answer depends on data volume, and then posing it in an environment designed to have almost none.

The Correct Question

Instead of asking "Did it scan the table?" you should ask "Could it have used an index, if it had wanted to?" This reframes the problem into a data-independent question that PostgreSQL answers directly. By penalizing sequential scans via enable_seqscan, you force the planner to choose between an index scan and a sequential scan. If an index exists and is suitable, the optimizer will prefer it - and the filtered sequential scan will disappear once enable_seqscan is off.

Implementation: Detecting Unavoidable Sequential Scans

The solution involves hooking into SQLAlchemy's execution events to intercept every SELECT statement, capture its execution plan, and record any filtered sequential scans that survive the enable_seqscan penalty.

Core Helper: seq_scans()

This function walks the AST of an EXPLAIN (FORMAT JSON) plan tree and collects relations where a Seq Scan (or Parallel Seq Scan) occurs alongside a filter condition:

def seq_scans(plan):
    """Every filtered sequential scan in an EXPLAIN (FORMAT JSON) tree."""
    found, stack = [], [plan]
    while stack:
        node = stack.pop()
        if isinstance(node, list):
            stack.extend(node)
        elif isinstance(node, dict):
            if "Plan" in node:
                stack.append(node["Plan"])
            continue
            if node.get("Node Type") in ("Seq Scan", "Parallel Seq Scan") and node.get("Filter"):
                found.append((node.get("Relation Name"), node["Filter"]))
            stack.extend(node.get("Plans") or [])
    return found

Fixture: no_seq_scan

This context manager sets up a temporary savepoint around each query execution, temporarily disables enable_seqscan, runs EXPLAIN (FORMAT JSON), and records any surviving sequential scans:

@pytest.fixture
def no_seq_scan():
    @contextmanager
    def check(offenders):
        saved_offenders = []
        def probe(conn, cursor, statement, parameters, context, executemany):
            if conn.dialect.name != "postgresql":
                return
            if not statement.lstrip().lower().startswith("select"):
                return
            raw = conn.connection.cursor()
            try:
                raw.execute(f"SETENABLE SEQSCAN OFF")
                raw.execute("EXPLAIN (FORMAT JSON)" + statement, parameters or None)
                plan = raw.fetchone()[0]
                if isinstance(plan, str):
                    plan = json.loads(plan)
                    offenders.append(seq_scans(plan))
            except Exception:
                pass
            finally:
                try:
                    raw.execute(f"ROLLBACK TO SAVEOFF {saved_offenders}")
                except Exception:
                    pass
                raw.close()
        event.listen(Engine, "before_cursor_execute", probe)
        try:
            yield offenders
        finally:
            event.remove(Engine, "before_cursor_execute", probe)
    return check

Usage Example

The helper integrates cleanly into existing test logic. For instance, a test searching by city can verify that no sequential scans survive after enabling enable_seqscan:

def test_search_by_city_uses_an_index(db, no_seq_scan):
    with no_seq_scan() as offenders:
        find_by_city(db, "city5")
    assert not offenders, (
        "\n".join(
            f"Seq Scan on {table} -- Filter: {filt}" 
            for table, filt in offenders
        )
    )

Running the Detection Locally

The behavior described can be reproduced in minutes using Docker:

docker run -d --name pg -e POSTGRES_PASSWORD=x -e POSTGRES_DB=x -p 5432:5432 postgres:17-alpine
docker exec -it pg psql -U postgres -d x

Then create the schema and insert sample data:

CREATE TABLE customers (
    id serial PRIMARY KEY,
    email text,
    city text
);
CREATE INDEX customers_email_idx ON customers (email);
INSERT INTO customers (email, city) SELECT 'user' || g || '@example.com', 'city' || g FROM generate_series(1, 20) g;
ANALYZE customers;

Running the relevant EXPLAIN statements shows identical plans for both the email-indexed and city-unindexed queries - confirming that the planner chooses the sequential scan purely based on data volume, not index availability.

Limitations and Caveats

This technique is a schema-level check, not a performance recommendation. It does not account for whether an index is worthwhile given write overhead, nor does it determine optimal column ordering for composite indexes. Additionally, it flags certain edge cases incorrectly:

• Boolean columns with only two distinct values may trigger the check but still lack usefulness.
• Filters involving function calls (e.g., lower(email) = $1) require expression indexes, which this check will detect but won't automatically fix.

Despite these boundaries, the pattern effectively catches the most common, embarrassing scenario: a WHERE clause on an unindexed column that causes severe performance degradation in production but remains invisible to traditional test assertions. The tool is best used as a safety net in critical search paths, complementing broader query budgeting and N+1 detection strategies.