Projection doesn't use index to make query covered

Hi, could you help find out why the projection stage doesn’t use an index that contains all the fields to be included? And any advice on how to avoid full scan to get the projection.

Objects have the following schema:

test> db.events.find({}).limit(3)
[
  {
    _id: ObjectId("63a086c116b1985489f8ccaf"),
    data: { x: 'X' },
    meta: {
      key: 'nk0',
      schema: { tenant: 't1', name: 'type#0', version: '2.0.22' }
    }
  },
  {
    _id: ObjectId("63a086c116b1985489f8ccb0"),
    data: { x: 'X' },
    meta: {
      key: 'nk1',
      schema: { tenant: 't1', name: 'type#1', version: '2.0.22' }
    }
  },
  {
    _id: ObjectId("63a086c116b1985489f8ccb1"),
    data: { x: 'X' },
    meta: {
      key: 'nk2',
      schema: { tenant: 't1', name: 'type#4', version: '2.0.22' }
    }
  }
]

I’ve created a unique compound index that includes ‘meta.key’, ‘meta.schema.name’, ‘meta.schema.tenant’

test> db.events.getIndices()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { 'meta.key': 1, 'meta.schema.name': 1, 'meta.schema.tenant': 1 },
    name: 'meta.key_1_meta.schema.name_1_meta.schema.tenant_1',
    unique: true
  }
]

I want to extract these fields into a separate collection. I expect that aggregation pipeline will use the index to retrieve data, but it doesn’t.

test> db.events.explain().aggregate([{$project: {'_id':0, 'meta.key': 1, 'meta.schema.name': 1, 'meta.schema.tenant': 1}}, {$out: 'out_test'}])
{
  explainVersion: '1',
  stages: [
    {
      '$cursor': {
        queryPlanner: {
          namespace: 'test.events',
          indexFilterSet: false,
          parsedQuery: {},
          queryHash: 'E7D9C058',
          planCacheKey: 'E7D9C058',
          maxIndexedOrSolutionsReached: false,
          maxIndexedAndSolutionsReached: false,
          maxScansToExplodeReached: false,
          winningPlan: {
            stage: 'PROJECTION_DEFAULT',
            transformBy: {
              meta: { key: true, schema: { tenant: true, name: true } },
              _id: false
            },
            inputStage: { stage: 'COLLSCAN', direction: 'forward' }
          },
          rejectedPlans: []
        }
      }
    },
    { '$out': { db: 'test', coll: 'out_test' } }
  ],
  serverInfo: {
    host: '02295ffc4e95',
    port: 27017,
    version: '6.0.2',
    gitVersion: '94fb7dfc8b974f1f5343e7ea394d0d9deedba50e'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  command: {
    aggregate: 'events',
    pipeline: [
      {
        '$project': {
          _id: 0,
          'meta.key': 1,
          'meta.schema.name': 1,
          'meta.schema.tenant': 1
        }
      },
      { '$out': 'out_test' }
    ],
    cursor: {},
    '$db': 'test'
  },
  ok: 1
}

What is wrong?


Using MongoDB: 6.0.2
Using Mongosh: 1.5.0

You need to add a match stage.

Something like {$match:{'meta.key':{$gte:Minkey}}} should work for you.

That works. Thanks.
Could you explain or share a link to understand why and how it’s working? And why is not working without match stage?

It because before the project your query is doing a match on all of the documents, similar to a find({}) with no arguments then the project is showing or suppressing the fields that you want from those documents. When you do an empty find it is a COLLSCAN and can’t use an index

So by adding a match on an indexed field you get the benefit of an index (IXSCAN) and then the projection on those documents.

It would be similar to doing db.collection.find({}, {'_id':0, 'meta.key': 1, 'meta.schema.name': 1, 'meta.schema.tenant': 1})

VS
db.collection.find({'meta.key': value}, {'_id':0, 'meta.key': 1, 'meta.schema.name': 1, 'meta.schema.tenant': 1})

3 Likes

Here is an example with find + project and we see similar results you get

> db.product.find({}, {'_id':0}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "products.product",
                "indexFilterSet" : false,
                "parsedQuery" : {

                },
                "queryHash" : "8B3D4AB8",
                "planCacheKey" : "8B3D4AB8",
                "winningPlan" : {
                        "stage" : "PROJECTION_DEFAULT",
                        "transformBy" : {
                                "_id" : 0
                        },
                        "inputStage" : {
                                "stage" : "COLLSCAN",
                                "direction" : "forward"
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "",
                "port" : 27017,
                "version" : "4.4.15",
                "gitVersion" : "bc17cf2c788c5dda2801a090ea79da5ff7d5fac9"
        },
        "ok" : 1
}
> db.product.find({'sku': 20000026}, {'_id':0}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "products.product",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "sku" : {
                                "$eq" : 20000026
                        }
                },
                "queryHash" : "5B7E4F14",
                "planCacheKey" : "FD7B6BBF",
                "winningPlan" : {
                        "stage" : "PROJECTION_DEFAULT",
                        "transformBy" : {
                                "_id" : 0
                        },
                        "inputStage" : {
                                "stage" : "FETCH",
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "sku" : 1
                                        },
                                        "indexName" : "sku_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "sku" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "sku" : [
                                                        "[20000026.0, 20000026.0]"
                                                ]
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "",
                "port" : 27017,
                "version" : "4.4.15",
                "gitVersion" : "bc17cf2c788c5dda2801a090ea79da5ff7d5fac9"
        },
        "ok" : 1
}

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.