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

Sorting an indexed key in pipeline raises scanned and decreases performance

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Won't Fix
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.2.3, 2.2.7
    • Component/s: Aggregation Framework, Querying
    • Labels:
      None
    • Fully Compatible
    • ALL
    • Hide

      db.test.ensureIndex(

      {i: 1, t: 1, _id: -1, cs: 1}

      );
      db.test.insert(

      {i: 1, t:1, ts: 1, cs: 1, c: []}

      );
      db.test.insert(

      {i: 2, t:1, ts: 2, cs: 1, c: []}

      );
      db.test.insert(

      {i: 3, t:1, ts: 3, cs: 1, c: []}

      );
      db.test.insert(

      {i: 4, t:1, ts: 4, cs: 1, c: []}

      );

      //indexed key sort
      db.test.runCommand('aggregate', {pipeline: [{"$match": {i:

      {"$in":[1,2,3,4]}

      , t: 1, cs:

      {"$gt":0}

      }}, {"$sort":{_id:-1}}], explain: true});

      //non-indexed key sort
      db.test.runCommand('aggregate', {pipeline: [{"$match": {i:

      {"$in":[1,2,3,4]}

      , t: 1, cs:

      {"$gt":0}

      }}, {"$sort":{ts:-1}}], explain: true});

      The reason for the index above is to optimize:
      db.test.find({i: 1, t: 1, cs: {"$gt":0}}).sort({_id: -1}).limit(1)

      Show
      db.test.ensureIndex( {i: 1, t: 1, _id: -1, cs: 1} ); db.test.insert( {i: 1, t:1, ts: 1, cs: 1, c: []} ); db.test.insert( {i: 2, t:1, ts: 2, cs: 1, c: []} ); db.test.insert( {i: 3, t:1, ts: 3, cs: 1, c: []} ); db.test.insert( {i: 4, t:1, ts: 4, cs: 1, c: []} ); //indexed key sort db.test.runCommand('aggregate', {pipeline: [{"$match": {i: {"$in":[1,2,3,4]} , t: 1, cs: {"$gt":0} }}, {"$sort":{_id:-1}}], explain: true}); //non-indexed key sort db.test.runCommand('aggregate', {pipeline: [{"$match": {i: {"$in":[1,2,3,4]} , t: 1, cs: {"$gt":0} }}, {"$sort":{ts:-1}}], explain: true}); The reason for the index above is to optimize: db.test.find({i: 1, t: 1, cs: {"$gt":0}}).sort({_id: -1}).limit(1)

      If I specify a sort aggregation on an indexed key, performance is greatly reduced and the number of scanned items goes up. Looking at the explain, it seems to be adding another plan. When I use a non-indexed key it will not cause another plan and executes quickly.

      I know this affects 2.2.7 and 2.2.3 since I tested those both specifically. Didn't try anything between those. I'd like to see this backported into 2.2.8, if possible.

        1. indexes.txt
          1 kB
        2. sortOnIndex.txt
          10 kB
        3. sortOnNonIndex.txt
          8 kB

            Assignee:
            Unassigned Unassigned
            Reporter:
            fastest963 James Hartig
            Votes:
            2 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: