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

Affected Versions
Versions known to exhibit this issue

No affected versions recorded.

Fixed Versions
Versions that include the fix

No fix available yet.

Components
Server components related to this issue

No components specified.

Labels
Tags and classification

No labels assigned.