correlated EXISTS is ~128.69× slower than IN (subquery)

A correlated `EXISTS` is not rewritten into a join by SQLite: it becomes 100,000 primary-key lookups (129× slower). The `IN (subquery)` form scans once.

Result

Spread over 3 runs: correlated EXISTS CV 1.4% / IN (subquery) CV 3.4% / Ratio CV 2.2%

The statement

SELECT id FROM items i WHERE EXISTS (SELECT 1 FROM items x WHERE x.id=i.id AND x.score>900) LIMIT 400;

SELECT id FROM items WHERE id IN (SELECT id FROM items WHERE score>900) LIMIT 400;

Conditions

100,000 rows; `id` is the primary key and `score` is indexed.

Where this number does not apply

If the correlated table is small, or the outer query returns few rows, the gap shrinks.

Reproduce

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