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

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Fixed
    • 2.5.4
    • 2.5.5
    • Querying
    • Minor Change
    • 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
          Benety Goh
        2. server11568.js
          0.2 kB
          Benety Goh

        Issue Links

          Activity

            People

              benety.goh@mongodb.com Benety Goh
              bjori Hannes Magnusson
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: