Investigate instability in plan selection for certain queries

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide 
      const docs = [
    {
        "_id": 187,
        "t": ISODate("1970-01-01T00:00:00Z"),
        "m": {"m1": NumberInt(0), "m2": NumberInt(0)},
        "array": [],
        "a": NumberInt(0),
        "b": NumberInt(0)
    },
    {
        "_id": 83,
        "t": ISODate("1970-01-01T00:00:00Z"),
        "m": {"m1": NumberInt(0), "m2": ISODate("1970-01-01T00:00:00Z")},
        "array": "",
        "a": NumberInt(0),
        "b": NumberInt(0)
    }
];
const query = [{
    "$match": {
        "$or": [{"t": {"$exists": true}}, {"_id": 0, "a": 0}],
        "$and": [{"array": {"$nin": [0]}}, {"array": {"$eq": ""}}]
    }
}];

const coll = db.c;
assert.commandWorked(coll.insert(docs))
assert.commandWorked(coll.createIndex({t: 1, array: 1}))

jsTestLog(coll.explain().aggregate(query))
jsTestLog(coll.explain().aggregate(query))
jsTestLog(coll.explain().aggregate(query))
// may output different plans
      Show
      const docs = [ { "_id" : 187, "t" : ISODate( "1970-01-01T00:00:00Z" ), "m" : { "m1" : NumberInt(0), "m2" : NumberInt(0)}, "array" : [], "a" : NumberInt(0), "b" : NumberInt(0) }, { "_id" : 83, "t" : ISODate( "1970-01-01T00:00:00Z" ), "m" : { "m1" : NumberInt(0), "m2" : ISODate( "1970-01-01T00:00:00Z" )}, "array" : "", "a" : NumberInt(0), "b" : NumberInt(0) } ]; const query = [{ "$match" : { "$or" : [{ "t" : { "$exists" : true }}, { "_id" : 0, "a" : 0}], "$and" : [{ "array" : { "$nin" : [0]}}, { "array" : { "$eq" : ""}}] } }]; const coll = db.c; assert .commandWorked(coll.insert(docs)) assert .commandWorked(coll.createIndex({t: 1, array: 1})) jsTestLog(coll.explain().aggregate(query)) jsTestLog(coll.explain().aggregate(query)) jsTestLog(coll.explain().aggregate(query)) // may output different plans
    • None
    • 3
    • TBD
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      The repro shows that different plans are chosen depending on the run. Sometimes the bounds for one branch is a point, and sometimes it's almost Minkey-Maxkey (the actual bounds are [MinKey, 0.0) U (0.0, MaxKey] )

      The multiplanner should be deterministic, we should investigate why this happens

            Assignee:
            Unassigned
            Reporter:
            Matt Boros
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated: