iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To catch a missing database index, test that the index exists in the schema or catalog; do not rely on a query plan from a 20-row table. A sequential scan can be the right choice for such a small table, even when the index is present. If you also need to test planner behavior, use a separate fixture with representative data and inspect its plan.
Why 20 rows cannot prove an index is missing
A planner chooses an access path based on estimated cost, not simply on whether an index exists. For a tiny table, reading every row may cost less than looking up rows through an index. PostgreSQL’s documentation illustrates the point: selecting one row from a 100-row table may still favor a sequential scan because the table can fit on one disk page. That is an explanatory example, not a universal row-count threshold. PostgreSQL: Examining Index Usage
There is no dependable minimum number of rows at which every database will use an index. The result depends on the database engine, query, values and their distribution, planner statistics, and cost settings. A sequential scan on your 20-row test fixture therefore does not establish that a migration failed.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Test index existence separately from planner behavior
| Check | What it tells you | Best fit |
|---|---|---|
| Schema or catalog assertion | Whether the expected index was created | Catching a missing or incorrectly named index after a migration |
| Query-plan inspection | Which access path the optimizer selects for a particular query and dataset | Checking planner behavior under representative conditions |
Keep ordinary correctness tests focused on returned rows: an index should help retrieve the answer, not change it. Add a direct schema-level assertion for index presence. If performance behavior is the regression you are guarding against, create a distinct plan test rather than trying to make the small correctness fixture do both jobs.
#1 Best Overall
How to check the plan in SQLite
SQLite’s EXPLAIN QUERY PLAN output describes each table access as a SCAN or SEARCH and can show the index and indexed WHERE terms involved.
SEARCH ... USING INDEX ...indicates that SQLite visits a subset of rows using an index. The documentation’s example isSEARCH t1 USING INDEX i1 (a=?).SCANmeans rows are scanned. Read the full plan detail: a scan can be a full table scan or a scan following an index, so the word alone does not establish that an index is absent.
Check the detail for the table relevant to your query, rather than asserting that the entire plan must contain a particular line in a fixed position. SQLite’s Query Planning documentation also shows how candidate indexes and statistics affect planning.
How to check the plan in PostgreSQL
PostgreSQL’s Using EXPLAIN documentation explains how to inspect a plan tree and its estimated costs. For a fixture-based plan experiment, collect statistics before inspecting the plan so the planner has information about the data distribution.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →- Build a separate fixture with enough rows and a distribution that approximates the production values relevant to the query.
- Run
ANALYZEon the fixture so PostgreSQL can use distribution statistics. - Run
EXPLAINon the target query and inspect the plan node for the relevant relation and access path. - Use
EXPLAIN ANALYZEonly when you deliberately want to execute the statement and observe actual behavior; run it in a safe test database.
A plan is evidence about that query, data, statistics, and environment—not a general guarantee that PostgreSQL will always use the index. PostgreSQL’s guidance recommends using real data for experimentation: synthetic values that are nearly identical, completely random, or inserted in sorted order can skew statistics and plan choice. PostgreSQL: Examining Index Usage
Make the plan test reflect the index’s job
Start with the workload the index is intended to support. An index on an unrelated column, or one whose column order does not suit the query, may not support the condition being tested.
- For equality or range predicates, use representative values and selectivity for those predicates.
- For joins, make the fixture and plan assertion target the relevant join key and relation.
- For ordering or combined conditions, reflect the actual ordering and column combination the query needs.
- Allow for legitimate alternatives such as a covering index or a different join strategy when they still meet the intended requirement.
Avoid asserting the entire formatted plan when you only care about one access-path property. Plan output and optimizer choices are specific to the database engine and version; an assertion tied to one complete text rendering can break after a legitimate plan change. Assert the relevant relation and index use, and decide in advance which alternate plans are acceptable.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.A practical test layout
- Schema test: apply the migration, then query the engine’s schema or catalog to assert that the intended index exists. This is the direct check for a missing index.
- Correctness test: use a small fixture if it keeps the test fast, and verify the query returns the expected rows.
- Plan test, when needed: use a separate, representative dataset; gather planner statistics where applicable; inspect the plan with the engine’s plan tool; and assert only the access-path behavior that matters.
This division makes failures easier to diagnose: a failed schema assertion points to index creation, while an unexpected plan points to planner behavior under the conditions represented by that plan test.
Quick Recap
Best Value
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.
