-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
None
-
None
-
None
-
None
-
None
-
None
SERVER-103601 improved this by pushing down the filter partially before the fetch:
db.a.drop()
db.a.createIndex({c:1, a: 1})
db.a.explain().find({a:1, b: 1}).sort({c: 1})
8.3 output:
winningPlan: {
isCached: false,
stage: 'FETCH',
filter: {
'$and': [ { a: { '$eq': 1 } }, { b: { '$eq': 1 } } ]
},
nss: 'test.a',
inputStage: {
stage: 'IXSCAN',
indexBounds: { c: [ '[MinKey, MaxKey]' ], a: [ '[MinKey, MaxKey]' ] }
}
},
9.0 output (after SERVER-103601):
winningPlan: {
isCached: false,
stage: 'FETCH',
filter: { b: { '$eq': 1 } },
nss: 'test.a',
inputStage: {
stage: 'IXSCAN',
filter: { a: { '$eq': 1 } },
indexBounds: { c: [ '[MinKey, MaxKey]' ], a: [ '[MinKey, MaxKey]' ] },
...
}
},
Note that partial a: 1 filter before the fetch.
Ideally we would use a skip scan (indexBounds: { c: [ '[MinKey, MaxKey]' ], a: [ '[1, 1]' ]) instead of a residual filter, reducing the number of scanned keys.
- is related to
-
SERVER-103601 Optimize AND trees for full IXSCAN plans
-
- Closed
-