-
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' }`.