Loading...

Type: Bug
Resolution: Unresolved
Priority: Minor - P4
Fix Version/s: None
Affects Version/s: 8.0.14
Component/s: None
Labels:
None

Assigned Teams:

Server Triage
Operating System:
ALL
Steps To Reproduce:
Hide

Make a collection with the following indexes:

{ status: 1, ignoreReason: 1, arr1: 1 }

{ status: 1, ignoreReason: 1, arr2: 1 }

{ status: 1, code: 1, ignoreReason: 1 }

{ status: 1, ignoreReason: 1, unrelatedField: 1 }

Run the following queries:

{
$or: [

{ $or: [\{ arr1: [] }
, { arr2: [] }],
status:
{ $in: ["STATUS_1", "STATUS_2", "STATUS_3"], }
,
},
{
status:
{ $in: ["STATUS_4", "STATUS_5", "STATUS_6"], }
,
},

{ code: \{ $ne: null }
,
status: null,
},
],
ignoreReason: null,
}

{
$or: [

{ $or: [\{ arr1: [] }
, { arr2: [] }],
status:
{ $in: ["STATUS_1", "STATUS_2", "STATUS_3"], }
,
ignoreReason: null,
},
{
status:
{ $in: ["STATUS_4", "STATUS_5", "STATUS_6"], }
,
ignoreReason: null,
},

{ code: \{ $ne: null }
,
status: null,
ignoreReason: null,
},
],
}

When running the second query my database takes 88ms, examines 16 documents, and returns 14, while using the following plan:

"winningPlan": {
"isCached": false,
"stage": "SUBPLAN",
"inputStage":
{ "stage": "PROJECTION_SIMPLE", "transformBy": \{ "_id": 1 }
,
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "OR",
"inputStages": [
{
"stage": "IXSCAN",
"keyPattern":
{ "status": 1, "ignoreReason": 1, "arr1": 1 }
,
"indexName": "status_1_ignoreReason_1_arr1_1",
"isMultiKey": true,
"multiKeyPaths":
{ "status": [], "ignoreReason": [], "arr1": [ "arr1" ] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": [ "[\"STATUS_4\", \"STATUS_4\"]", "[\"STATUS_5\", \"STATUS_5\"]", "[\"STATUS_6\", \"STATUS_6\"]" ], "ignoreReason": [ "[null, null]" ], "arr1": [ "[MinKey, MaxKey]" ] }
},
{
"stage": "OR",
"inputStages": [
{
"stage": "FETCH",
"filter": {
"arr1":
{ "$eq": [] }
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern":
{ "status": 1, "ignoreReason": 1, "arr1": 1 }
,
"indexName": "status_1_ignoreReason_1_arr1_1",
"isMultiKey": true,
"multiKeyPaths":
{ "status": [], "ignoreReason": [], "arr1": [ "arr1" ] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": [ "[\"STATUS_1\", \"STATUS_1\"]", "[\"STATUS_2\", \"STATUS_2\"]", "[\"STATUS_3\", \"STATUS_3\"]" ], "ignoreReason": [ "[null, null]" ], "arr1": [ "[undefined, undefined]", "[[], []]" ] }
}
},
{
"stage": "FETCH",
"filter":
{ "arr2": \{ "$eq": [] }
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern":
{ "status": 1, "ignoreReason": 1, "arr2": 1 }
,
"indexName": "status_1_ignoreReason_1_arr2_1",
"isMultiKey": true,
"multiKeyPaths":
{ "status": [], "ignoreReason": [], "arr2": ["arr2"] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": [ "[\"STATUS_1\", \"STATUS_1\"]", "[\"STATUS_2\", \"STATUS_2\"]", "[\"STATUS_3\", \"STATUS_3\"]" ], "ignoreReason": [ "[null, null]" ], "arr2": [ "[undefined, undefined]", "[[], []]" ] }
}
}
]
},
{
"stage": "IXSCAN",
"keyPattern":
{ "status": 1, "bookingId": 1, "ignoreReason": 1 }
,
"indexName": "status_1_bookingId_1_ignoreReason_1",
"isMultiKey": false,
"multiKeyPaths":
{ "status": [], "bookingId": [], "ignoreReason": [] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": [ "[null, null]" ], "bookingId": [ "[MinKey, null)", "(null, MaxKey]" ], "ignoreReason": [ "[null, null]" ] }
}
]
}
}
}
},

But when using the version with ignoreReason: null separate, it uses the much less efficient:

