I have the look up code as below
{ "from" : "Books",
"let" : {
"code" : "$Code"},
"pipeline" : [{ "$match" :
{ "$expr" : { "$and" : [{ "$eq" :
["$Code", "$$code"] },
{ "$eq" : ["$Category", "LPR"] },
{ "$eq" : ["$MonthProcessed", 202202] }] } } }],
"as" : "Books" }
This stage is not using the index on Category. Is there any way to achieve that?
Thanks
Can you share getIndexes from both collection and the execution plan?
Please note that the $expr lookups were greatly improved with association to indexes in 5.0+ version. What version you use and will it be possible to upgrade?
Thanks
Pavel
We are using Mongo db server 4.4.
Explain Plan
{
"queryPlanner":{
"plannerVersion":1,
"namespace":"DEV-T01-DR.Library",
"indexFilterSet":false,
"parsedQuery":{
},
"winningPlan":{
"stage":"COLLSCAN",
"direction":"forward"
},
"rejectedPlans":[
]
},
"executionStats":{
"executionSuccess":true,
"nReturned":1,
"executionTimeMillis":0,
"totalKeysExamined":0,
"totalDocsExamined":1,
"executionStages":{
"stage":"COLLSCAN",
"nReturned":1,
"executionTimeMillisEstimate":0,
"works":3,
"advanced":1,
"needTime":1,
"needYield":0,
"saveState":0,
"restoreState":0,
"isEOF":1,
"direction":"forward",
"docsExamined":1
},
"allPlansExecution":[
]
},
"serverInfo":{
"host":"GWT-5CG0356LF0",
"port":27017,
"version":"4.2.15",
"gitVersion":"d7fd78dead621a539c20791a93abec34bb1be385"
},
"ok":1
}"."
There is no possibility in the near future to upgrade to 5.0version
First it looks like there is no data to scan so why would an index be preferred?
totalKeysExamined”:0
Second you should create an index on all three equality fields combine to answer the prediction…
Having said that the $expr option is not optimized in 4.4 unfortunately… According to the execution plan it actually looks like the server is 4.2.15 …
Can you find or change the data model to use localField and foreignField syntax as it will be better for indexing…
Pavel
First it looks like there is no data to scan so why would an index be preferred?
I just created a sample collection to get the data u have requested with 1 document
Ok… In order to test performance and index behaviour I suggest to have a data set with a reasonable amount of test data.
Its hard to predict any execution outcomes with 1 or 2 documents involved…
Thanks
Pavel
@Sangeetha_Vinusabari there may be some confusion as to what the explain results are showing.
For example, if we try the following in MongoDB 4.2.15:
db.foo.drop(); db.foo.insert({ Code: 1 });
db.bar.drop(); db.bar.insert({ Code: 1, Category: "LPR", MonthProcessed: 202202 });
db.foo.explain("executionStats").aggregate([
{ $lookup: {
"from": "bar",
"let": { "code": "$Code" },
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{ "$eq": ["$Code", "$$code"] },
{ "$eq": ["$Category", "LPR"] },
{ "$eq": ["$MonthProcessed", 202202] }
]
}
}
}],
"as": "Books"
}}]);
the output would show that the operation is performing a COLLSCAN (as you’ve witnessed as well):
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1.0,
"executionTimeMillis" : 1.0,
"totalKeysExamined" : 0.0,
"totalDocsExamined" : 1.0,
"executionStages" : {
"stage" : "COLLSCAN",
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 3.0,
"advanced" : 1.0,
"needTime" : 1.0,
"needYield" : 0.0,
"saveState" : 1.0,
"restoreState" : 1.0,
"isEOF" : 1.0,
"direction" : "forward",
"docsExamined" : 1.0
}
}
This is due to pipelines only being able to use indexes for specific initial stages (ex: $match or $sort).
In our sample above, the first stage is a $lookup, which results in a full COLLSCAN as every document in the foo collection must be scanned and passed to the $lookup stage.
In the $lookup stage (targeting the bar collection in our example) if an index exists on { Category: 1 } it is actually being used!
Though the explain output doesn’t show it directly, we can wrap the operation in a custom function to measure the index usage:
function MEASURE_INDEX_USAGE(block) {
var statsCmd = [{ $indexStats: {} }];
// measure index usage
var statsBefore = {};
db.getCollectionNames().forEach(function (c) {
statsBefore[c] = {};
db.getCollection(c).aggregate(statsCmd).forEach(function (d) {
statsBefore[c][d.name] = d.accesses.ops * 1.0;
});
});
block();
// measure index usage again
var stats = {};
db.getCollectionNames().forEach(function (c) {
stats[c] = {};
db.getCollection(c).aggregate(statsCmd).forEach(function (d) {
if (!statsBefore[c].hasOwnProperty(d.name)) {
stats[c][d.name] = d.accesses.ops;
} else if (statsBefore[c][d.name] != d.accesses.ops) {
stats[c][d.name] = (d.accesses.ops - statsBefore[c][d.name]) + " (" + d.accesses.ops + " total)";
}
});
});
printjson(stats);
}
db.bar.createIndex({ Category: 1 });
MEASURE_INDEX_USAGE(function () {
db.foo.aggregate([
{ $lookup: {
"from": "bar",
"let": { "code": "$Code" },
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{ "$eq": ["$Code", "$$code"] },
{ "$eq": ["$Category", "LPR"] },
{ "$eq": ["$MonthProcessed", 202202] }
]
}
}
}],
"as": "Books"
}}]);
});
The output to the above shows (as we expect) no indexes touched on foo, but 1 index hit on bar:
{
"bar" : {
"Category_1" : "1 (1 total)"
},
"foo" : {
}
}
Explain output was improved in MongoDB 5.0 (see SERVER-53762) which makes it easier to determine what indexes were used by the $lookup stage.
What about $expr in $match
My Model example
{
"_id" : ObjectId("622f573cb9fd75a8f988cdb6"),
"branchId" : ObjectId("6212f2fa0615b313e2eb83f5"),
"groupId" : ObjectId("622f573cb9fd75a8f988cdb4"),
"teacherId" : ObjectId("622f4f70475460a853fd8fa1"),
"date" : ISODate("2022-03-15T00:00:00.000+0000"),
"state" : "created",
"createdAt" : ISODate("2022-03-14T14:54:52.850+0000"),
"updatedAt" : ISODate("2022-03-14T14:54:52.850+0000"),
"deletedAt" : ISODate("2022-03-16T09:04:15.740+0000")
}
Indexes of my collection
[
{
"v" : 2.0,
"key" : {
"_id" : 1.0
},
"name" : "_id_"
},
{
"v" : 2.0,
"key" : {
"date" : 1.0,
"groupId" : 1.0
},
"name" : "UniqueGruopLesson",
"background" : true,
"unique" : true,
"partialFilterExpression" : {
"deletedAt" : {
"$eq" : null
}
}
}
]
Situation 1.
db = db.getSiblingDB("TEST_DB");
db.getCollection("LESSONS").explain("executionStats")
.aggregate(
[
{
$match: {
groupId: ObjectId("627f7821c5e9de1b328ea918"),
date: ISODate("2022-05-30T00:00:00.000+0000"),
deletedAt:null
}
}
]
);
Result 1
{
"explainVersion" : "1",
"queryPlanner" : {
"namespace" : "TEST_DB.LESSONS",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"date" : {
"$eq" : ISODate("2022-05-30T00:00:00.000+0000")
}
},
{
"deletedAt" : {
"$eq" : null
}
},
{
"groupId" : {
"$eq" : ObjectId("627f7821c5e9de1b328ea918")
}
}
]
},
"optimizedPipeline" : true,
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"deletedAt" : {
"$eq" : null
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"date" : 1.0,
"groupId" : 1.0
},
"indexName" : "UniqueGruopLesson",
"isMultiKey" : false,
"multiKeyPaths" : {
"date" : [
],
"groupId" : [
]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"date" : [
"[new Date(1653868800000), new Date(1653868800000)]"
],
"groupId" : [
"[ObjectId('627f7821c5e9de1b328ea918'), ObjectId('627f7821c5e9de1b328ea918')]"
]
}
}
},
"rejectedPlans" : [
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1.0,
"executionTimeMillis" : 0.0,
"totalKeysExamined" : 1.0,
"totalDocsExamined" : 1.0,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"deletedAt" : {
"$eq" : null
}
},
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 2.0,
"advanced" : 1.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 1.0,
"docsExamined" : 1.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 2.0,
"advanced" : 1.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 1.0,
"keyPattern" : {
"date" : 1.0,
"groupId" : 1.0
},
"indexName" : "UniqueGruopLesson",
"isMultiKey" : false,
"multiKeyPaths" : {
"date" : [
],
"groupId" : [
]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"date" : [
"[new Date(1653868800000), new Date(1653868800000)]"
],
"groupId" : [
"[ObjectId('627f7821c5e9de1b328ea918'), ObjectId('627f7821c5e9de1b328ea918')]"
]
},
"keysExamined" : 1.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0
}
}
},
"command" : {
"aggregate" : "LESSONS",
"pipeline" : [
{
"$match" : {
"groupId" : ObjectId("627f7821c5e9de1b328ea918"),
"date" : ISODate("2022-05-30T00:00:00.000+0000"),
"deletedAt" : null
}
}
],
"cursor" : {
},
"$db" : "TEST_DB"
},
"serverInfo" : {
"host" : "nitro",
"port" : 2717.0,
"version" : "5.0.8",
"gitVersion" : "c87e1c23421bf79614baf500fda6622bd90f674e"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600.0,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600.0,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600.0,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600.0,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600.0,
"internalQueryProhibitBlockingMergeOnMongoS" : 0.0,
"internalQueryMaxAddToSetBytes" : 104857600.0,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600.0
},
"ok" : 1.0,
"$clusterTime" : {
"clusterTime" : Timestamp(1652524686, 1),
"signature" : {
"hash" : BinData(0, "AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1652524686, 1)
}
Situation 1 working fine but
Situation 2.
with $expr scanning all documents in collection
why I need it to be $expr because then I need it to be inside $lookup
db = db.getSiblingDB("TEST_DB");
db.getCollection("LESSONS").explain("executionStats")
.aggregate(
[
{
$match: {
$expr: {
$and: [
{
$eq: [
"$groupId",
ObjectId("627f7821c5e9de1b328ea918"),
]
},
{
"$eq": [
"$deletedAt",
null
]
},
{
$eq:[
"$date",
ISODate("2022-05-30T00:00:00.000+0000")
]
}
]
}
}
}
]
);
Result 2
{
"explainVersion" : "1",
"queryPlanner" : {
"namespace" : "TEST_DB.LESSONS",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"$expr" : {
"$and" : [
{
"$eq" : [
"$groupId",
{
"$const" : ObjectId("627f7821c5e9de1b328ea918")
}
]
},
{
"$eq" : [
"$deletedAt",
{
"$const" : null
}
]
},
{
"$eq" : [
"$date",
{
"$const" : ISODate("2022-05-30T00:00:00.000+0000")
}
]
}
]
}
},
{
"date" : {
"$_internalExprEq" : ISODate("2022-05-30T00:00:00.000+0000")
}
},
{
"deletedAt" : {
"$_internalExprEq" : null
}
},
{
"groupId" : {
"$_internalExprEq" : ObjectId("627f7821c5e9de1b328ea918")
}
}
]
},
"optimizedPipeline" : true,
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"$expr" : {
"$and" : [
{
"$eq" : [
"$groupId",
{
"$const" : ObjectId("627f7821c5e9de1b328ea918")
}
]
},
{
"$eq" : [
"$deletedAt",
{
"$const" : null
}
]
},
{
"$eq" : [
"$date",
{
"$const" : ISODate("2022-05-30T00:00:00.000+0000")
}
]
}
]
}
},
{
"date" : {
"$_internalExprEq" : ISODate("2022-05-30T00:00:00.000+0000")
}
},
{
"deletedAt" : {
"$_internalExprEq" : null
}
},
{
"groupId" : {
"$_internalExprEq" : ObjectId("627f7821c5e9de1b328ea918")
}
}
]
},
"direction" : "forward"
},
"rejectedPlans" : [
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1.0,
"executionTimeMillis" : 25.0,
"totalKeysExamined" : 0.0,
"totalDocsExamined" : 28851.0,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"$expr" : {
"$and" : [
{
"$eq" : [
"$groupId",
{
"$const" : ObjectId("627f7821c5e9de1b328ea918")
}
]
},
{
"$eq" : [
"$deletedAt",
{
"$const" : null
}
]
},
{
"$eq" : [
"$date",
{
"$const" : ISODate("2022-05-30T00:00:00.000+0000")
}
]
}
]
}
},
{
"date" : {
"$_internalExprEq" : ISODate("2022-05-30T00:00:00.000+0000")
}
},
{
"deletedAt" : {
"$_internalExprEq" : null
}
},
{
"groupId" : {
"$_internalExprEq" : ObjectId("627f7821c5e9de1b328ea918")
}
}
]
},
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 3.0,
"works" : 28853.0,
"advanced" : 1.0,
"needTime" : 28851.0,
"needYield" : 0.0,
"saveState" : 28.0,
"restoreState" : 28.0,
"isEOF" : 1.0,
"direction" : "forward",
"docsExamined" : 28851.0
}
},
"command" : {
"aggregate" : "LESSONS",
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$groupId",
ObjectId("627f7821c5e9de1b328ea918")
]
},
{
"$eq" : [
"$deletedAt",
null
]
},
{
"$eq" : [
"$date",
ISODate("2022-05-30T00:00:00.000+0000")
]
}
]
}
}
}
],
"cursor" : {
},
"$db" : "TEST_DB"
},
"serverInfo" : {
"host" : "nitro",
"port" : 2717.0,
"version" : "5.0.8",
"gitVersion" : "c87e1c23421bf79614baf500fda6622bd90f674e"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600.0,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600.0,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600.0,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600.0,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600.0,
"internalQueryProhibitBlockingMergeOnMongoS" : 0.0,
"internalQueryMaxAddToSetBytes" : 104857600.0,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600.0
},
"ok" : 1.0,
"$clusterTime" : {
"clusterTime" : Timestamp(1652524646, 1),
"signature" : {
"hash" : BinData(0, "AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1652524646, 1)
}