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

index is not used to provide sort for $not query

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.5.5
    • Affects Version/s: None
    • Component/s: Querying
    • Environment:
      Windows 7, Gentoo
    • Fully Compatible
    • ALL

      explain output from 2.5.4 indicates that an in-memory sort (scanAndSort=true) is used to order results of $not query. The result set is large enough that it is hitting the internal limit of the in-memory sort - see assertion message. This is different from 2.5.3 where the query optimizer is able to utilize the index (scanAndSort=false).


      After updating from 2.5.3 to 2.5.4 start to get error messages for quires with sorting:

      query:
      {[db.photos.find({ $or: [

      { r1: 1, s: 5 }

      , { r1: 3, s:

      { $ne: 9 }

      } ] }).sort(

      {sdate: -1}

      , {_id:0, cid: 1}).limit(30).explain()}}

      indexes
      { "v" : 1, "key" :

      { "sdate" : 1 }

      , "name" : "sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r0" : 1 }

      , "name" : "r0_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r1" : 1 }

      , "name" : "r1_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r2" : 1 }

      , "name" : "r2_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r3" : 1 }

      , "name" : "r3_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r4" : 1 }

      , "name" : "r4_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "s" : 1 }

      , "name" : "s_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r0" : 1, "sdate" : 1 }

      , "name" : "r0_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r1" : 1, "sdate" : 1 }

      , "name" : "r1_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r2" : 1, "sdate" : 1 }

      , "name" : "r2_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r3" : 1, "sdate" : 1 }

      , "name" : "r3_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
      { "v" : 1, "key" :

      { "r4" : 1, "sdate" : 1 }

      , "name" : "r4_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }

      2.5.3:
      {
      "cursor" : "BtreeCursor sdate_1 reverse",
      "isMultiKey" : false,
      "n" : 30,
      "nscannedObjects" : 270,
      "nscanned" : 270,
      "nscannedObjectsAllPlans" : 270,
      "nscannedAllPlans" : 270,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 116,
      "indexBounds" : {
      "sdate" : [
      [

      { "$maxElement" : 1 }

      ,

      { "$minElement" : 1 }

      ]
      ]
      }
      }

      2.5.4:
      2013-11-23T19:37:02.040+0400 error: {
      "$err" : "Runner error, memory limit for sort probably exceeded",
      "code" : 17144
      } at src/mongo/shell/query.js:131

            Assignee:
            benety.goh@mongodb.com Benety Goh
            Reporter:
            klimashkin Paul Klimashkin
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: