MongoDb not using compound index for an $or query

Working with Data
,
1

I have a collection with almost 2 million docs.

I have created a compound index as below:

db.test.createIndex( {"beneAccount": 1, "createdDate": 1} )

I run simple query below:

db.test.find({ "beneAccount" : "345678901In", "createdDate" : { "$gt" : { "$date" : "2023-04-05T16:28:28.139Z"}}}, { _id: 0, createdDate:1, beneAccount: 1}).hint("beneAccount_1_createdDate_1").explain('executionStats');

Below is the executionStats, here it shown PROJECTION_COVERED which means it use my created compound index.

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'db1.test',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [
        {
          beneAccount: {
            '$eq': '345678901In'
          }
        },
        {
          createdDate: {
            '$gt': 2023-04-05T16:28:28.139Z
          }
        }
      ]
    },
    queryHash: '7A509538',
    planCacheKey: '1DCD7324',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'PROJECTION_COVERED',
      transformBy: {
        _id: 0,
        createdDate: 1,
        beneAccount: 1
      },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          beneAccount: 1,
          createdDate: 1
        },
        indexName: 'beneAccount_1_createdDate_1',
        isMultiKey: false,
        multiKeyPaths: {
          beneAccount: [],
          createdDate: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          beneAccount: [
            '["345678901In", "345678901In"]'
          ],
          createdDate: [
            '({ $date: "2023-04-05T16:28:28.139Z" }, [])'
          ]
        }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 0,
    executionTimeMillis: 0,
    totalKeysExamined: 0,
    totalDocsExamined: 0,
    executionStages: {
      stage: 'PROJECTION_COVERED',
      nReturned: 0,
      executionTimeMillisEstimate: 0,
      works: 1,
      advanced: 0,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      transformBy: {
        _id: 0,
        createdDate: 1,
        beneAccount: 1
      },
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 0,
        executionTimeMillisEstimate: 0,
        works: 1,
        advanced: 0,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: {
          beneAccount: 1,
          createdDate: 1
        },
        indexName: 'beneAccount_1_createdDate_1',
        isMultiKey: false,
        multiKeyPaths: {
          beneAccount: [],
          createdDate: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          beneAccount: [
            '["345678901In", "345678901In"]'
          ],
          createdDate: [
            '({ $date: "2023-04-05T16:28:28.139Z" }, [])'
          ]
        },
        keysExamined: 0,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    }
  },
  command: {
    find: 'test',
    filter: {
      beneAccount: '345678901In',
      createdDate: {
        '$gt': 2023-04-05T16:28:28.139Z
      }
    },
    projection: {
      _id: 0,
      createdDate: 1,
      beneAccount: 1
    },
    hint: 'beneAccount_1_createdDate_1',
    '$db': 'db1'
  },
  serverInfo: {
    host: '11111',
    port: 27017,
    version: '6.0.4',
    gitVersion: '441'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1685111492, i: 1 }),
    signature: {
      hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
      keyId: 0
    }
  },
  operationTime: Timestamp({ t: 1685111492, i: 1 })
}

The I run below OR query:

db.test.find({ "$or" : [{ "beneAccount" : "345678901In", "createdDate" : { "$gt" : { "$date" : "2023-04-05T16:28:28.139Z"}}}, { "beneAccount" : "145678901In", "createdDate" : { "$gt" : { "$date" : "2023-04-05T16:28:28.14Z"}}}]}, {  _id: 0, createdDate:1, beneAccount: 1}).explain('executionStats');

