-
Type: Improvement
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
None
Related issues:
- SERVER-32549**
SERVER-38138
**
Summary:
**$expr inside $match stage seems to only respect index when using $eq operator. Other operators (e.g. $gt, $gte, $lt, $lte, $indexOfCP) would result collection scan.
I decided to create separate ticket because it looks to me as a bug rather than enhancement, since $expr operator within $match stage have no alternatives sometimes and for large datasets it becomes unusable if index is ignored.
Documentation states that "$expr does not support multikey indexes" which probably implies that is should support single field indexes.
Tested with ascending index on a single field for date, int and string for $gt, $gte, $lt, $lte, $indexOfCP operators. Both find and aggregate would result collection scan. Only $eq would result ixscan.
Tested on community version 4.0.8 / 4.0.9 and enterprise version 4.0.8, against single instance and replica set (distmod: "rhel70").
Steps to reproduce:
Sample set:
const size = 100000; [...new Array(size)].map((x, i) => ({ num: i, str: 'val_' + i, date: new Date(new Date() + (size/2 - i)*1000*60) }))
Index definitions:
createIndex({num: 1}, {name: `${colName}_num_asc` }); createIndex({str: 1}, {name: `${colName}_str_asc` }); createIndex({date: 1}, {name: `${colName}_date_asc` });
Find query:
find({$expr: {$gt: ['$num', 90000]}}, {explain: true}).limit(5)
Aggregation:
aggregate( [{$match: {$expr: {$gt: ['$num', 90000]}}}, {$limit: 5}], { explain: true })
Expected result:
ixscan
Actual result:
collscan
Details:
Explained and profiled queries are attached.
'Good-*' attachements - result of $eq operator.
'Bad-*' attachments - result of $gt operator.
- duplicates
-
SERVER-34926 allow $expr with comparison bounded on both sides with same type to use a non-multikey index
- Closed