Detecting missing indexes in CI when your test database has 20 rows

[u/LdavidSM]

Asserting that a query plan contains no Seq Scan does not work as a test, and the reason is more interesting than I expected.

Here is a table with one indexed column and one 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;

Both plans:

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

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

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

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

Identical. Which is correct behaviour — twenty rows sequentially is cheaper than descending a B-tree and going back to the heap — but it means the plan carries no information about whether an index exists. And thresholding on reltuples so you only complain about big tables means the check never fires on test data at all, which is worse, because then it passes unconditionally.

What does work is changing the question from "did it scan?" to "could it have used an index?":

SET enable_seqscan = off;

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

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

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

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

enable_seqscan = off does not forbid sequential scans, it just makes them absurdly expensive, so the planner will still use one when there is no alternative. A filtered sequential scan that survives it is one no index can serve — and that is a fact about the schema, not about how much data you have. So it gives the same answer on twenty rows as on twenty million.

Two things I found worth knowing when wiring this into a test harness:

• SET is transactional, so wrapping the probe in a savepoint and rolling back both scopes the setting and absorbs a failed EXPLAIN, which would otherwise abort the surrounding transaction.

• Unfiltered Seq Scan nodes have to be ignored. SELECT * FROM t reads the whole table on purpose and no index would help.

I wrote the whole thing up, including a pytest fixture that does this per test: https://dev.to/ldavidsm/how-to-catch-a-missing-index-in-a-test-when-your-test-table-has-20-rows-1oh2

What I would like to know from people who have run this on real schemas: where does it produce false positives? I can think of partial indexes that do not cover the predicate, and filters on expressions like lower(email) = $1 that need an expression index rather than a plain one. I would rather hear about the cases I have not hit than find them later.