-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
Labels:
-
Query Optimization
db.temp.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.temp"
},
{
"v" : 2,
"key" : {
"a" : 1,
"b" : 1
},
"name" : "a_1_b_1",
"ns" : "test.temp"
},
{
"v" : 2,
"key" : {
"a" : 1,
"c" : 1
},
"name" : "a_1_c_1",
"ns" : "test.temp",
"partialFilterExpression" : {
"a" : 0
}
}
]
Here are queries and indexes they result in:
db.temp.explain().find({a:0},{_id:0,b:1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.temp",
"indexFilterSet" : false,
"parsedQuery" : {
"a" : {
"$eq" : 0
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"b" : 1
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"b" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[0.0, 0.0]"
],
"b" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"b" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"c" : 1
},
"indexName" : "a_1_c_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"c" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[0.0, 0.0]"
],
"c" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "Asyas-MacBook-Pro-2.local",
"port" : 27017,
"version" : "3.4.1",
"gitVersion" : "5e103c4f5583e2566a45d740225dc250baacfbd7"
},
"ok" : 1
}
test@127.0.0.1:27017(3.4.1) > db.temp.explain().find({a:0},{_id:0,c:1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.temp",
"indexFilterSet" : false,
"parsedQuery" : {
"a" : {
"$eq" : 0
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"c" : 1
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"c" : 1
},
"indexName" : "a_1_c_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"c" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[0.0, 0.0]"
],
"c" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"c" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"b" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[0.0, 0.0]"
],
"b" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "Asyas-MacBook-Pro-2.local",
"port" : 27017,
"version" : "3.4.1",
"gitVersion" : "5e103c4f5583e2566a45d740225dc250baacfbd7"
},
"ok" : 1
}
test@127.0.0.1:27017(3.4.1) > db.temp.explain().find({a:0},{_id:0,x:1,c:1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.temp",
"indexFilterSet" : false,
"parsedQuery" : {
"a" : {
"$eq" : 0
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"x" : 1,
"c" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"b" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[0.0, 0.0]"
],
"b" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"x" : 1,
"c" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"c" : 1
},
"indexName" : "a_1_c_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"c" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[0.0, 0.0]"
],
"c" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "Asyas-MacBook-Pro-2.local",
"port" : 27017,
"version" : "3.4.1",
"gitVersion" : "5e103c4f5583e2566a45d740225dc250baacfbd7"
},
"ok" : 1
}
I'm completely confused by the last one - it's uncovered compared to the one above but I don't see why it would use a:1,b:1 rather than a:1,c:1 which is "just as good" but is partial index with expression match and hence a smaller index and should be preferred.