[SERVER-33303] Modifying indexes impacts application of index filters Created: 13/Feb/18  Updated: 29/Oct/23  Resolved: 07/Nov/18

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 4.1.5

Type: Bug Priority: Major - P3
Reporter: Chris Harris Assignee: Ian Boros
Resolution: Fixed Votes: 5
Labels: QFB, storch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
Documented
is documented by DOCS-12387 Docs for SERVER-33303: Modifying inde... Closed
Problem/Incident
Related
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v4.0, v3.6
Steps To Reproduce:

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"
}

Sprint: Query 2018-10-22, Query 2018-11-05, Query 2018-11-19
Participants:
Case:
Linked BF Score: 13

 Description   

According to the documentation:

Index filters exist for the duration of the server process and do not persist after shutdown. MongoDB also provides a command to manually remove filters.

While a given index filter does persist across index creation and drops, its application towards queries does seem to be influenced by such actions.



 Comments   
Comment by David Storch [ 07/Nov/18 ]

ian.boros, did you mean to once again close this ticket as fixed?

Comment by Githook User [ 06/Nov/18 ]

Author:

{'name': 'Ian Boros', 'email': 'ian.boros@10gen.com'}

Message: SERVER-33303 Add stable plan cache key and use for index filters
Branch: master
https://github.com/mongodb/mongo/commit/50630d5c4ea5f731bdfd95b2dab5ab4e9620fd10

Comment by Githook User [ 06/Nov/18 ]

Author:

{'name': 'Ian Boros', 'email': 'ian.boros@10gen.com'}

Message: Revert "SERVER-33303 Add stable plan cache key and use for index filters"

This reverts commit 36d4668e854d8bd17e8b684dbbf42b3b0903bbe7.
Branch: master
https://github.com/mongodb/mongo/commit/84df94224aa86213250fa7e5e8d3a1ca6a71ac1b

Comment by Githook User [ 06/Nov/18 ]

Author:

{'name': 'Ian Boros', 'email': 'ian.boros@10gen.com'}

Message: SERVER-33303 Add stable plan cache key and use for index filters
Branch: master
https://github.com/mongodb/mongo/commit/36d4668e854d8bd17e8b684dbbf42b3b0903bbe7

Comment by Ian Boros [ 08/Oct/18 ]

Looks like this is caused by the fact that index filters use a query's "plan cache key" as a key in the mapping from query shape -> allowed indices. The plan cache key encodes information about which indexes the query can use, and is therefore catalog dependent. This means that the query {x: 3} will have a different cache key after an index is dropped or created, and therefore, map to a different (non-existent) index filter. This explains why index filters may not get applied after a catalog change, as well as why it's possible to see "duplicate" entries.

I think to solve this we'll need some kind of "catalog agnostic" key for a query, but I'm going to talk to some other people on the team before I take that approach.

Comment by Chris Harris [ 26/Feb/18 ]

As asya mentioned, this is a correctness issue with respect to the filters not being applied although they are present.

In addition to the applyiing -> not applying behavior in the previous comment, the reproduction steps also show not applying -> applying again as well (see the "But now after creating a new index it is being applied again" section).

I went ahead and confirmed that the behavior of the reproduction steps is the same in the log file (eg not specific to explain) on 3.4.3:

2018-02-26T12:25:21.051-0600 I COMMAND  [conn12] CMD: drop if.foo
 
2018-02-26T12:25:28.360-0600 I INDEX    [conn12] build index on: if.foo properties: { v: 2, key: { x: 1.0 }, name: "x_1", ns: "if.foo" }
2018-02-26T12:25:28.390-0600 I INDEX    [conn12] build index on: if.foo properties: { v: 2, key: { x: 1.0, y: 1.0 }, name: "x_1_y_1", ns: "if.foo" }
2018-02-26T12:25:28.435-0600 I COMMAND  [conn12] command if.$cmd appName: "MongoDB Shell" command: createIndexes { createIndexes: "foo", indexes: [ { key: { x: 1.0 }, name: "x_1" }, { key: { x: 1.0, y: 1.0 }, name: "x_1_y_1" } ] } numYields:0 reslen:98 locks:{ Global: { acquireCount: { r: 4, w: 4 } }, Database: { acquireCount: { w: 3, W: 1 } }, Collection: { acquireCount: { w: 1 } }, Metadata: { acquireCount: { w: 3 } }, oplog: { acquireCount: { w: 3 } } } protocol:op_command 175ms
 
2018-02-26T12:25:35.841-0600 I QUERY    [conn12] Index filter set on if.foo query: { x: 3.0 } sort: {} projection: {} indexes: [ { x: 1.0, y: 1.0 } ]
 
2018-02-26T12:25:35.841-0600 I COMMAND  [conn12] command if.foo appName: "MongoDB Shell" command: planCacheSetFilter { planCacheSetFilter: "foo", query: { x: 3.0 }, indexes: [ { x: 1.0, y: 1.0 } ] } numYields:0 reslen:22 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_command 0ms
 
2018-02-26T12:25:44.811-0600 I COMMAND  [conn12] command if.foo appName: "MongoDB Shell" command: find { find: "foo", filter: { x: 3.0 } } planSummary: IXSCAN { x: 1, y: 1 } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:79 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_command 0ms
 
2018-02-26T12:25:50.271-0600 I COMMAND  [conn12] command if.foo appName: "MongoDB Shell" command: dropIndexes { deleteIndexes: "foo", index: { x: 1.0, y: 1.0 } } numYields:0 reslen:39 locks:{ Global: { acquireCount: { r: 2, w: 2 } }, Database: { acquireCount: { w: 1, W: 1 } }, Metadata: { acquireCount: { w: 1 } }, oplog: { acquireCount: { w: 1 } } } protocol:op_command 4ms
 
2018-02-26T12:26:02.772-0600 I COMMAND  [conn12] command if.foo appName: "MongoDB Shell" command: find { find: "foo", filter: { x: 3.0 } } planSummary: IXSCAN { x: 1 } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:79 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_command 2ms
 
2018-02-26T12:26:09.467-0600 I COMMAND  [conn12] command if.$cmd appName: "MongoDB Shell" command: createIndexes { createIndexes: "foo", indexes: [ { key: { x: 1.0, z: 1.0 }, name: "x_1_z_1" } ] } numYields:0 reslen:98 locks:{ Global: { acquireCount: { r: 2, w: 2 } }, Database: { acquireCount: { w: 1, W: 1 } }, Collection: { acquireCount: { w: 1 } }, Metadata: { acquireCount: { w: 1 } }, oplog: { acquireCount: { w: 1 } } } protocol:op_command 41ms
 
2018-02-26T12:26:10.905-0600 I COMMAND  [conn12] command if.foo appName: "MongoDB Shell" command: find { find: "foo", filter: { x: 3.0 } } planSummary: COLLSCAN keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:79 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_command 0ms

Comment by Asya Kamsky [ 23/Feb/18 ]

The issue is that of correctness. The index filters are not applied correctly until they are re-created, so it's either that they are somehow getting invalidated when they shouldn't or something else is wrong.

db.runCommand( {  planCacheListFilters: 'foo' } )
{
	"filters" : [
		{
			"query" : {
				"x" : 3,
				"y" : 4
			},
			"sort" : {
 
			},
			"projection" : {
 
			},
			"indexes" : [
				{
					"x" : 1
				}
			]
		},
		{
			"query" : {
				"x" : 3,
				"y" : 4
			},
			"sort" : {
 
			},
			"projection" : {
 
			},
			"indexes" : [
				{
					"x" : 1
				}
			]
		}
	],
	"ok" : 1
}
// note the same filter shows up twice but seems to have no effect
db.foo.explain(true).find({x:3, y:4})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.foo",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"x" : {
						"$eq" : 3
					}
				},
				{
					"y" : {
						"$eq" : 4
					}
				}
			]
		},
		"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" : [
						"[4.0, 4.0]"
					]
				}
			}
		},
// ... note that indexFilterSet evaluated to false and the index in the filter was not winning plan (it was in rejected plans)

Creating the index filter again added it so that now three identical looking filters showed up and now the indexFilterSet was shown as true and correct index was used.

Comment by Ian Whalen (Inactive) [ 23/Feb/18 ]

christopher.harris the index filters and index catalog are intentionally independent and key pattern/index name-based.
Is this just something you noticed or is there some user impact you're running into? Otherwise we're inclined to resolve as Works As Designed.

Generated at Thu Feb 08 04:32:58 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.