-
Type: Bug
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: 7.0.1, 8.0.0-rc0
-
Component/s: None
-
Query Optimization
-
ALL
-
(copied to CRM)
I'm observing a weird behaviour with CWI's, where queries containing filters beyond those covered by the CWI, results in the `indexBounds` to revert to '[MinKey, MaxKey]`.
Say I have CWI:
db.cwi.createIndex({ TenantId: 1, 'CustomFields.$**': 1, Date: 1 })
If I query only using fields contained in the CWI:
db.cwi.explain().find({ TenantId: 1, 'CustomFields.Prop': 2, Date: { $gte: ISODate('2023-01-01'), $lte: ISODate('2024-01-01') }, })
I get this winning query plan:
{ "stage": "FETCH", "planNodeId": new NumberInt("2"), "inputStage": { "stage": "IXSCAN", "planNodeId": new NumberInt("1"), "keyPattern": { "TenantId": new NumberInt("1"), "$_path": new NumberInt("1"), "CustomFields.Prop": new NumberInt("1"), "Date": new NumberInt("1") }, "indexName": "TenantId_1_CustomFields.$**_1_Date_1", "isMultiKey": false, "multiKeyPaths": { "TenantId": [ ], "$_path": [ ], "CustomFields.Prop": [ ], "Date": [ ] }, "isUnique": false, "isSparse": true, "isPartial": false, "indexVersion": new NumberInt("2"), "direction": "forward", "indexBounds": { "TenantId": [ "[1, 1]" ], "$_path": [ "[\"CustomFields.Prop\", \"CustomFields.Prop\"]" ], "CustomFields.Prop": [ "[2, 2]" ], "Date": [ "[new Date(1672531200000), new Date(1704067200000)]" ] } } }
But if I add an extra criteria, say `
{ Foo: 'Bar' }`:
db.cwi.explain().find({ TenantId: 1, 'CustomFields.Prop': 2, Date: { $gte: ISODate('2023-01-01'), $lte: ISODate('2024-01-01') }, Foo: 'Bar' })
I suddenly get a query plan where the index bounds on `CustomFields.Prop` and `Date` are both `[MinKey, MaxKey]`:
{ "stage": "FETCH", "planNodeId": new NumberInt("2"), "filter": { "$and": [ { "CustomFields.Prop": { "$eq": new NumberInt("2") } }, { "Date": { "$lte": new ISODate("2024-01-01T00:00:00.000Z") } }, { "Date": { "$gte": new ISODate("2023-01-01T00:00:00.000Z") } }, { "Foo": { "$eq": "Bar" } } ] }, "inputStage": { "stage": "IXSCAN", "planNodeId": new NumberInt("1"), "keyPattern": { "TenantId": new NumberInt("1"), "$_path": new NumberInt("1"), "Date": new NumberInt("1") }, "indexName": "TenantId_1_CustomFields.$**_1_Date_1", "isMultiKey": false, "multiKeyPaths": { "TenantId": [ ], "$_path": [ ], "Date": [ ] }, "isUnique": false, "isSparse": true, "isPartial": false, "indexVersion": new NumberInt("2"), "direction": "forward", "indexBounds": { "TenantId": [ "[1, 1]" ], "$_path": [ "[MinKey, MaxKey]" ], "Date": [ "[MinKey, MaxKey]" ] } } }
Note the index bounds and the `filter` on the outer fetch stage which is suddenly doing the work of filtering on `CustomFields.Prop` and `Date`.
I see no reason why this is the case, an optimal query plan would be as before with the CWI filtering by all fields it covers, and then only a filter on the fetch stag on `
{ Foo: 'Bar' }`.