histogramCE: cardinality estimate with residual predicate flip-flops between two values

XMLWordPrintableJSON

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      The cardinality estimate will flip between two distinct values. I suspect this is if there is a negation of some sort ($nin, $ne) in the $match statement that ends up in the residual predicate, as all the examples I have seen so far seem to contain negation.

      Enterprise test> db.plan_stability.aggregate(pipeline).explain().queryPlanner.rejectedPlans[0].cardinalityEstimate;
40517.68738077253
Enterprise test> db.plan_stability.aggregate(pipeline).explain().queryPlanner.rejectedPlans[0].cardinalityEstimate;
28329.629072663985

      The plan in the rejectedPlans[0] slot is completely identical between the two executions in all but the cardinalityEstimate of the top-level FETCH.

      To reproduce:

      1. Run:

      buildscripts/resmoke.py run --installDir bazel-bin/install-dist-test/bin --suites=query_golden_classic '--mongodSetParameters={internalQueryFrameworkControl: forceClassicEngine, internalQuerySamplingBySequentialScan: True }' jstests/query_golden/plan_stability.js --pauseAfterPopulate

      Until it arrives to :

      [js_test:plan_stability] [jsTest] ----
[js_test:plan_stability] [jsTest] TestData.pauseAfterPopulate is set. Pausing indefinitely ...
[js_test:plan_stability] [jsTest] ----

      2. Run:

      db.adminCommand({setParameter: 1, planRankerMode: "automaticCE"});
pipeline = null;
pipeline = [{
    "$match": {
        "$and": [{
            "a_noidx": 1
        }, {
            "$nor": [{
                 "$nor": [{
                    "a_compound": 1
                },  {
                    "a_compound": 2
                }]
            }, {
                "c_noidx": 3
            }]
        }],
        "a_idx": {
            "$ne": 14
        }
    }
}];


db.plan_stability.aggregate(pipeline).explain().queryPlanner.rejectedPlans[0];
db.plan_stability.aggregate(pipeline).explain().queryPlanner.rejectedPlans[0];
db.plan_stability.aggregate(pipeline).explain().queryPlanner.rejectedPlans[0].cardinalityEstimate;
db.plan_stability.aggregate(pipeline).explain().queryPlanner.rejectedPlans[0].cardinalityEstimate;

            Assignee:
            Unassigned
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated: