Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
Query Optimization
Description
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.