As part of the investigation into BF-44824, we noticed that setting internalQueryPlanRanker: "costBased" in a JOO test suite caused major regressions on this query from the JOO plan stability golden test suite.

The regression here occurs in the part of the pipeline that is planned outside of the join optimizer, i.e. this part of the pipeline uses the classic $lookup agg code, which does regular access path selection. We can tell this is outside the join optimizer because it is formatted as a "$lookup <field> <index>" rather than the tree formatting seen above in the JOO segment.

This knob internalQueryPlanRanker: "costBased" was explicitly set in the join suites as part of SERVER-119688. Prior to the knob being set in the join test suites, the optimal plan for the above query when using the default value of automaticCE, was:

    "winningPlan": [
"HJ ps_partkey = p_partkey",
"  -> [partsupp] COLLSCAN: plan_stability_tpch_fuzzed.partsupp {'$and':[{'$or':[{'ps_availqty':{'$lte':9607}},{'ps_availqty':{'$gt':5937}}]},{'ps_availqty':{'$lt':9935}},{'ps_comment':{'$regex':'^i'}}]} ",
"  -> [none] COLLSCAN: plan_stability_tpch_fuzzed.part ",
"$match",
"$lookup lineitem l_partkey_1_l_suppkey_1"],
    "keys" :        86,
    "docs" :    100086,
    "rows" :        32,
    "csum" : "cfdcd1c75de85b84"},

(source)
Note the usage of the compound index l_partkey_1_l_suppkey_1.

After the knob was set to only use CBR rather than automaticCE in SERVER-119688, the optimal plan oscillated between 2 plans depending on the way the data was read (more info in this comment). The 2 possible plans we now saw were:

    "winningPlan": [
"HJ ps_partkey = p_partkey",
"  -> [partsupp] COLLSCAN: plan_stability_tpch_fuzzed.partsupp {'$and':[{'$or':[{'ps_availqty':{'$lte':9607}},{'ps_availqty':{'$gt':5937}}]},{'ps_availqty':{'$lt':9935}},{'ps_comment':{'$regex':'^i'}}]} ",
"  -> [none] COLLSCAN: plan_stability_tpch_fuzzed.part ",
"$match",
"$lookup lineitem l_partkey_1"],
    "keys" :     77237,
    "docs" :    177237,
    "rows" :        32,
    "csum" : "cfdcd1c75de85b84"},

or

  "winningPlan": [
"HJ ps_partkey = p_partkey",
"  -> [partsupp] COLLSCAN: plan_stability_tpch_fuzzed.partsupp {'$and':[{'$or':[{'ps_availqty':{'$lte':9607}},{'ps_availqty':{'$gt':5937}}]},{'ps_availqty':{'$lt':9935}},{'ps_comment':{'$regex':'^i'}}]} ",
"  -> [none] COLLSCAN: plan_stability_tpch_fuzzed.part ",
"$match",
"$lookup lineitem l_suppkey_1"],
    "keys" :     1633855,
    "docs" :     1733855,
    "rows" :                32,
    "csum" : "cfdcd1c75de85b84"},

We can see that the metrics in both of these new plans that use index l_partkey_1 or l_suppkey_1 indicate they are significantly worse than the optimal plan with the compound index.

I believe that this indicates a regression when using pure CBR to do access path selection in this case over using automaticCE, where presumably MP is able to fully make the decision.