An index is a bet: you pay write and storage cost up front so reads can skip most of the table. When a query genuinely needs most of the table, the bet loses: the engine descends a B-tree per row instead of reading the pages once in order.

Planners price both routes, so the real question is where the crossover sits. A small table is not a small amount of work, and the plan you see at a thousand rows is not the plan to expect at ten million.

The currency is pages, not rows

A sequential scan reads the table’s pages in physical order, many rows per page. Postgres prices that with seq_page_cost (1.0 by default) against random_page_cost (4.0 by default): a non-sequentially fetched page is priced four times a sequential one, and lowering random_page_cost relative to seq_page_cost is precisely what makes the planner start preferring index scans. When the whole database fits in cache, the documentation suggests the two can reasonably be equal.

Because pages are the currency, row count is a poor proxy for size. Two tables of 1,000 rows: a 4-byte payload is 12 pages, a 4,000-byte payload is 1,010. On sqlite 3.53 with a 2 MB page cache the broad query over the narrow table finished in 0.02 ms and over the wide one in 1.30 ms — a 65x spread at identical row counts. Only one of those tables is small.

Where the index stops paying

Forcing each access path makes the crossover visible. The table is events(id, tenant, payload), 1,000 distinct tenants, an index on tenant, predicate matching roughly 90% of rows:

  • 1,000 rows in 21 pages: scan 0.04 ms, index 0.05 ms. A tie. The index buys nothing at this size and still has to be maintained on every write.
  • 50,000 rows in 767 pages: scan 1.79 ms, index 6.61 ms. The scan wins by 3.7x.
  • 1,000,000 rows in 15,474 pages (63 MB): scan 44.37 ms, index 531.18 ms. The scan wins by 12x, and across seven samples the ratio never dropped below 11.3x.

The same index on the same million-row table is a 29x win for a selective predicate: tenant = 7 covers about 0.1% of rows and took 0.61 ms through the index against 17.88 ms for the scan. The index is not bad; it is bad for that predicate. Selectivity decides, and it decides harder as the table grows.

Let the planner decide, but keep its inputs honest

sqlite chose the scan for the broad predicate at every size and the index for the selective one. That is the correct default, and the lever when a plan looks wrong is the statistics, not a hint.

Statistics decay and the plan decays with them. ANALYZE the million-row table, then move every row to tenant = 5 without re-analyzing. sqlite_stat1 still reports 1,000 rows per key, so the planner keeps picking SEARCH ... USING INDEX for a predicate matching 100% of the table. Run ANALYZE and the statistic becomes 1,000,000 rows per key and the plan flips to SCAN. The wrong plan cost only 1.2x here, because sqlite’s table b-tree is keyed by rowid so the index walk still fetches pages in near-physical order; in Postgres each index hit is a separate heap fetch priced at random_page_cost.

When it breaks

The classic failure is the plan that flips when statistics go stale. A nightly job loads a tenant that comes to dominate the table; the histogram still describes last week; the dashboard queries that used to be index lookups now read a fifth of the table through the index. Nothing errors. Latency simply steps up, on exactly the queries nobody was watching.

The second failure is scale, not shape. The scan that costs 0.03 ms over 21 pages costs 1.88 ms over 767 and 41.34 ms over 15,474, because cost is linear in pages. A table that is fine today becomes an unaffordable full scan after a year of growth. The fix is an index the planner will actually choose, or a query that returns fewer rows.

Check it quickly

Confirm the table is small in pages, then see whether the planner agrees with reality:

sqlite3 events.db "PRAGMA page_count; PRAGMA page_size;"
sqlite3 events.db "EXPLAIN QUERY PLAN SELECT sum(length(payload)) FROM events WHERE tenant < 900;"
sqlite3 events.db "EXPLAIN QUERY PLAN SELECT sum(length(payload)) FROM events NOT INDEXED WHERE tenant < 900;"
sqlite3 events.db "ANALYZE; SELECT stat FROM sqlite_stat1 WHERE idx='events_tenant';"
printf '.timer on\nSELECT sum(length(payload)) FROM events NOT INDEXED WHERE tenant < 900;\n' | sqlite3 events.db

The first line gives you pages, the number that matters. The next two should name the same path for a broad predicate: if the planner says SEARCH ... USING INDEX while the forced SCAN is measurably faster, the statistics are the suspect. sqlite_stat1 shows the average rows per key — when it still says 1000 while one key owns the table, re-run ANALYZE and read the plan again.

Sources