-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
Consider the query
db.test.explain().find({
$or: [
{a: {$in: [3, 7]}},
{b: {$in: [4, 6]}}
],
{d: {$exists: false}}
}).sort({c: 1}));
And indexes
{a: 1, c: 1} {b: 1, c: 1}The ideal plan for this query should include 4 index scans with point bounds for a or b, such that they each provide a sort on c and a SORT_MERGE.
However to explode the index scans for sort and end up with a plan that contains a blocking sort
SORT - - FETCH - OR - 2x IXSCAN:
winningPlan: {
stage: 'SORT',
<..>
inputStage: {
stage: 'FETCH',
filter: { e: { '$not': { '$exists': true } } },
inputStage: {
stage: 'OR',
inputStages: [
{
stage: 'IXSCAN',
keyPattern: { a: 1, c: 1 },
<..>
indexBounds: { a: [ '[3, 3]', '[7, 7]' ], c: [ '[MinKey, MaxKey]' ] }
},
{
stage: 'IXSCAN',
keyPattern: { b: 1, c: 1 },
<..>
indexBounds: { b: [ '[4, 4]', '[6, 6]' ], c: [ '[MinKey, MaxKey]' ] }
}
]
}
}
But if we remove predicate on d, everything works as expected: sort_merge and 4 ixscans.
winningPlan: {
stage: 'SUBPLAN',
inputStage: {
stage: 'FETCH',
inputStage: {
stage: 'SORT_MERGE',
inputStages: [
{
stage: 'IXSCAN',
keyPattern: { a: 1, c: 1 },
indexName: 'a_1_c_1',
<..>
indexBounds: { a: [ '[3, 3]' ], c: [ '[MinKey, MaxKey]' ] }
},
{
stage: 'IXSCAN',
keyPattern: { a: 1, c: 1 },
<..>
indexBounds: { a: [ '[7, 7]' ], c: [ '[MinKey, MaxKey]' ] }
},
{
stage: 'IXSCAN',
keyPattern: { b: 1, c: 1 },
<..>
indexBounds: { b: [ '[4, 4]' ], c: [ '[MinKey, MaxKey]' ] }
},
{
stage: 'IXSCAN',
keyPattern: { b: 1, c: 1 },
<..>
indexBounds: { b: [ '[6, 6]' ], c: [ '[MinKey, MaxKey]' ] }
}
]
}
}
I suspect this code to be the reason: https://github.com/mongodb/mongo/blob/5cf319032320a6c3aca95eeccf59d1ec65a2e910/src/mongo/db/query/planner_analysis.cpp#L201
When we traverse the query solution to check for explode for sort, we only go down through SHARDING_FILTER, OR and FETCH, but only if FETCH has a single IXSCAN child.
However in this case we have FETCH before the OR, so we fail this check and don't proceed with the traversal.