-
Type: Bug
-
Resolution: Won't Do
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Aggregation Framework
-
None
-
ALL
I have a query which I believe can still be satisfied by the DISTINCT_SCAN and it ends up taking too long so I'm going to have to issue N distinct queries instead of using the aggregate framework like I expected to work.
The scenario is the following:
collection with index {one: 1, two: 1}
The following query uses a DISTINCT_SCAN as expected (comments inline how to force it to bail):
db.collection.aggregate([ {$match: {$or: [ {one: 'ID-1'} // uncomment the following line to use an IXSCAN // , {one: 'ID-2'} ]}}, // this actually only seems to bail if "two" is used in reverse, otherwise it still does use the index {$sort: {'one': 1, 'two': -1}}, {$group: {_id: '$one', record: {$first: '$$ROOT'}}}, ])
In our environment there are many thousands of records that are forced to be scanned through, and in the code I had written I was previously using $in and I thought that might have been the problem, but using $or instead still had the same issue. I did narrow it down to using the index in reverse order, and that means it would effectively be solved if I could use $last as described in https://jira.mongodb.org/browse/SERVER-37304 but since this is specifically using $first it seems like there might be a bug in the query planner.
- related to
-
SERVER-65159 Consider {a: 1, b: 1} index to satisfy sort on {a: 1, b: -1}
- Backlog
-
SERVER-9507 Optimize $sort+$group+$first pipeline to avoid full index scan
- Closed