Details
-
Bug
-
Resolution: Fixed
-
Major - P3
-
None
-
MongoDb Version 6.0.2
-
Query Execution
-
Fully Compatible
-
ALL
-
v6.0, v5.0
-
Hide
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.
ShowMongoDB 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. -
(copied to CRM)
Description
In MongoDB version 6.0.2 when running explain on an aggregation having a `$lookup` stage followed by an `$unwind`, the executionstats metrics from the explain plan are not reported properly.
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}])
|
According to the explain,
"totalDocsExamined" : NumberLong(0),
|
"totalKeysExamined" : NumberLong(0),
|
"collectionScans" : NumberLong(0),
|
"indexesUsed" : [ ],
|
"nReturned" : NumberLong(1),
|
However, when using `$indexstats` we can see an index has been used.
if I remove the $unwind stage, explain reports the metrics properly