core/optimizer: cost model makes a new in-memory index look too cheap
estimate_ephemeral_index_build_cost in core/translate/optimizer/cost.rs multiplies the key comparisons of a new in-memory index by cpu_cost_per_seek.
cpu_cost_per_seek is the cost of one seek into an index that exists, and estimate_index_cost charges it one time for each seek. estimate_ephemeral_index_build_cost charges it for each key comparison. A comparison and a seek are not the same work, so one constant cannot be correct for both. The optimizer makes a new in-memory index look much cheaper than it is.
TPC-H query 11 builds an in-memory index over partsupp two times (sqlite/conformance/sqlite-sqltests/snapshot_tests/tpch/snapshots/tpch__q11-important-stock.snap). With 160,000 rows in partsupp and 2,000 in supplier, after ANALYZE, that plan takes 43 s. The same query with CROSS JOIN for the other join order takes 1.5 s and gives the same rows. Both times come from a debug build.
A new in-memory index needs a cost constant of its own.
Source: tursodatabase/turso