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;
Enter fullscreen mode Exit fullscreen mode
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 = '[email protected]';
QUERY PLAN
-----------------------------------------------
Seq Scan on customers
Filter: (email = '[email protected]'::text)
EXPLAIN (COSTS off) SELECT * FROM customers WHERE city = 'city5';
QUERY PLAN
----------------------------------
Seq Scan on customers
Filter: (city = 'city5'::text)
Enter fullscreen mode Exit fullscreen mode
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 = '[email protected]';
QUERY PLAN
---------------------------------------------------
Index Scan using customers_email_idx on customers
Index Cond: (email = '[email protected]'::text)
EXPLAIN (COSTS off) SELECT * FROM customers WHERE city = 'city5';
QUERY PLAN
----------------------------------
Seq Scan on customers
Filter: (city = 'city5'::text)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
)
Enter fullscreen mode Exit fullscreen mode
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)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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.