primary key lookup (id = ?) is ~26.32× faster than search on an unindexed column (0 matches)

A primary-key lookup is 26× faster. On an unindexed column SQLite reads every row even when nothing matches, so the gap tracks scan volume.

Result

Spread over 3 runs: primary key lookup (id = ?) CV 2.1% / search on an unindexed column (0 matches) CV 0.6% / Ratio CV 2.1%

The statement

SELECT * FROM items WHERE id = 54321;

SELECT * FROM items WHERE payload = 'not-there';

Conditions

100,000 rows; `payload` is the same value in every row.

Where this number does not apply

On a table small enough to sit in cache, the scan is fast too and the gap shrinks.

Reproduce

cd bench && python sqbench.py --samples 9 --out ../data/run.json