Covered plans can be incorrectly generated for sort queries even without projections

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Minor - P4
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • ALL
    • Hide 
      Enterprise test> db.coll.drop()
true
Enterprise test> const docs = [{a: 1, b: null}, {a: 2}, {a: 3, b: "string"}]

Enterprise test> db.coll.insertMany(docs)
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId('6a7dcd295242dedc1cf36bc5'),
    '1': ObjectId('6a7dcd295242dedc1cf36bc6'),
    '2': ObjectId('6a7dcd295242dedc1cf36bc7')
  }
}
Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: false}}}, {$count: "num"}])
[ { num: 1 } ]
Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: true}}}, {$count: "num"}])
[ { num: 2 } ]
Enterprise test> db.coll.createIndex({a: 1, b: 1})
a_1_b_1
Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: false}}}, {$count: "num"}])

Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: true}}}, {$count: "num"}])
[ { num: 3 } ]
      Show
      Enterprise test> db.coll.drop() true Enterprise test> const docs = [{a: 1, b: null }, {a: 2}, {a: 3, b: "string" }] Enterprise test> db.coll.insertMany(docs) { acknowledged: true , insertedIds: { '0' : ObjectId( '6a7dcd295242dedc1cf36bc5' ), '1' : ObjectId( '6a7dcd295242dedc1cf36bc6' ), '2' : ObjectId( '6a7dcd295242dedc1cf36bc7' ) } } Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: false }}}, {$count: "num" }]) [ { num: 1 } ] Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: true }}}, {$count: "num" }]) [ { num: 2 } ] Enterprise test> db.coll.createIndex({a: 1, b: 1}) a_1_b_1 Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: false }}}, {$count: "num" }]) Enterprise test> db.coll.aggregate([{$sort: {a: 1}}, {$limit: 20}, {$match: {b: {$exists: true }}}, {$count: "num" }]) [ { num: 3 } ]
    • 200
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Consider the followings aggregation pipeline:

      {$sort: {a: 1}}.
{$limit: 20},
{$match: {b: {$exists: false}}},
{$count: "num"}

      The last $count stage works like projections in terms of enabling covered plans, $limit prevents predicate pushdown optimization for the following $match.

      For an index like

      {a: 1, b: 1}

      a full index plan will be generated

      stage: 'PROJECTION_DEFAULT',
planNodeId: 6,
transformBy: { num: true, _id: false },
inputStage: {
  stage: 'GROUP',
  planNodeId: 5,
  inputStage: {
    stage: 'MATCH',
    planNodeId: 4,
    filter: { b: { '$exists': true } },
    inputStage: {
      stage: 'LIMIT',
      planNodeId: 3,
      limitAmount: 20,
      inputStage: {
        stage: 'PROJECTION_COVERED',
        planNodeId: 2,
        transformBy: { b: true, _id: false },
        inputStage: {
          stage: 'IXSCAN',
          planNodeId: 1,
          nss: 'test.coll',
          keyPattern: { a: 1, b: 1 },
          indexName: 'a_1_b_1',
          isMultiKey: false,
          multiKeyPaths: { a: [], b: [] },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: {
            a: [ '[MinKey, MaxKey]' ],
            b: [ '[MinKey, MaxKey]' ]
          }
        }
      }
    }
  }
}

      And the covered plan becomes possible here since we never check bounds for the field "b"in the IndexBoundBuilder.

            Assignee:
            Unassigned
            Reporter:
            Alexander Ignatyev
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: