Details
-
Bug
-
Resolution: Unresolved
-
Major - P3
-
None
-
7.0.1
-
None
-
None
-
Server Triage
-
ALL
Description
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' }`.