-
Type: Bug
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
Labels:None
-
Query Optimization
-
ALL
-
Hide
> db.foo.ensureIndex({'$**': 1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.find({a:1, b:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "queryHash" : "43CAB4C5", "planCacheKey" : "CEC1F6AF", "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "$_path" : 1, "a" : 1 }, "indexName" : "$**_1", "isMultiKey" : false, "multiKeyPaths" : { "$_path" : [ ], "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "$_path" : [ "[\"a\", \"a\"]" ], "a" : [ "[1.0, 1.0]" ] } } }, "rejectedPlans" : [ { "stage" : "FETCH", "filter" : { "a" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "$_path" : 1, "b" : 1 }, "indexName" : "$**_1", "isMultiKey" : false, "multiKeyPaths" : { "$_path" : [ ], "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "$_path" : [ "[\"b\", \"b\"]" ], "b" : [ "[1.0, 1.0]" ] } } } ] }, "serverInfo" : { "host" : "silversurfer-wsl", "port" : 27017, "version" : "4.4.3", "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13" }, "ok" : 1 }
Compare that to:
> db.foo.ensureIndex({a: 1}) { "createdCollectionAutomatically" : true, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.ensureIndex({b: 1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } > db.foo.find({a:1, b:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "queryHash" : "43CAB4C5", "planCacheKey" : "CEC1F6AF", "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : 1 }, "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[1.0, 1.0]" ] } } }, "rejectedPlans" : [ { "stage" : "FETCH", "filter" : { "a" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "b" : 1 }, "indexName" : "b_1", "isMultiKey" : false, "multiKeyPaths" : { "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "b" : [ "[1.0, 1.0]" ] } } }, { "stage" : "FETCH", "filter" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "inputStage" : { "stage" : "AND_SORTED", "inputStages" : [ { "stage" : "IXSCAN", "keyPattern" : { "a" : 1 }, "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[1.0, 1.0]" ] } }, { "stage" : "IXSCAN", "keyPattern" : { "b" : 1 }, "indexName" : "b_1", "isMultiKey" : false, "multiKeyPaths" : { "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "b" : [ "[1.0, 1.0]" ] } } ] } } ] }, "serverInfo" : { "host" : "silversurfer-wsl", "port" : 27017, "version" : "4.4.3", "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13" }, "ok" : 1 }
Show> db.foo.ensureIndex({'$**': 1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.find({a:1, b:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "queryHash" : "43CAB4C5", "planCacheKey" : "CEC1F6AF", "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "$_path" : 1, "a" : 1 }, "indexName" : "$**_1", "isMultiKey" : false, "multiKeyPaths" : { "$_path" : [ ], "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "$_path" : [ "[\"a\", \"a\"]" ], "a" : [ "[1.0, 1.0]" ] } } }, "rejectedPlans" : [ { "stage" : "FETCH", "filter" : { "a" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "$_path" : 1, "b" : 1 }, "indexName" : "$**_1", "isMultiKey" : false, "multiKeyPaths" : { "$_path" : [ ], "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "$_path" : [ "[\"b\", \"b\"]" ], "b" : [ "[1.0, 1.0]" ] } } } ] }, "serverInfo" : { "host" : "silversurfer-wsl", "port" : 27017, "version" : "4.4.3", "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13" }, "ok" : 1 } Compare that to: > db.foo.ensureIndex({a: 1}) { "createdCollectionAutomatically" : true, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.ensureIndex({b: 1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } > db.foo.find({a:1, b:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "queryHash" : "43CAB4C5", "planCacheKey" : "CEC1F6AF", "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : 1 }, "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[1.0, 1.0]" ] } } }, "rejectedPlans" : [ { "stage" : "FETCH", "filter" : { "a" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "b" : 1 }, "indexName" : "b_1", "isMultiKey" : false, "multiKeyPaths" : { "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "b" : [ "[1.0, 1.0]" ] } } }, { "stage" : "FETCH", "filter" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "inputStage" : { "stage" : "AND_SORTED", "inputStages" : [ { "stage" : "IXSCAN", "keyPattern" : { "a" : 1 }, "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[1.0, 1.0]" ] } }, { "stage" : "IXSCAN", "keyPattern" : { "b" : 1 }, "indexName" : "b_1", "isMultiKey" : false, "multiKeyPaths" : { "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "b" : [ "[1.0, 1.0]" ] } } ] } } ] }, "serverInfo" : { "host" : "silversurfer-wsl", "port" : 27017, "version" : "4.4.3", "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13" }, "ok" : 1 } -
Query Optimization 2021-03-08
Ideally wildcard indexes should be usable anywhere an infinite number of {a:1}, {b:1}, etc indexes would be. But right now a query like {a:1, b:1} will use AND_SORTED of two IX_SCANs, but it won't try to use the equivalent plan if you have a wildcard index
- is related to
-
SERVER-53930 AND_SORTED IX_SCAN plans should take advantage of ability to seek by recordid in indexes
- Backlog
-
SERVER-36521 Prevent allPaths indexes from generating self-intersecting plans
- Closed