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

Regex "s" option causes mongo to use slow query plan

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor - P4
    • Resolution: Fixed
    • Affects Version/s: 2.2.3, 3.0.0-rc6
    • Fix Version/s: 3.1.0
    • Component/s: JavaScript, Querying
    • Labels:
      None
    • Environment:
      Amazon linux ami
    • Backwards Compatibility:
      Fully Compatible
    • Operating System:
      ALL

      Description

      When a $ regex query is issued with $options: "s" mongo does not choose the most appropriate query plan. For example the first query below returns in 15000 ms with nscanned: 932956 the same query without $options: "s" return in 1ms with "nscanned" : 61. Finally I ran the first query again with a hint to use the correct index but it gives a warning: unknown regex flag:s and uses the same index as the first query.

      mongos> db.CA.find({ "a": "acme.com", "att": { $elemMatch: { "e.a": {$regex:"^x", $options: "s"} }, start: { $gte: new Date(1363737600000) }}}).explain()
      {
              "cursor" : "BtreeCursor a_1_uId_1",
              "isMultiKey" : false,
              "n" : 0,
              "nscannedObjects" : 932956,
              "nscanned" : 932956,
              "nscannedObjectsAllPlans" : 2798869,
              "nscannedAllPlans" : 2798869,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 729,
              "nChunkSkips" : 0,
              "millis" : 14817,
              "indexBounds" : {
                      "a" : [
                              [
                                      "acme.com",
                                      "acme.com"
                              ]
                      ],
                      "uId" : [
                              [
                                      {
                                              "$minElement" : 1
                                      },
                                      {
                                              "$maxElement" : 1
                                      }
                              ]
                      ]
              },
              "server" : "rs11",
              "millis" : 14817
      }
      mongos> db.CA.find({ "a": "acme.com", "att": { $elemMatch: { "e.a": {$regex:"^x"} }, start: { $gte: new Date(1363737600000) }}}).explain()
      {
              "cursor" : "BtreeCursor a_1_att.e.a_1 multi",
              "isMultiKey" : true,
              "n" : 0,
              "nscannedObjects" : 60,
              "nscanned" : 61,
              "nscannedObjectsAllPlans" : 180,
              "nscannedAllPlans" : 181,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 1,
              "indexBounds" : {
                      "a" : [
                              [
                                      "acme.com",
                                      "acme.com"
                              ]
                      ],
                      "att.e.a" : [
                              [
                                      "x",
                                      "y"
                              ],
                              [
                                      /^x/,
                                      /^x/
                              ]
                      ]
              },
              "server" : "rs11",
              "millis" : 1
      }
      mongos> db.CA.find({ "a": "acme.com", "att": { $elemMatch: { "e.a": {$regex:"^x", $options: "s"} }, start: { $gte: new Date(1363737600000) }}}).hint("a_1_att.e.a_1").explain()
      Wed Mar 20 18:09:53 warning: unknown regex flag:s
      Wed Mar 20 18:09:53 warning: unknown regex flag:s
      {
              "cursor" : "BtreeCursor a_1_att.e.a_1 multi",
              "isMultiKey" : true,
              "n" : 0,
              "nscannedObjects" : 1103778,
              "nscanned" : 1103778,
              "nscannedObjectsAllPlans" : 1103778,
              "nscannedAllPlans" : 1103778,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 8163,
              "nChunkSkips" : 0,
              "millis" : 5359,
              "indexBounds" : {
                      "a" : [
                              [
                                      "acme.com",
                                      "acme.com"
                              ]
                      ],
                      "att.e.a" : [
                              [
                                      "",
                                      {
       
                                      }
                              ],
                              [
                                      /^x/,
                                      /^x/
                              ]
                      ]
              },
              "server" : "rs11",
              "millis" : 5359
      }
      mongos>

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: