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

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

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • 3.1.0
    • Affects Version/s: 2.2.3, 3.0.0-rc6
    • Component/s: JavaScript, Querying
    • None
    • Environment:
      Amazon linux ami
    • Fully Compatible
    • ALL

      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>
      

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            davidburke David Burke
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: