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.