Then I got below executionStats which stated it used PROJECTION_DEFAULT and not PROJECTION_COVERED.

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'db1.test ',
    indexFilterSet: false,
    parsedQuery: {
      '$or': [
        {
          '$and': [
            {
              beneAccount: {
                '$eq': '345678901In'
              }
            },
            {
              createdDate: {
                '$gt': 2023-04-05T16:28:28.139Z
              }
            }
          ]
        },
        {
          '$and': [
            {
              beneAccount: {
                '$eq': '145678901In'
              }
            },
            {
              createdDate: {
                '$gt': 2023-04-05T16:28:28.140Z
              }
            }
          ]
        }
      ]
    },
    queryHash: '058FF00C',
    planCacheKey: '01157F11',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'SUBPLAN',
      inputStage: {
        stage: 'PROJECTION_DEFAULT',
        transformBy: {
          _id: 0,
          createdDate: 1,
          beneAccount: 1
        },
        inputStage: {
          stage: 'OR',
          inputStages: [
            {
              stage: 'IXSCAN',
              keyPattern: {
                beneAccount: 1,
                createdDate: 1
              },
              indexName: 'beneAccount_1_createdDate_1',
              isMultiKey: false,
              multiKeyPaths: {
                beneAccount: [],
                createdDate: []
              },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                beneAccount: [
                  '["345678901In", "345678901In"]'
                ],
                createdDate: [
                  '({ $date: "2023-04-05T16:28:28.139Z" }, [])'
                ]
              }
            },
            {
              stage: 'IXSCAN',
              keyPattern: {
                beneAccount: 1,
                createdDate: 1
              },
              indexName: 'beneAccount_1_createdDate_1',
              isMultiKey: false,
              multiKeyPaths: {
                beneAccount: [],
                createdDate: []
              },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                beneAccount: [
                  '["145678901In", "145678901In"]'
                ],
                createdDate: [
                  '({ $date: "2023-04-05T16:28:28.14Z" }, [])'
                ]
              }
            }
          ]
        }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 0,
    executionTimeMillis: 0,
    totalKeysExamined: 0,
    totalDocsExamined: 0,
    executionStages: {
      stage: 'SUBPLAN',
      nReturned: 0,
      executionTimeMillisEstimate: 0,
      works: 2,
      advanced: 0,
      needTime: 1,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      inputStage: {
        stage: 'PROJECTION_DEFAULT',
        nReturned: 0,
        executionTimeMillisEstimate: 0,
        works: 2,
        advanced: 0,
        needTime: 1,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        transformBy: {
          _id: 0,
          createdDate: 1,
          beneAccount: 1
        },
        inputStage: {
          stage: 'OR',
          nReturned: 0,
          executionTimeMillisEstimate: 0,
          works: 2,
          advanced: 0,
          needTime: 1,
          needYield: 0,
          saveState: 0,
          restoreState: 0,
          isEOF: 1,
          dupsTested: 0,
          dupsDropped: 0,
          inputStages: [
            {
              stage: 'IXSCAN',
              nReturned: 0,
              executionTimeMillisEstimate: 0,
              works: 1,
              advanced: 0,
              needTime: 0,
              needYield: 0,
              saveState: 0,
              restoreState: 0,
              isEOF: 1,
              keyPattern: {
                beneAccount: 1,
                createdDate: 1
              },
              indexName: 'beneAccount_1_createdDate_1',
              isMultiKey: false,
              multiKeyPaths: {
                beneAccount: [],
                createdDate: []
              },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                beneAccount: [
                  '["345678901In", "345678901In"]'
                ],
                createdDate: [
                  '({ $date: "2023-04-05T16:28:28.139Z" }, [])'
                ]
              },
              keysExamined: 0,
              seeks: 1,
              dupsTested: 0,
              dupsDropped: 0
            },
            {
              stage: 'IXSCAN',
              nReturned: 0,
              executionTimeMillisEstimate: 0,
              works: 1,
              advanced: 0,
              needTime: 0,
              needYield: 0,
              saveState: 0,
              restoreState: 0,
              isEOF: 1,
              keyPattern: {
                beneAccount: 1,
                createdDate: 1
              },
              indexName: 'beneAccount_1_createdDate_1',
              isMultiKey: false,
              multiKeyPaths: {
                beneAccount: [],
                createdDate: []
              },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                beneAccount: [
                  '["145678901In", "145678901In"]'
                ],
                createdDate: [
                  '({ $date: "2023-04-05T16:28:28.14Z" }, [])'
                ]
              },
              keysExamined: 0,
              seeks: 1,
              dupsTested: 0,
              dupsDropped: 0
            }
          ]
        }
      }
    }
  },
  command: {
    find: 'test ',
    filter: {
      '$or': [
        {
          beneAccount: '345678901In',
          createdDate: {
            '$gt': 2023-04-05T16:28:28.139Z
          }
        },
        {
          beneAccount: '145678901In',
          createdDate: {
            '$gt': 2023-04-05T16:28:28.140Z
          }
        }
      ]
    },
    projection: {
      _id: 0,
      createdDate: 1,
      beneAccount: 1
    },
    '$db': 'sbcbfbmy'
  },
  serverInfo: {
    host: '111111',
    port: 27017,
    version: '6.0.4',
    gitVersion: '44ff59461c1353638a71e710f'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1685111862, i: 1 }),
    signature: {
      hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
      keyId: 0
    }
  },
  operationTime: Timestamp({ t: 1685111862, i: 1 })
}

May I know what I am doing wrong and how I can make $OR query to use PROJECTION_DEFAULT? Appreciated and thanks in advance for all the help given.

3

Hi @Emrul_Haikal and welcome to MongoDB community forums!!

The PROJECTION_COVERED stage represents the utilisation of all fields mentioned in the create index commands by the query. In the case of the first query, which is an $and operation and utilises the fields “beneAccount” and “createdDate,” it qualifies as a covered query. However, in the second scenario, where an $or operation is used, the query does not meet the criteria to be considered a covered query.

The above statement is a bit unclear to me as the the query with $or operator makes use of the PROJECTION_DEFAULT in the explain output.

The PROJECTION_COVERED query is an index covers a query only when both all the fields in the query are part of an index, and all the fields returned in the results are in the same index, which generally means we’d be filtering out _id.

To simplify,

Term Explanation
PROJECTION_COVERED An explicit projection which is supported by an index scan (eg, for a covered index query).
PROJECTION_DEFAULT A default projection, where no explicit projection is requested.
PROJECTION_SIMPLE An explicit projection which is supported by a collection access – usually preceded by a FETCH or COLLSCAN step.

Let me know if you have any questions.

Regards
Aasawari

3 Likes