How to catch a missing index in a test, when your test table has 20 rows.

Luis David Senra — Sun, 27 Sep 2026 15:20:04 +0000

Here is a bug that no test suite catches.

Someone adds a lookup by email. It works. Six months later the table has two
million rows, that lookup has no index, and the endpoint takes four seconds. The
tests were green the whole time, because the test database had twenty rows and
four seconds of two million rows is four milliseconds of twenty.

The obvious fix is to assert on the query plan. That does not work either, and
the reason it does not work is more interesting than the fix.

What you would try first

Ask PostgreSQL for the plan and fail the test if it contains a Seq Scan. Let us
try it on a table with an indexed column and an unindexed one:

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

Now the two plans. One of these queries has a perfect index available. The other
has nothing.

EXPLAIN (COSTS off) SELECT * FROM customers WHERE email = 'user5@example.com';

                  QUERY PLAN
-----------------------------------------------
 Seq Scan on customers
   Filter: (email = 'user5@example.com'::text)

EXPLAIN (COSTS off) SELECT * FROM customers WHERE city = 'city5';

            QUERY PLAN
----------------------------------
 Seq Scan on customers
   Filter: (city = 'city5'::text)

They are identical.

That is not a bug in PostgreSQL, it is PostgreSQL being right. Reading twenty rows
sequentially costs less than descending a B-tree and then fetching those same rows
from the heap. The planner picks the cheaper plan, and on twenty rows the cheaper
plan is always the scan.

So the presence of a Seq Scan carries no information about whether an index
is missing. A check built on it fails every test that touches a small table, which
means you delete the check within a day.

The obvious rescue also fails

The usual next idea: only complain when the table is big. Read reltuples from
pg_class, and fail only above some threshold.

Now run that against your test suite, where every table has a few dozen seeded
rows. The check never fires. Ever. You have built something that passes
unconditionally, which is strictly worse than having nothing, because now you
believe you are covered.

Both directions fail for the same underlying reason: you are asking a question
whose answer depends on how much data you have, and then asking it in an
environment deliberately built to have almost none.

A different question

Stop asking "did it scan the table?" Ask instead:

Could it have used an index, if it had wanted to?

That question has a data-independent answer, and PostgreSQL will answer it for
you. enable_seqscan is a planner setting that makes sequential scans absurdly
expensive. It does not forbid them -- the planner will still use one if there is no
alternative -- it just makes any alternative look better.

So: penalise sequential scans, ask for the plan again, and see what happens.

SET enable_seqscan = off;

EXPLAIN (COSTS off) SELECT * FROM customers WHERE email = 'user5@example.com';

                    QUERY PLAN
---------------------------------------------------
 Index Scan using customers_email_idx on customers
   Index Cond: (email = 'user5@example.com'::text)

EXPLAIN (COSTS off) SELECT * FROM customers WHERE city = 'city5';

            QUERY PLAN
----------------------------------
 Seq Scan on customers
   Filter: (city = 'city5'::text)

There it is. The indexed column switches to an index scan the moment the planner
has a reason to prefer one. The unindexed column cannot switch, because there is
nothing to switch to.

A filtered sequential scan that survives enable_seqscan = off is one that no
index can serve. That conclusion holds on twenty rows, on zero rows, and on two
million, because it is a fact about your schema rather than about your data.

Reproduce it in two minutes

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 paste the CREATE TABLE above and both pairs of EXPLAINs. It is worth
seeing the identical plans with your own eyes, because that is the part nobody
expects.

Wiring it into a test

The rest is plumbing. Listen for every statement SQLAlchemy sends, take a plan for
the SELECTs with sequential scans penalised, and collect the ones that survive.

# conftest.py
import json
from contextlib import contextmanager

import pytest
from sqlalchemy import event
from sqlalchemy.engine import Engine

SAVEPOINT = "index_check"

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

@pytest.fixture
def no_seq_scan():
    @contextmanager
    def check():
        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"SAVEPOINT {SAVEPOINT}")
                raw.execute("SET enable_seqscan = off")
                raw.execute("EXPLAIN (FORMAT JSON) " + statement, parameters or None)
                row = raw.fetchone()
                plan = row[0] if row else None
                if isinstance(plan, str):
                    plan = json.loads(plan)
                offenders.extend(seq_scans(plan))
            except Exception:
                pass
            finally:
                try:
                    raw.execute(f"ROLLBACK TO SAVEPOINT {SAVEPOINT}")
                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

Used like this:

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
    )

On that same twenty-row table:

FAILED test_index.py::test_search_by_city_uses_an_index
AssertionError: Seq Scan on customers -- Filter: ((city)::text = 'city5'::text)

while the email version passes. Twenty rows, and the check tells them apart.

Three details that matter

The savepoint does two jobs. It scopes the enable_seqscan change, since SET
is transactional and a rollback undoes it -- so the setting cannot leak into the
rest of your test. And it absorbs a failed EXPLAIN: without it, one unexplainable
statement aborts the transaction your test is running in, and every assertion
after that point fails for reasons that have nothing to do with the test.

The EXPLAIN goes out on a raw DBAPI cursor, not through the engine. Send it
through SQLAlchemy and it fires before_cursor_execute again, and the probe
recurses into itself. Going under the ORM also keeps the EXPLAIN out of any
query counting you are doing in the same test.

An unfiltered Seq Scan is never a problem. SELECT * FROM customers reads the
whole table because you asked it to, and no index would improve that. Only scans
that carry a Filter are interesting, which is why the code above checks for one.

What this does not tell you

It is a schema check, not performance advice, and it is worth being clear about
the difference.

It does not know whether the index is worth its write cost. It does not know the
right column order for a composite index, which depends on all your queries and
not on one plan node. It says nothing about selectivity: an index on a boolean
column with two distinct values will be found by this check and still be useless.
And a filter over a function call, lower(email) = $1, needs an expression index
rather than a plain one -- this check will flag it correctly but the obvious fix
will not help.

What it does do is catch the specific, common, embarrassing case: a WHERE clause
on a column that nobody indexed, found in CI, months before anyone notices in
production.

I packaged this, along with query budgets and N+1 detection, as
pytest-querycount -- so disclosure, it is mine. The marker version:

@pytest.mark.no_seq_scan
def test_customer_search(db):
    assert len(find_by_city(db, "city5")) == 1

which fails with the table, the filter, the line of your code that emitted the
query, and a suggested CREATE INDEX built from the filtered columns.

But the fixture above is the whole idea in forty lines, and it is genuinely enough
if you only need it in a couple of places. The trick is the part worth taking away.

DEV Community: Luis David Senra