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

Wrong SBE plan reported by explain() for rejected plans

    • Fully Compatible
    • ALL
    • v6.0
    • Hide

      Have a collection with three indexes: {a:1}, {b:1}, {a:1, b:1}. Run explain for a query that would consider all three indexes, e.g. find({a:7, b:9}).

      Result: the output for rejectedPlans would look similar to the below one. Notice, that the IXSCAN stage refers to index a_1 while slotBasedPlan.stages refers to a_1_b_1 (which is the winning plan in this case).

      "rejectedPlans" : [
      {
      "queryPlan" : {
      "stage" : "FETCH",
      "planNodeId" : 2,
      "filter" : {
      "b" :

      { "$eq" : 42 }

      },
      "inputStage" : {
      "stage" : "IXSCAN",
      "planNodeId" : 1,
      "keyPattern" :

      { "a" : 1 }

      ,
      "indexName" : "a_1",
      "isMultiKey" : false,
      "multiKeyPaths" :

      { "a" : [ ] }

      ,
      "isUnique" : false,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "forward",
      "indexBounds" :

      { "a" : [ "[7.0, 7.0]" ] }

      }
      },
      "slotBasedPlan" :

      { "slots" : "$$RESULT=s12 $$RID=s13 env: \{ s2 = Nothing (SEARCH_META), s1 = TimeZoneDatabase(Africa/Monrovia...Mexico/BajaSur) (timeZoneDB), s3 = 1636757269766 (NOW) }

      ",
      "stages" : "[2] nlj [] [s8, s4, s5, s6, s7] \n left \n [1] nlj [s5, s7] [s9, s10] \n left \n [1] project [s5 = \"a_1_b_1\", s7 = \{\"a\" : 1, \"b\" : 1}, s9 = KS(2B0E2B540104), s10 = KS(2B0E2B54FE04)] \n [1] limit 1 \n [1] coscan \n right \n [1] project [s4 = s11] \n [1] ixseek s9 s10 s6 s8 s11 [] @\"9f965641-651e-48d6-859b-e7b494a16b5f\" @\"a_1_b_1\" true \n \n \n right \n [2] limit 1 \n [2] seek s8 s12 s13 s4 s5 s6 s7 [] @\"9f965641-651e-48d6-859b-e7b494a16b5f\" true false \n \n"

      Show
      Have a collection with three indexes: {a:1}, {b:1}, {a:1, b:1}. Run explain for a query that would consider all three indexes, e.g. find({a:7, b:9}). Result: the output for rejectedPlans would look similar to the below one. Notice, that the IXSCAN stage refers to index a_1 while slotBasedPlan.stages refers to a_1_b_1 (which is the winning plan in this case). "rejectedPlans" : [ { "queryPlan" : { "stage" : "FETCH", "planNodeId" : 2, "filter" : { "b" : { "$eq" : 42 } }, "inputStage" : { "stage" : "IXSCAN", "planNodeId" : 1, "keyPattern" : { "a" : 1 } , "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] } , "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[7.0, 7.0]" ] } } }, "slotBasedPlan" : { "slots" : "$$RESULT=s12 $$RID=s13 env: \{ s2 = Nothing (SEARCH_META), s1 = TimeZoneDatabase(Africa/Monrovia...Mexico/BajaSur) (timeZoneDB), s3 = 1636757269766 (NOW) } ", "stages" : " [2] nlj [] [s8, s4, s5, s6, s7] \n left \n [1] nlj [s5, s7] [s9, s10] \n left \n [1] project [s5 = \"a_1_b_1\", s7 = \{\"a\" : 1, \"b\" : 1}, s9 = KS(2B0E2B540104), s10 = KS(2B0E2B54FE04)] \n [1] limit 1 \n [1] coscan \n right \n [1] project [s4 = s11] \n [1] ixseek s9 s10 s6 s8 s11 [] @\"9f965641-651e-48d6-859b-e7b494a16b5f\" @\"a_1_b_1\" true \n \n \n right \n [2] limit 1 \n [2] seek s8 s12 s13 s4 s5 s6 s7 [] @\"9f965641-651e-48d6-859b-e7b494a16b5f\" true false \n \n"
    • QE 2021-12-13, QE 2021-12-27, QE 2022-01-10, QE 2022-04-04, QE 2022-02-07, QE 2022-02-21, QE 2022-03-07, QE 2022-03-21, QE 2022-01-24, QE 2022-04-18, QE 2022-05-02, QE 2022-05-16

          Assignee:
          denis.grebennicov@mongodb.com Denis Grebennicov
          Reporter:
          irina.yatsenko@mongodb.com Irina Yatsenko (Inactive)
          Votes:
          0 Vote for this issue
          Watchers:
          9 Start watching this issue

            Created:
            Updated:
            Resolved: