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

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

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: 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 } } ]);

      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.

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

              Created:
              Updated:
              Resolved: