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

Query Planner uses inefficient plan for regular expression + sort with aggregations

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Duplicate
    • Icon: Major - P3 Major - P3
    • None
    • None
    • Aggregation Framework
    • None
    • ALL
    • Hide

      var bulk = db.items.initializeUnorderedBulkOp();
       
      for (var i = 0; i < 10000000; i++) {
        bulk.insert({ name: i.toString(), created: new Date() });
      }
       
      bulk.execute();
       
      db.items.createIndex({ name: 1, created: 1 }, { background: true });
      db.items.createIndex({ created: 1 }, { background: true });
       
      // This command runs instantly
      db.things
          .find({ name: /^999999/ })
          .sort({ created: 1 });
       
      // This command takes several seconds to complete
      db.things.aggregate([
          { $match: { name: /^999999/ } },
          { $sort: { created: 1 } }
      ]);
      

      Show
      var bulk = db.items.initializeUnorderedBulkOp();   for (var i = 0; i < 10000000; i++) { bulk.insert({ name: i.toString(), created: new Date() }); }   bulk.execute();   db.items.createIndex({ name: 1, created: 1 }, { background: true }); db.items.createIndex({ created: 1 }, { background: true });   // This command runs instantly db.things .find({ name: /^999999/ }) .sort({ created: 1 });   // This command takes several seconds to complete db.things.aggregate([ { $match: { name: /^999999/ } }, { $sort: { created: 1 } } ]);

    Description

      If a collection has a compound index on a string field + a sort field and another index on just the sort field then an aggregation using a regular expression match will only use the index with just the sort field. This does not happen with the equivalent find command. Adding an index on sort field + string field doesn't improve performance. Changing the location of the sort in the aggregation also has no effect.

      The reason for both indexes is that some queries need to be able to do a find based just on the sort key.

      Attachments

        Activity

          People

            kelsey.schubert@mongodb.com Kelsey Schubert
            jakesjews Jacob Jewell
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: