Details
-
Bug
-
Resolution: Won't Do
-
Major - P3
-
None
-
None
-
None
-
ALL
Description
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.
Attachments
Issue Links
- related to
-
DOCS-13065 Investigate changes in SERVER-9507: Optimize $sort+$group+$first pipeline to avoid full index scan
-
- Closed
-
-
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
-