An index trades storage and write work for a possible reduction in read work. A full scan can win when the table is small or a predicate selects much of it, but neither row count nor selectivity decides alone. Projection, ordering, caching, row width, and available access paths change the comparison. Start with the query’s actual result contract.

Understand the competing work

A B-tree range scan does not descend from the root for every matching index entry. After finding the range, it can advance through adjacent entries. A non-covering path may then need table lookups for missing columns; those lookups can dominate a broad query. A covering index can avoid them, so even a query matching most rows can benefit from an index.

Pages matter, but they are not the only currency. PostgreSQL’s default seq_page_cost and random_page_cost are relative planner estimates, not milliseconds or a charge mechanically applied to every returned row. Caching, correlation, CPU work, bitmap scans, and index-only scans matter too. SQLite stores ordinary table rows in a rowid B-tree; that is a different storage model from PostgreSQL’s heap. Do not transfer a measured ratio between them.

SQLite’s PRAGMA page_count reports the whole database file, including indexes and other objects. It is not the page count of one named table. Likewise, logical page traversal is not a guarantee of contiguous physical disk reads. Compare consistent object scopes and record page size, schema, payload width, and cache conditions alongside row count.

Keep earlier measurements in context

Earlier notes recorded SQLite 3.53 measurements with a 2 MB page cache. For a broad predicate, scan/index times were 0.04/0.05 ms at 1,000 rows, 1.79/6.61 ms at 50,000, and 44.37/531.18 ms at one million. A selective million-row query instead took 0.61 ms through the index versus 17.88 ms scanning. Equal-row narrow/wide fixtures were reported as 12/1,010 pages and 0.02/1.30 ms.

Those are historical observations, not universal thresholds or measurements repeated here. The earlier data generator and complete benchmark environment are unavailable in this review. The exercise below therefore establishes a separate reproducible comparison and prints its runtime version. It does not claim to reproduce the earlier timings, page counts, or automatic planner choices.

When it breaks

A growing tenant can invalidate an old selectivity estimate while every query still returns correct rows. Statistics are one suspect, not proof of the cause: changed parameters, index coverage, cache state, or competing workload can also explain a slower plan. Check estimated versus observed work before forcing a permanent access path.

Fresh statistics do not guarantee a scan for every broad range predicate. SQLite builds differ in optional statistics support, and plan text can change between versions. A SCAN entry can even describe a covering-index scan rather than a table scan. Read the entire plan and compare equivalent query results instead of turning one keyword into a pass/fail rule.

A table scan reads rows directly; a non-covering tenant index first locates and traverses the range, then fetches payload from the table. The separate SQLite fixture checks equal results across chosen, scan and index paths without declaring a winner. Historical timings are not repeated.
The tenant index lacks payload, so its range path includes table lookups. All three fixture paths must return equal results; no winner is asserted. Historical timings remain unreplicated.

Check it quickly

Save this as scans.py and run python3 scans.py. It uses Python’s SQLite binding, creates its own temporary database, and never opens an existing application database. All three paths must return 2,880,000 for the broad predicate and 3,200 for the selective one. Seven alternating-order warm samples report medians without asserting which path wins.

from pathlib import Path
from tempfile import TemporaryDirectory
from statistics import median
from time import perf_counter
import sqlite3

with TemporaryDirectory(prefix="scan-crossover-") as directory:
    db = sqlite3.connect(Path(directory) / "fixture.db")
    try:
        db.executescript("""CREATE TABLE events(id INTEGER PRIMARY KEY, tenant INTEGER, payload TEXT);
        CREATE INDEX events_tenant ON events(tenant);""")
        db.executemany("INSERT INTO events VALUES(?,?,?)",
                       ((i, i % 1000, "x" * 64) for i in range(50000)))
        db.commit()
        db.execute("ANALYZE")
        print("SQLite", sqlite3.sqlite_version)
        print("database pages", db.execute("PRAGMA page_count").fetchone()[0],
              "page size", db.execute("PRAGMA page_size").fetchone()[0])
        for predicate, expected in [("tenant < 900", 2880000), ("tenant = 7", 3200)]:
            base = "SELECT sum(length(payload)) FROM events {} WHERE " + predicate
            paths = {"chosen": "", "scan": "NOT INDEXED", "index": "INDEXED BY events_tenant"}
            queries = {name: base.format(clause) for name, clause in paths.items()}
            samples = {name: [] for name in queries}
            for name, query in queries.items():
                assert db.execute(query).fetchone()[0] == expected
                print(predicate, name, db.execute("EXPLAIN QUERY PLAN " + query).fetchall())
            for repeat in range(7):
                names = list(queries) if repeat % 2 == 0 else list(reversed(queries))
                for name in names:
                    start = perf_counter()
                    result = db.execute(queries[name]).fetchone()[0]
                    samples[name].append((perf_counter() - start) * 1000)
                    assert result == expected
            print("warm median ms", {k: round(median(v), 3) for k, v in samples.items()})
        old = db.execute("SELECT stat FROM sqlite_stat1 WHERE idx='events_tenant'").fetchone()[0]
        db.execute("UPDATE events SET tenant=5")
        db.commit()
        stale = db.execute("SELECT stat FROM sqlite_stat1 WHERE idx='events_tenant'").fetchone()[0]
        assert stale == old
        db.execute("ANALYZE")
        fresh = db.execute("SELECT stat FROM sqlite_stat1 WHERE idx='events_tenant'").fetchone()[0]
        assert fresh.split()[:2] == ["50000", "50000"] and fresh != old
        print("statistics before/stale/fresh", old, stale, fresh)
        try:
            db.execute("SELECT * FROM events INDEXED BY absent_index")
        except sqlite3.OperationalError as error:
            assert "no such index" in str(error), error
        else:
            raise AssertionError("missing required index was silently ignored")
        print("missing INDEXED BY target rejected")
    finally:
        db.close()

The payload expression requires data absent from the tenant index, making this a non-covering comparison. INDEXED BY requires the named index; it is not merely a suggestion. The missing-index negative control confirms that requirement. NOT INDEXED disables secondary indexes here; elsewhere it does not prohibit a rowid lookup. Keep these controls in diagnosis, rather than copying them into application queries without justification.

After the data changes to one tenant, sqlite_stat1 remains stale until ANALYZE. The refreshed entry reports 50,000 rows sharing that key. This tests statistics maintenance, not a guaranteed plan flip. In production, follow the SQLite version’s recommended PRAGMA optimize workflow and schedule expensive analysis appropriately.

The fixture warms data and measures one process without competing writers. It cannot establish cold-storage latency, production concurrency, or the best long-term index budget. Retain representative distributions and query projections in performance tests, and repeat them as the table grows; an acceptable scan today can become expensive without any correctness failure.

Sources