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

$and $ne performance degradation in 2.6

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 2.6.3
    • Fix Version/s: None
    • Component/s: Performance, Querying
    • Labels:
      None
    • Operating System:
      ALL

      Description

      Given this query:

      { $and: [ { tags: "A" }, { tags: { $ne: "B" } } ] }

      Where tags is an array and indexed, the query is much slower in 2.6.3 when compared to 2.4.10.

      I have attached a JS script that reproduces my test.

      I tested with a basic PSS replica-set, 1 million documents, with a pseudo-random set of 5 values (single letter of the alphabet as a string) per document for the tags array. Indexed.

      Sample output in 2.4.10:

      db.test.find( { $and: [ { tags: "A" }, { tags: { $ne: "B" } } ] } ).explain()
      {
              "cursor" : "BtreeCursor tags_1",
              "isMultiKey" : true,
              "n" : 152168,
              "nscannedObjects" : 178651,
              "nscanned" : 178651,
              "nscannedObjectsAllPlans" : 178651,
              "nscannedAllPlans" : 178651,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 1,
              "nChunkSkips" : 0,
              "millis" : 378,
              "indexBounds" : {
                      "tags" : [
                              [
                                      "A",
                                      "A"
                              ]
                      ]
              },
              "server" : "Boomtime:27119"
      }

      Sample output in 2.6.3:

      db.test.find( { $and: [ { tags: "A" }, { tags: { $ne: "B" } } ] } ).explain()
      {
              "cursor" : "BtreeCursor tags_1",
              "isMultiKey" : true,
              "n" : 152168,
              "nscannedObjects" : 1000000,
              "nscanned" : 4452528,
              "nscannedObjectsAllPlans" : 1000000,
              "nscannedAllPlans" : 4452644,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 36831,
              "nChunkSkips" : 0,
              "millis" : 5725,
              "indexBounds" : {
                      "tags" : [
                              [
                                      {
                                              "$minElement" : 1
                                      },
                                      "B"
                              ],
                              [
                                      "B",
                                      {
                                              "$maxElement" : 1
                                      }
                              ]
                      ]
              },
              "server" : "Boomtime:27119",
              "filterSet" : false,
              "stats" : {
                      "type" : "KEEP_MUTATIONS",
                      "works" : 4455900,
                      "yields" : 36831,
                      "unyields" : 36831,
                      "invalidates" : 0,
                      "advanced" : 152168,
                      "needTime" : 4300359,
                      "needFetch" : 3372,
                      "isEOF" : 1,
                      "children" : [
                              {
                                      "type" : "FETCH",
                                      "works" : 4455900,
                                      "yields" : 36831,
                                      "unyields" : 36831,
                                      "invalidates" : 0,
                                      "advanced" : 152168,
                                      "needTime" : 4300359,
                                      "needFetch" : 3372,
                                      "isEOF" : 1,
                                      "alreadyHasObj" : 0,
                                      "forcedFetches" : 0,
                                      "matchTested" : 152168,
                                      "children" : [
                                              {
                                                      "type" : "IXSCAN",
                                                      "works" : 4452527,
                                                      "yields" : 36831,
                                                      "unyields" : 36831,
                                                      "invalidates" : 0,
                                                      "advanced" : 1000000,
                                                      "needTime" : 3452527,
                                                      "needFetch" : 0,
                                                      "isEOF" : 1,
                                                      "keyPattern" : "{ tags: 1.0 }",
                                                      "boundsVerbose" : "field #0['tags']: [MinKey, \"B\"), (\"B\", MaxKey]",
                                                      "isMultiKey" : 1,
                                                      "yieldMovedCursor" : 0,
                                                      "dupsTested" : 4452527,
                                                      "dupsDropped" : 3452527,
                                                      "seenInvalidated" : 0,
                                                      "matchTested" : 0,
                                                      "keysExamined" : 4452528,
                                                      "children" : [ ]
                                              }
                                      ]
                              }
                      ]
              }
      }

      Hinting $natural performs better on 2.6.3 (for me).

        Attachments

        1. $and.js
          2 kB
          Andrew Ryder

          Issue Links

            Activity

              People

              Assignee:
              david.storch David Storch
              Reporter:
              andrew.ryder Andrew Ryder (Inactive)
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: