-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Index Maintenance
-
ALL
-
-
Query 2019-12-30
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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
-
-
- Closed
-