Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-69359

Aggregate query bails on DISTINCT_SCAN and uses IXSCAN

    • Type: Icon: Bug Bug
    • Resolution: Won't Do
    • Priority: Icon: Major - P3 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.

            Assignee:
            asya.kamsky@mongodb.com Asya Kamsky
            Reporter:
            terence@nexleaf.org Terence Honles
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: