To catch a missing index, test that the index exists in the database schema; do not infer its existence from whether a query uses it on a 20-row table. A tiny table may be faster to scan, so the planner can correctly ignore an index that is present. Test index existence and query-plan behavior separately.
Why 20 rows cannot prove an index is missing
A query planner chooses an access path based on estimated cost, not simply on whether an index exists. For a very small table, reading every row can be cheaper than looking up rows through an index. PostgreSQL warns that very small test datasets can make index experiments misleading: if a relation fits on one disk page, a sequential scan may be the cheaper choice. See PostgreSQL’s guide to examining index usage.
As an Amazon Associate I earn from qualifying purchases.
There is no universal row-count threshold at which a planner should use an index. The decision depends on the database engine, query, data distribution, statistics, and cost settings. A sequential scan on 20 rows is therefore not evidence by itself that an index is absent.
Recommended Free Tools
Separate index existence from planner behavior
| What you want to verify | What to test | What the result tells you |
|---|---|---|
| Was the index created? | Inspect the migration result or database catalog in a schema-level test. | Whether the expected index is present, regardless of which plan a query chooses. |
| Does the query use a suitable access path? | Inspect the plan for the target query against representative data. | Which plan the specific engine chooses under those data and statistics. |
| Does the query return the right answer? | Keep a functional test that checks returned rows. | Whether the query is correct. An index should help retrieve the answer, not change it, as SQLite explains in its query-planning documentation. |
These checks answer different questions. A catalog assertion directly catches a missing index; a plan check evaluates optimizer behavior for a particular test setup.
#1 Best Overall
Design a test that catches the failure you care about
- Define the workload. Identify whether the index should support an equality or range filter, a join key, sorting, or a combination. Confirm that the indexed columns and their order match that query pattern.
- Assert the schema. After applying the migration, check the database catalog or schema for the expected index, allowing an equivalent index only if that is acceptable for your application. This is the direct way to catch an index that was never created.
- Keep correctness tests focused. Use the ordinary small fixture to verify returned rows and other behavior. Do not require its planner to choose an index.
- Add a plan test only when plan behavior is the regression. Use a separate fixture whose size, values, and distribution approximate the workload where the index matters. There is no magic number of rows that guarantees index use.
- Check the relevant part of the plan. Assert the access path for the target table or relation rather than matching every line of formatted plan output. A valid optimizer may choose another suitable plan, and plan details can change between engine versions.
Inspect plans in SQLite
SQLite’s EXPLAIN QUERY PLAN output describes table access with terms including SCAN and SEARCH. Its documentation says that SEARCH means only a subset of table rows is visited, and shows an index-backed example such as SEARCH t1 USING INDEX i1 (a=?). See SQLite’s EXPLAIN QUERY PLAN documentation.
Run the explanation for the query under test and inspect the detail for the table whose index matters. A SEARCH using the intended index is evidence of that access path in this run. A SCAN means rows are scanned, but it does not by itself prove that no index exists; the scan may be appropriate, and the term’s meaning depends on whether the scan is full or follows an index.
Inspect plans in PostgreSQL
Use EXPLAIN to inspect PostgreSQL’s selected plan and estimated costs. For a fixture used to assess planner behavior, run ANALYZE first so the planner has statistics about the data distribution. PostgreSQL’s documentation covers using EXPLAIN and examining index usage.
EXPLAIN ANALYZE executes the statement and reports actual behavior as well as the plan. Use it deliberately against a safe test database, especially when the statement changes data or has other side effects. Estimates and choices can vary with statistics and planner cost settings, so interpret the result in the context of the test environment.
Rank #3
Make plan assertions resilient
- Test only the plan property that matters, such as whether the target relation uses the expected index for the relevant lookup.
- Account for legitimate alternatives, including joins, covering indexes, and engine upgrades.
- Do not treat a plan string as a portable contract across database engines or versions.
- Use representative values and distributions. PostgreSQL cautions that very similar, completely random, or sorted synthetic values can distort statistics and plan choice.
When the goal is simply to catch a migration that omitted an index, the schema assertion is the decisive test. Reserve realistic data and plan inspection for cases where the optimizer’s choice itself is under test.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
