Details
-
Bug
-
Resolution: Duplicate
-
Major - P3
-
None
-
None
-
ALL
-
-
Query 2019-12-30
Description
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)
Attachments
Issue Links
- duplicates
-
SERVER-27646 covered index should be used when null inequality is in the query and index is not multikey
-
- Closed
-