"winningPlan":
{ "isCached": false, "stage": "PROJECTION_SIMPLE", "transformBy": \{ "_id": 1 }
,
"inputStage": {
"stage": "FETCH",
"filter":
{ "ignoreReason": \{ "$eq": null }
},
"inputStage": {
"stage": "OR",
"inputStages": [
{
"stage": "FETCH",
"filter": {
"$or": [
{
"arr1":
{ "$eq": [] }
},
{ "arr2":
{ "$eq": [] }
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern":
{ "status": 1, "ignoreReason": 1, "unrelated": 1 }
,
"indexName": "status_1_ignoreReason_1_unrelated_1",
"isMultiKey": false,
"multiKeyPaths":
{ "status": [], "ignoreReason": [], "unrelated": [] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": [ "[\"STATUS_1\", \"STATUS_1\"]", "[\"STATUS_2\", \"STATUS_2\"]", "[\"STATUS_3\", \"STATUS_3\"]" ], "ignoreReason": [ "[null, null]" ], "unrelated": [ "[MinKey, MaxKey]" ] }
}
},
{
"stage": "IXSCAN",
"keyPattern":
{ "status": 1, "code": 1, "ignoreReason": 1 }
,
"indexName": "status_1_code_1_ignoreReason_1",
"isMultiKey": false,
"multiKeyPaths":
{ "status": [], "code": [], "ignoreReason": [] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": ["[null, null]"], "code": [ "[MinKey, null)", "(null, MaxKey]" ], "ignoreReason": [ "[null, null]" ] }
},
{
"stage": "IXSCAN",
"keyPattern":
{ "status": 1 }
,
"indexName": "status_1",
"isMultiKey": false,
"multiKeyPaths":
{ "status": [] }
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{ "status": [ "[\"STATUS_4\", \"STATUS_4\"]", "[\"STATUS_5\", \"STATUS_5\"]", "[\"STATUS_6\", \"STATUS_6\"]" ] }
}
]
}
}
},

Which ends up scanning 71k documents to still only return the same 14.

Note: the above queries and winning plans are the actual ones used and reported in our database, but I have redacted the actual field names and values used. If those details would be relevant I am happy to talk to an engineer in a non-public forum.
Show
Make a collection with the following indexes: { status: 1, ignoreReason: 1, arr1: 1 } { status: 1, ignoreReason: 1, arr2: 1 } { status: 1, code: 1, ignoreReason: 1 } { status: 1, ignoreReason: 1, unrelatedField: 1 } Run the following queries: { $or: [ { $or: [\{ arr1: [] } , { arr2: [] }], status: { $in: ["STATUS_1", "STATUS_2", "STATUS_3"], } , }, { status: { $in: ["STATUS_4", "STATUS_5", "STATUS_6"], } , }, { code: \{ $ne: null } , status: null, }, ], ignoreReason: null, } { $or: [ { $or: [\{ arr1: [] } , { arr2: [] }], status: { $in: ["STATUS_1", "STATUS_2", "STATUS_3"], } , ignoreReason: null, }, { status: { $in: ["STATUS_4", "STATUS_5", "STATUS_6"], } , ignoreReason: null, }, { code: \{ $ne: null } , status: null, ignoreReason: null, }, ], } When running the second query my database takes 88ms, examines 16 documents, and returns 14, while using the following plan: "winningPlan": { "isCached": false, "stage": "SUBPLAN", "inputStage": { "stage": "PROJECTION_SIMPLE", "transformBy": \{ "_id": 1 } , "inputStage": { "stage": "FETCH", "inputStage": { "stage": "OR", "inputStages": [ { "stage": "IXSCAN", "keyPattern": { "status": 1, "ignoreReason": 1, "arr1": 1 } , "indexName": "status_1_ignoreReason_1_arr1_1", "isMultiKey": true, "multiKeyPaths": { "status": [], "ignoreReason": [], "arr1": [ "arr1" ] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": [ "[\"STATUS_4\", \"STATUS_4\"]", "[\"STATUS_5\", \"STATUS_5\"]", "[\"STATUS_6\", \"STATUS_6\"]" ], "ignoreReason": [ "[null, null]" ], "arr1": [ "[MinKey, MaxKey]" ] } }, { "stage": "OR", "inputStages": [ { "stage": "FETCH", "filter": { "arr1": { "$eq": [] } }, "inputStage": { "stage": "IXSCAN", "keyPattern": { "status": 1, "ignoreReason": 1, "arr1": 1 } , "indexName": "status_1_ignoreReason_1_arr1_1", "isMultiKey": true, "multiKeyPaths": { "status": [], "ignoreReason": [], "arr1": [ "arr1" ] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": [ "[\"STATUS_1\", \"STATUS_1\"]", "[\"STATUS_2\", \"STATUS_2\"]", "[\"STATUS_3\", \"STATUS_3\"]" ], "ignoreReason": [ "[null, null]" ], "arr1": [ "[undefined, undefined]", "[[], []]" ] } } }, { "stage": "FETCH", "filter": { "arr2": \{ "$eq": [] } }, "inputStage": { "stage": "IXSCAN", "keyPattern": { "status": 1, "ignoreReason": 1, "arr2": 1 } , "indexName": "status_1_ignoreReason_1_arr2_1", "isMultiKey": true, "multiKeyPaths": { "status": [], "ignoreReason": [], "arr2": ["arr2"] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": [ "[\"STATUS_1\", \"STATUS_1\"]", "[\"STATUS_2\", \"STATUS_2\"]", "[\"STATUS_3\", \"STATUS_3\"]" ], "ignoreReason": [ "[null, null]" ], "arr2": [ "[undefined, undefined]", "[[], []]" ] } } } ] }, { "stage": "IXSCAN", "keyPattern": { "status": 1, "bookingId": 1, "ignoreReason": 1 } , "indexName": "status_1_bookingId_1_ignoreReason_1", "isMultiKey": false, "multiKeyPaths": { "status": [], "bookingId": [], "ignoreReason": [] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": [ "[null, null]" ], "bookingId": [ "[MinKey, null)", "(null, MaxKey]" ], "ignoreReason": [ "[null, null]" ] } } ] } } } }, But when using the version with ignoreReason: null separate, it uses the much less efficient: "winningPlan": { "isCached": false, "stage": "PROJECTION_SIMPLE", "transformBy": \{ "_id": 1 } , "inputStage": { "stage": "FETCH", "filter": { "ignoreReason": \{ "$eq": null } }, "inputStage": { "stage": "OR", "inputStages": [ { "stage": "FETCH", "filter": { "$or": [ { "arr1": { "$eq": [] } }, { "arr2": { "$eq": [] } } ] }, "inputStage": { "stage": "IXSCAN", "keyPattern": { "status": 1, "ignoreReason": 1, "unrelated": 1 } , "indexName": "status_1_ignoreReason_1_unrelated_1", "isMultiKey": false, "multiKeyPaths": { "status": [], "ignoreReason": [], "unrelated": [] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": [ "[\"STATUS_1\", \"STATUS_1\"]", "[\"STATUS_2\", \"STATUS_2\"]", "[\"STATUS_3\", \"STATUS_3\"]" ], "ignoreReason": [ "[null, null]" ], "unrelated": [ "[MinKey, MaxKey]" ] } } }, { "stage": "IXSCAN", "keyPattern": { "status": 1, "code": 1, "ignoreReason": 1 } , "indexName": "status_1_code_1_ignoreReason_1", "isMultiKey": false, "multiKeyPaths": { "status": [], "code": [], "ignoreReason": [] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": ["[null, null]"], "code": [ "[MinKey, null)", "(null, MaxKey]" ], "ignoreReason": [ "[null, null]" ] } }, { "stage": "IXSCAN", "keyPattern": { "status": 1 } , "indexName": "status_1", "isMultiKey": false, "multiKeyPaths": { "status": [] } , "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "status": [ "[\"STATUS_4\", \"STATUS_4\"]", "[\"STATUS_5\", \"STATUS_5\"]", "[\"STATUS_6\", \"STATUS_6\"]" ] } } ] } } }, Which ends up scanning 71k documents to still only return the same 14. Note: the above queries and winning plans are the actual ones used and reported in our database, but I have redacted the actual field names and values used. If those details would be relevant I am happy to talk to an engineer in a non-public forum.
CAR Domain/s:
None

Aha! Reference:
None
Tracking Level:
None
Risk Status:
None
Exec Notes:
None
Goal Name(s):
None
Goal Link:
None

Running a semi-complicated query with an "$or" with multiple conditions and a static condition that should be applied regardless uses different indexes when the static condition is unrolled into the "$or". If I include the condition in every branch of the "$or" query then it uses optimal indexes (each "$or" subquery uses the most fitting index, and then it joins the results), but when I have the constant conditional outside then it uses an unrelated index for part of it, and ends up being way less efficient overall.

This is very strange to me. I would have expected that they two queries would be have identically in all cases.

is related to

SERVER-98821 Index not used in a sub-or query

Backlog

Details

Description

Attachments

Issue Links

Activity

People

Dates