SERVER-139211ClosedDuplicate
Join cost model picks COLLSCAN + hash join over index nested loop join for in-memory data once cardinality estimates are accurate
h2. Summary With more accurate cardinality estimates, the join optimizer switches some TPC\-H plans from an index nested loop join \(INLJ\) to a COLLSCAN \+ hash join \(HJ\), and the new plans are 2.8–7.3x slower when the data is in memory, the regime the join plan stability tests and most working sets run in. When the cache is cold they are no faster \(0.98–1.04x\). The join cost model appears to misprice INLJ probes against COLLSCAN \+ HJ in the CPU\-bound \(cached\) regime. The previous plan