Show
MongoDB version 6.0.2.
We have two collections:
1. Collection pod7sims
with document:
{ "_id" : ObjectId( "6361f3964759e66f091a87e5" ), "acctid" : 100121114, "activationdate" : "1629923806736" , "cellidtracking" : false , "commplanid" : 313, "suspended" : "N" , "dateadded" : "158839640" , "datemodified" : "16142" , "dateoneboxmodified" : "1652143" , "dateshipped" : "16054" , "documentmeta" : { "datemodified" : "16657093" }, "iccid" : "dummy" , "imsi1" : "dummy" , "laststatechange" : "16206729" , "linepaystatus" : "CURRENT" , "live" : true , "migratedsim" : "N" , "msisdn" : "dummy" , "networkblocked" : false , "notes" : "noteeee" , "operatorid" : 25014, "overagelimit" : 30, "overagelimitoverridemode" : 1, "overagelimitreached" : false , "primarydevice" : true , "rateplanid" : 1411914, "rawsimid" : 482812113, "simid" : 522790313, "simprofileid" : 134813, "status" : 6, "testreadydatalimit" : 20480, "testreadydatastate" : 11, "testreadytimelimit" : 120, "testreadytimestate" : 11, "version" : 0 }
and index:
{ v: 2, key: { acctid: 1 }, name: 'acctid_1' },
2. Collection pod7smtdu
with document:
{ "_id" : ObjectId( "636203a14759e66f091a87e9" ), "billingcycleid" : 233, "scheduleid" : 1, "simid" : 522790313, "acctid" : 100057613, "billable" : false , "billablecsdusage" : 0, "billabledatausage" : 236544, "billablesmsusage" : 864, "billableussdusage" : 0, "billablevoiceusage" : 0, "csdusage" : 0, "datausage" : "236544" , "dateadded" : 1646331936072, "datemodified" : "1647283182076" , "operatorid" : 27014, "smsusage" : 864, "ussdusage" : 0, "voiceusage" : 0 }
and index:
{ v: 2, key: { simid: -1, billingcycleid: -1, scheduleid: -1 }, name: 'simid_-1_billingcycleid_-1_scheduleid_-1' , unique: true }
If I run the following aggregation with explain:
> db.pod7sims.explain( "executionStats" ).aggregate([
... {$match: {acctid: 100121114}},
...
... {$lookup: { from: 'pod7smtdu' ,
... localField: 'simid' , foreignField: 'simid' ,
... pipeline: [ {$match: {$expr: {$and: [ {$eq: [ '$scheduleid' , 1]},
... {$eq: [ '$billingcycleid' , 233]} ]}}}
... ],
... as: 'smtdu'
... }
... },
... {$unwind: {path: '$smtdu' }},
... {$sort: { 'smtdu.datausage' : -1}},
... {$limit: 50}
... ])
{
"explainVersion" : "1" ,
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"namespace" : "01021859.pod7sims" ,
"indexFilterSet" : false ,
"parsedQuery" : {
"acctid" : {
"$eq" : 100121114
}
},
"queryHash" : "598915AC" ,
"planCacheKey" : "CAA53C2C" ,
"maxIndexedOrSolutionsReached" : false ,
"maxIndexedAndSolutionsReached" : false ,
"maxScansToExplodeReached" : false ,
"winningPlan" : {
"stage" : "FETCH" ,
"inputStage" : {
"stage" : "IXSCAN" ,
"keyPattern" : {
"acctid" : 1
},
"indexName" : "acctid_1" ,
"isMultiKey" : false ,
"multiKeyPaths" : {
"acctid" : [ ]
},
"isUnique" : false ,
"isSparse" : false ,
"isPartial" : false ,
"indexVersion" : 2,
"direction" : "forward" ,
"indexBounds" : {
"acctid" : [
"[100121114.0, 100121114.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true ,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH" ,
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN" ,
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"keyPattern" : {
"acctid" : 1
},
"indexName" : "acctid_1" ,
"isMultiKey" : false ,
"multiKeyPaths" : {
"acctid" : [ ]
},
"isUnique" : false ,
"isSparse" : false ,
"isPartial" : false ,
"indexVersion" : 2,
"direction" : "forward" ,
"indexBounds" : {
"acctid" : [
"[100121114.0, 100121114.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
},
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(0)
},
{
"$lookup" : {
"from" : "pod7smtdu" ,
"as" : "smtdu" ,
"localField" : "simid" ,
"foreignField" : "simid" ,
"let" : {
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$scheduleid" ,
1
]
},
{
"$eq" : [
"$billingcycleid" ,
233
]
}
]
}
}
}
],
"unwinding" : {
"preserveNullAndEmptyArrays" : false
}
},
"totalDocsExamined" : NumberLong(0),
"totalKeysExamined" : NumberLong(0),
"collectionScans" : NumberLong(0),
"indexesUsed" : [ ],
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(0)
},
{
"$sort" : {
"sortKey" : {
"smtdu.datausage" : -1
},
"limit" : NumberLong(50)
},
"totalDataSizeSortedBytesEstimate" : NumberLong(1587),
"usedDisk" : false ,
"spills" : NumberLong(0),
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(0)
}
],
"serverInfo" : {
"host" : "M-C02CC3ZNMD6R" ,
"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" : "pod7sims" ,
"pipeline" : [
{
"$match" : {
"acctid" : 100121114
}
},
{
"$lookup" : {
"from" : "pod7smtdu" ,
"localField" : "simid" ,
"foreignField" : "simid" ,
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$scheduleid" ,
1
]
},
{
"$eq" : [
"$billingcycleid" ,
233
]
}
]
}
}
}
],
"as" : "smtdu"
}
},
{
"$unwind" : {
"path" : "$smtdu"
}
},
{
"$sort" : {
"smtdu.datausage" : -1
}
},
{
"$limit" : 50
}
],
"cursor" : {
},
"$db" : "01021859"
},
"ok" : 1
}
we can see in the lookup-unwind stage reports the following which indicates no index was used:
"totalDocsExamined" : NumberLong(0),
"totalKeysExamined" : NumberLong(0),
"collectionScans" : NumberLong(0),
"indexesUsed" : [ ],
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(0)
However, if we use index stats before and after the execution:
db.pod7smtdu.aggregate( [{$indexStats: {}}, {$project: {key: 1, 'accesses.ops' : 1}}] )
before:
{ "key" : { "simid" : -1, "billingcycleid" : -1, "scheduleid" : -1 }, "accesses" : { "ops" : NumberLong(4) } }
after
{ "key" : { "simid" : -1, "billingcycleid" : -1, "scheduleid" : -1 }, "accesses" : { "ops" : NumberLong(5) } }
we can see an index has been used.
Bug
Major - P3