-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Index Maintenance
-
ALL
-
-
Query 2019-12-30
For queries that have $ne/$nin operators, they seem to be unable to use covered queries. This is seemingly not particularly useful for indexes that apply only to that field, but if the $ne is a compound index, we could potentially be dropping a dramatically large amount of data even with the $ne filter. For example, consider this query:
mongos> filter = { 'target._id':{ $in:idsList } }, actor:{ $ne:'system' }, published:{ $gte:moment().subtract(120, 'days' ).valueOf() } } mongos> var exp = db.activities.explain('executionStats') mongos> exp.find(filter, { _id:0, published:1 })
..."executionStats":{ "nReturned":117287, "executionTimeMillis":587, "totalKeysExamined":120551, "totalDocsExamined":117287, "executionStages":{ "stage":"SINGLE_SHARD", "nReturned":117287, "executionTimeMillis":587, "totalKeysExamined":120551, "totalDocsExamined":117287, "totalChildMillis":NumberLong(571), "shards":[ { "shardName":"beta-blend-d", "executionSuccess":true, "executionStages":{ "stage":"PROJECTION", "nReturned":117287, "executionTimeMillisEstimate":176, "works":120551, "advanced":117287, "needTime":3263, "needYield":0, "saveState":946, "restoreState":946, "isEOF":1, "invalidates":0, "transformBy":{ "_id":0, "published":1 }, "inputStage":{ "stage":"FETCH", "filter":{ "actor":{ "$not":{ "$eq":"system" } } }, "nReturned":117287, "executionTimeMillisEstimate":166, "works":120551, "advanced":117287, "needTime":3263, "needYield":0, "saveState":946, "restoreState":946, "isEOF":1, "invalidates":0, "docsExamined":117287, "alreadyHasObj":0, "inputStage":{ "stage":"IXSCAN", "nReturned":117287, "executionTimeMillisEstimate":151, "works":120551, "advanced":117287, "needTime":3263, "needYield":0, "saveState":946, "restoreState":946, "isEOF":1, "invalidates":0, "keyPattern":{ "target._id":1, "published":-1, "actor":1, "verb":1 }, "indexName":"target.id_1_published-1_actor_1_verb_1", "isMultiKey":true, "multiKeyPaths":{ "target._id":[ "target" ], "published":[ ], "actor":[ ], "verb":[ ] }, "isUnique":false, "isSparse":false, "isPartial":false, "indexVersion":2, "direction":"forward", "indexBounds":{ "target._id":[ ... // a bunch of target._id bounds ], "published":[ "[inf.0, 1565026916796.0]" ], "actor":[ "[MinKey, \"system\")", "(\"system\", MaxKey]" ], "verb":[ "[MinKey, MaxKey]" ] }, "keysExamined":120551, "seeks":3264, "dupsTested":117287, "dupsDropped":0, "seenInvalidated":0 } } } } ] } }, ... }
It seems like the fact that there is a FETCH stage in this plan (even though it will never eliminate any documents as the index scan prevents it) prevents us from being able to use a covered query (`published` is part of the index, so should not require examining any documents)
- duplicates
-
SERVER-27646 covered index should be used when null inequality is in the query and index is not multikey
- Closed