Build1 publisher2 min readPublished
Penalising sequential scans in tests exposes the missing index a 20-row table hides
PostgreSQL scans a 20-row test table sequentially even when a perfect index exists, so a test that fails on Seq Scan cannot find a missing index. Re-running EXPLAIN with enable_seqscan off catches it at any row count, since only a filter no index can serve keeps its scan.
The Engineer · Build desk
What happened
- A test that fails on any Seq Scan in the plan fails every test touching a small table, and the post's author expects such a check to be deleted within a day.
- Failing only when pg_class reltuples exceeds a threshold breaks the opposite way, never firing in a suite whose tables hold a few dozen seeded rows.
- With enable_seqscan set to off, the indexed email lookup switched to an index scan on customers_email_idx while the unindexed city lookup kept its sequential scan.
- The post's pytest fixture listens to every statement SQLAlchemy sends, plans each SELECT with sequential scans penalised, and collects the scans that survive.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- decision Teams asserting on query plans in CI have to test whether an index could serve each filter, because any gate tied to row count either fires on everything or stays silent on seeded fixtures.
- capability Missing-index checks can run against seeded fixtures of any size, empty tables included, so catching them no longer requires a production-scale copy of the data.
- constraint A passing check only shows that an index exists for the filter; whether the planner picks it at two million rows still has to be tested with data at that scale.
- cost The price is one extra EXPLAIN for every SELECT the suite runs, planned against tables that stay a few dozen rows.
PostgreSQL is right to scan. According to the post, reading 20 rows in order costs less than descending a B-tree and then fetching the same rows from the heap, so the planner takes the scan [4]. In the post's customers table, email has an index and city has none [2]. Both lookups print the same plan: Seq Scan on customers, followed by a Filter line [3]. The post produced those plans on the postgres:17-alpine image, after running ANALYZE on the seeded table [2][11]. From that output, a test cannot tell the indexed column from the unindexed one.
The post opens on a four-second endpoint at two million rows and says the same lookup costs four milliseconds at 20 [1]. If scan time scales linearly, the lookup costs 2 microseconds a row, or 40 microseconds for 20 rows [13]. The post's figure is off by a factor of 100, in the direction that helps its case [13].
"You have built something that passes unconditionally, which is strictly worse than having nothing, because now you believe you are covered," the author wrote of the row-count gate [6]. The author's diagnosis covers both failed attempts: "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." [12]
The replacement check works because of how enable_seqscan is defined. The setting makes sequential scans look very expensive to the planner without forbidding them [7]. Any usable alternative then wins on cost, and a scan still appears when nothing else can serve the query [7]. A filtered scan that survives the penalty has no index behind it [9]. The author says that result holds on zero rows, 20 rows or two million, because it is a property of the schema [9].
This is good engineering. The original check asked a question about data volume in an environment built to have almost no data [12]. The new one asks the planner a question it can answer from the schema, using a setting PostgreSQL already has [7][9].
What to watch
- Whether the fixture offers a way to exempt small lookup tables filtered without an index on purpose, since the survives-the-penalty rule flags them like any other gap.
- Whether the identical 20-row plans shown on the postgres:17-alpine image reproduce on other PostgreSQL versions and under non-default planner cost settings.