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

$expr inside $match does not use indexes in many cases

    • Type: Icon: Improvement Improvement
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • None

      Related issues:

      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.

       

       

        1. bad-aggr-query-explained.json
          2 kB
          Egor Elagin
        2. bad-aggr-query-info.json
          1 kB
          Egor Elagin
        3. bad-find-query-explained.json
          3 kB
          Egor Elagin
        4. bad-find-query-info.json
          2 kB
          Egor Elagin
        5. good-aggr-query-explained.json
          3 kB
          Egor Elagin
        6. good-aggr-query-info.json
          1 kB
          Egor Elagin

            Assignee:
            asya.kamsky@mongodb.com Asya Kamsky
            Reporter:
            egor.elagin Egor Elagin
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: