I am trying MongoDB with a dataset about the company profile margin for learning purpose. Here is the sample document
{
"parent_comp" : 1
"child_comp" : 101
"profit" : NumberLong(70320020)
}
I have created two indexes i.e one on child_comp field and the other one is a compound index with parent_comp, child_comp, and last_outage_timestamp.
For the below query, I executed the explain command to see the query plan.
MongoDB Enterprise > db.data.find({ "$and" : [{ "parent_comp" : 951, "child_comp" : 9351, "profit" : { "$gte" : { "$numberLong" : "500000000" } } }, { "profit" : { "$lte" : { "$numberLong" : "1000000000" } } }] }).sort({"profit" : 1}).limit(3).explain();
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.data",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"child_comp" : {
"$eq" : 9351
}
},
{
"parent_comp" : {
"$eq" : 951
}
},
{
"profit" : {
"$lte" : {
"$numberLong" : "1000000000"
}
}
},
{
"profit" : {
"$gte" : {
"$numberLong" : "500000000"
}
}
}
]
},
"queryHash" : "B570EF0C",
"planCacheKey" : "187EF74B",
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"child_comp" : {
"$eq" : 9351
}
},
{
"parent_comp" : {
"$eq" : 951
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"profit" : 1
},
"indexName" : "profit_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"profit" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"profit" : [
"[{ $numberLong: \"500000000\" }, { $numberLong: \"1000000000\" }]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"profit" : 1
},
"limitAmount" : 3,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"parent_comp" : {
"$eq" : 951
}
},
{
"profit" : {
"$lte" : {
"$numberLong" : "1000000000"
}
}
},
{
"profit" : {
"$gte" : {
"$numberLong" : "500000000"
}
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"child_comp" : 1
},
"indexName" : "child_comp_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"child_comp" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"child_comp" : [
"[9351.0, 9351.0]"
]
}
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 3,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"parent_comp" : 1,
"child_comp" : 1,
"profit" : 1
},
"indexName" : "parent_comp_1_child_comp_1_profit_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"parent_comp" : [ ],
"child_comp" : [ ],
"profit" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"parent_comp" : [
"[951.0, 951.0]"
],
"child_comp" : [
"[9351.0, 9351.0]"
],
"profit" : [
"[{ $numberLong: \"500000000\" }, { $numberLong: \"1000000000\" }]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "localhost",
"port" : 27017,
"version" : "4.2.8",
"gitVersion" : "43d25888249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1
}
As you can see winning plan used single index instead of compound index. So could you please let me know why compound index was not used.
