Show
db.version()
db.foo.drop()
db.foo.createIndexes( [{x:1}, {x:1,y:1} ])
db.runCommand( { planCacheSetFilter: 'foo', query: {"x" : 3}, indexes: [ {x:1,y:1} ] } )
db.runCommand( { planCacheListFilters: 'foo' } )
db.foo.explain().find({x:3}).finish().queryPlanner.indexFilterSet
db.foo.explain().find({x:3}).finish().queryPlanner.winningPlan
db.foo.dropIndex({x:1,y:1})
db.runCommand( { planCacheListFilters: 'foo' } )
db.foo.explain().find({x:3}).finish().queryPlanner.indexFilterSet
db.foo.explain().find({x:3}).finish().queryPlanner.winningPlan
db.foo.createIndex({x:1, z:1})
db.runCommand( { planCacheListFilters: 'foo' } )
db.foo.explain().find({x:3}).finish().queryPlanner.indexFilterSet
db.foo.explain().find({x:3}).finish().queryPlanner.winningPlan
Output of running those commands:
MongoDB Enterprise > db.version()
3.6.2
MongoDB Enterprise > db.foo.drop()
true
MongoDB Enterprise > db.foo.createIndexes( [{x:1}, {x:1,y:1} ])
{
"createdCollectionAutomatically" : true,
"numIndexesBefore" : 1,
"numIndexesAfter" : 3,
"ok" : 1
}
MongoDB Enterprise > db.runCommand( { planCacheSetFilter: 'foo', query: {"x" : 3}, indexes: [ {x:1,y:1} ] } )
{ "ok" : 1 }
MongoDB Enterprise > db.runCommand( { planCacheListFilters: 'foo' } )
{
"filters" : [
{
"query" : {
"x" : 3
},
"sort" : {
},
"projection" : {
},
"indexes" : [
{
"x" : 1,
"y" : 1
}
]
}
],
"ok" : 1
}
MongoDB Enterprise > db.foo.explain().find({x:3}).finish().queryPlanner.indexFilterSet
true
MongoDB Enterprise > db.foo.explain().find({x:3}).finish().queryPlanner.winningPlan
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"x" : 1,
"y" : 1
},
"indexName" : "x_1_y_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"x" : [ ],
"y" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"x" : [
"[3.0, 3.0]"
],
"y" : [
"[MinKey, MaxKey]"
]
}
}
}
MongoDB Enterprise > db.foo.dropIndex({x:1,y:1})
{ "nIndexesWas" : 3, "ok" : 1 }
MongoDB Enterprise > db.runCommand( { planCacheListFilters: 'foo' } )
{
"filters" : [
{
"query" : {
"x" : 3
},
"sort" : {
},
"projection" : {
},
"indexes" : [
{
"x" : 1,
"y" : 1
}
]
}
],
"ok" : 1
}
MongoDB Enterprise > // Filter is still present above, expect it to be applied below but it is not:
MongoDB Enterprise > db.foo.explain().find({x:3}).finish().queryPlanner.indexFilterSet
false
MongoDB Enterprise > db.foo.explain().find({x:3}).finish().queryPlanner.winningPlan
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"x" : 1
},
"indexName" : "x_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"x" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"x" : [
"[3.0, 3.0]"
]
}
}
}
MongoDB Enterprise > db.foo.createIndex({x:1, z:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
MongoDB Enterprise > db.runCommand( { planCacheListFilters: 'foo' } )
{
"filters" : [
{
"query" : {
"x" : 3
},
"sort" : {
},
"projection" : {
},
"indexes" : [
{
"x" : 1,
"y" : 1
}
]
}
],
"ok" : 1
}
MongoDB Enterprise > // Nothing has changed with the index filter. But now after creating a new index it is being applied again (as originally expected):
MongoDB Enterprise > db.foo.explain().find({x:3}).finish().queryPlanner.indexFilterSet
true
MongoDB Enterprise > db.foo.explain().find({x:3}).finish().queryPlanner.winningPlan
{
"stage" : "COLLSCAN",
"filter" : {
"x" : {
"$eq" : 3
}
},
"direction" : "forward"
}
Major - P3