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

Query plan is different for '$gte' and '$gt'

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Critical - P2 Critical - P2
    • None
    • Affects Version/s: 3.2.8
    • Component/s: Querying
    • None
    • ALL
    • Hide

      see description

      Show
      see description

      version 3.2.8 (wildTiger):

      [dbshell]: db.collection.find({crt: {$lte: new Date(1467047134260), $gt: new Date(1466442334260) }}).maxTimeMS(15000).count()
      [mongd log]: 
      2017-06-30T07:15:24.477+0000 I COMMAND  [conn5609213] query: { crt: { $lte: new Date(1467047134260), $gt: new Date(1466442334260) } }, maxTimeMS: 15000.0, fields: {} } planSummary: *IXSCAN* { crt: 1, wh: 1 } keyUpdates:0 writeConflicts:0 exception: operation exceeded time limit code:50 numYields:7019 reslen:74 locks:{ Global: { acquireCount: { r: 14040 } }, Database: { acquireCount: { r: 7020 } }, Collection: { acquireCount: { r: 7020 } } } protocol:op_command *15201ms*
      

      if change the '$gt' to '$gte', it then shows different:

      [dbshell]:  db.collection.find({crt: {$lte: new Date(1467047134260), $gte: new Date(1466442334260) }}).maxTimeMS(15000).count()
      [mongd log]: 2017-06-30T07:14:41.683+0000 I COMMAND  [conn5609213] count { count: "order", query: { crt: { $lte: new Date(1467047134260), $gte: new Date(1466442334260) } }, maxTimeMS: 15000.0, fields: {} } planSummary: *COUNT_SCAN* { crt: 1, wh: 1 } keyUpdates:0 writeConflicts:0 numYields:20357 reslen:47 locks:{ Global: { acquireCount: { r: 40716 } }, Database: { acquireCount: { r: 20358 } }, Collection: { acquireCount: { r: 20358 } } } protocol:op_command *1962ms*
      

      I tested with all the combination of '$lt'/''$lte', 'gt'/'gte', only when '$lte' and '$gte' using together can make mongo use COUNT_SCAN queryplan, which is much much faster.

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            hailong hailong cao
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: