Understanding Compound Index in MongoDB

Working with Data
,
1

Firstly, I create a collection - test and inserted some values

db.test.insertOne({a:1,b:1,c:1})

Then I created a compound index

db.test.createIndex({a:1,b:1})

Then I used the explain() to see the winning plan

Atlas atlas-d3opcw-shard-0 [primary] example> db.test.find({a:1,b:1,c:1}).explain()
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'example.test',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [ { a: { '$eq': 1 } }, { b: { '$eq': 1 } }, { c: { '$eq': 1 } } ]
    },
    queryHash: '8ACB2000',
    planCacheKey: '3C4FF473',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      filter: { c: { '$eq': 1 } },
      inputStage: {
        stage: 'IXSCAN',
        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: [ '[1, 1]' ], b: [ '[1, 1]' ] }
      }
    },
    rejectedPlans: []
  },
  command: { find: 'test', filter: { a: 1, b: 1, c: 1 }, '$db': 'example' },
  serverInfo: {
    host: 'atlas-d3opcw-shard-00-01.3xfvk.mongodb.net',
    port: 27017,
    version: '6.0.8',
    gitVersion: '3d84c0dd4e5d99be0d69003652313e7eaf4cdd74'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1692249290, i: 1 }),
    signature: {
      hash: Binary(Buffer.from("3538e6ceb2f3ada9370432af2cd8526de5649c01", "hex"), 0),
      keyId: Long("7222277933912555522")
    }
  },
  operationTime: Timestamp({ t: 1692249290, i: 1 })

then i created one more composite index

db.test.createIndex({a:1,b:1,c:1})

looked again at the winning plan

Atlas atlas-d3opcw-shard-0 [primary] example> db.test.find({a:1,b:1,c:1}).explain()
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'example.test',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [ { a: { '$eq': 1 } }, { b: { '$eq': 1 } }, { c: { '$eq': 1 } } ]
    },
    queryHash: '8ACB2000',
    planCacheKey: '8E4E5BF1',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      filter: { c: { '$eq': 1 } },
      inputStage: {
        stage: 'IXSCAN',
        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: [ '[1, 1]' ], b: [ '[1, 1]' ] }
      }
    },
    rejectedPlans: [
      {
        stage: 'FETCH',
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: { a: 1, b: 1, c: 1 },
          indexName: 'a_1_b_1_c_1',
          isMultiKey: false,
          multiKeyPaths: { a: [], b: [], c: [] },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: { a: [ '[1, 1]' ], b: [ '[1, 1]' ], c: [ '[1, 1]' ] }
        }
      }
    ]
  },
  command: { find: 'test', filter: { a: 1, b: 1, c: 1 }, '$db': 'example' },
  serverInfo: {
    host: 'atlas-d3opcw-shard-00-01.3xfvk.mongodb.net',
    port: 27017,
    version: '6.0.8',
    gitVersion: '3d84c0dd4e5d99be0d69003652313e7eaf4cdd74'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1692249341, i: 7 }),
    signature: {
      hash: Binary(Buffer.from("71739a96ca57b0f6e87bb3f38cf996796d5ebb41", "hex"), 0),
      keyId: Long("7222277933912555522")
    }
  },
  operationTime: Timestamp({ t: 1692249341, i: 7 })

My question is -
Why is MongoDB choosing the plan with index a_1_b_1 when there is a better option of choosing a_1_b_1_c_1?

3

Hey @Abhishek_Chaudhary1,

MongoDB chooses query plans based on the query shape and available indexes to find the optimal balance between performance and resource utilization.

In this case when inserting a document {a:1, b:1, c:1}, and creating two indexes - a_1_b_1 and a_1_b_1_c_1.

The MongoDB will choose the smaller a_1_b_1 index as the winning plan for this query since the collection has a single document. The database can retrieve the matching document using just a and b, and get the c:1 value for free without needing to use the larger index.

However, when inserting a second document {a:1, b:1, c:1} with the same value for c, MongoDB will still use the a_1_b_1 index. This index contains only a and b, but that’s sufficient to locate the two matching documents as the value of c is also the same across the collection.

Finally, upon inserting a third document {a:1, b:1, c:2} with a different value for c, MongoDB will now choose a_1_b_1_c_1 as the winning plan. This index contains all three fields a, b, and c, which allows MongoDB to efficiently locate the two documents that match the query’s criteria on all three fields.

   winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          a: 1,
          b: 1,
          c: 1
        },
        indexName: 'a_1_b_1_c_1',
    ...
    rejectedPlans: [
      {
        stage: 'FETCH',
        filter: {
          c: {
            '$eq': 1
          }
        },
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: {
            a: 1,
            b: 1
          },
          indexName: 'a_1_b_1',

In summary, MongoDB adapts its query plan based on several factors one being data distribution in this specific case. As more documents match on a specific field like c, MongoDB will start utilizing indexes that contain that field, switching from smaller indexes to larger ones as needed to improve query performance.

I hope it clarifies!

Thanks,
Kushagra

5 Likes
4

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