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

query optimizer fails to use index when sort spec is inconsistent with index spec

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: 2.5.4
    • Fix Version/s: 2.5.5
    • Component/s: Querying
    • Labels:
    • Backwards Compatibility:
      Minor Change
    • Operating System:
      ALL

      Description

      attached smoke test specifies index using the more correct numerical value

      {a: 1}

      instead of using boolean. still get similar results. When the sort spec is not exactly the same as the index spec, the query optimizer fails to come up with a plan that uses the sparse index. query planning output below:

      Beginning planning, options = INCLUDE_COLLSCAN 
      Canonical query:
      ns=test.server11568 limit=0 skip=0
      Tree: $and
      Sort: { a: true }
      Proj: {}
       
      =============================
      idx 0 is { _id: 1 }
      idx 1 is { a: 1.0 } sparse
      Finding relevant indices
      rated tree
      $and
       
      Planner: outputted 0 indexed solutions.
      Planner: outputting a collscan:
      SORT
      ---pattern = { a: true }
      ---fetched = 1
      ---sortedByDiskLoc = 0
      ---getSort = []
      Child:
      ------COLLSCAN
      ---------ns = test.server11568
      --------- filter = $and
      ---------fetched = 1
      ---------sortedByDiskLoc = 0
      ---------getSort = []
       
      not caching runner but returning 2 results
      
      

      -------------------

      2.4.6 (and afaict, since "forever")

      > db.example.ensureIndex({title: true}, {sparse: true})
      > db.example.insert({name: "Bob", title: "doctor"})
      > db.example.insert({name: "Jim"})
      > db.example.find().sort({title: true})
      { "_id" : ObjectId("5278370b13b0e338b3511f67"), "name" : "Bob", "title" : "doctor" }
      > db.example.find().sort({title: 1})
      { "_id" : ObjectId("5278370b13b0e338b3511f67"), "name" : "Bob", "title" : "doctor" }
      >
      

      in master from today (worked fine ~7days ago):

       db.example.ensureIndex({title: true}, {sparse: true})
      > db.example.insert({name: "Bob", title: "doctor"})
      > db.example.insert({name: "Jim"})
      > db.example.find().sort({title: true})
      { "_id" : ObjectId("527836b596870941233e3da8"), "name" : "Bob", "title" : "doctor" }
      > db.example.find().sort({title: 1})
      { "_id" : ObjectId("527836bb96870941233e3da9"), "name" : "Jim" }
      { "_id" : ObjectId("527836b596870941233e3da8"), "name" : "Bob", "title" : "doctor" }
      >
      

      Suddenly it matters if its 1 or true

        Attachments

        1. server11568.js
          0.6 kB
        2. server11568.js
          0.2 kB

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: