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

Filter with a complex logical expression is slow in SBE

    • Type: Icon: Bug Bug
    • Resolution: Gone away
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • ALL
    • QE 2023-02-06
    • 0

      Summary: queries with complex logical expressions in the filter are ~60-70% slower in SBE than in the classical engine

      Create a collection with 10^6 documents that have numeric fields a, b, c, and d.
       

      let queryExpr = {
               $expr:
               {
                 $or:
                 [
                   {$and: [{$eq: ["$a", 8]}, {$eq: ["$b", 70]}]},
                   {$and: [{$eq: ["$c", 42.5]}, {$eq: ["$d", 17]}]},
                   {$eq: ["$a", 9]}, {$eq: ["$b", 200]}, {$eq: ["$c", 51.2]}, {$eq: ["$d", 400]},
                 ]
               }
           };

       
      Assuming the namespace of the collection is "sbe-perf.LS", run the following benchmark

      benchRun({parallel: 1, seconds: 5, ops: [{op:"find", ns:"sbe-perf.LS", query:queryExpr, readCmd: true}]})

      results
      "queryLatencyAverageMicros" : 3873858.5,
      "totalOps" : NumberLong(2),
      "totalOps/s" : 0.2581392266174585,
       
      The same benchmark in classical engine:
      "queryLatencyAverageMicros" : 1061308,
      "totalOps" : NumberLong(5),
      "totalOps/s" : 0.9422241351465996,
       
      Top consumers of CPU in SBE:
      + 44.24% mongod [.] mongo::sbe::vm::ByteCode::runInternal
      + 4.65% mongod [.] mongo::sbe::ProjectStage::getNext
      + 3.22% mongod [.] mongo::sbe::vm::ByteCode::run
      + 3.04% mongod [.] mongo::sbe::LimitSkipStage::getNext
      + 2.98% mongod [.] mongo::sbe::ProjectStage::open
      + 2.53% mongod [.] mongo::sbe::UnionStage::open
      + 2.52% mongod [.] mongo::sbe::LimitSkipStage::open
      + 2.48% mongod [.] mongo::sbe::vm::ByteCode::swapStack
      + 2.34% libc-2.27.so [.] __strlen_avx2
      + 2.17% mongod [.] mongo::sbe::value::compareValue
      + 2.13% mongod [.] mongo::sbe::vm::genericCompare<std::equal_to<void> >
      + 1.96% mongod [.] mongo::sbe::ProjectStage::close
      + 1.84% mongod [.] mongo::sbe::vm::ByteCode::getField
      + 1.73% mongod [.] mongo::sbe::UnionStage::getNext
      + 1.47% mongod [.] mongo::sbe::FilterStage<false, false>::getNext
      + 1.30% mongod [.] mongo::sbe::FilterStage<false, false>::open
      + 1.15% mongod [.] mongo::sbe::LimitSkipStage::close
      + 1.06% mongod [.] mongo::sbe::FilterStage<false, false>::close
      + 1.04% mongod [.] __wt_btcur_next_prefix
       
      The generated SBE plan

       

      [1] filter {let [l19.0 = s39] exists (l19.0) && ! typeMatch (l19.0, 0x00000440) && l19.0 <=> false != 0 && l19.0 <=> 0 != 0}
      [1] nlj [s4, s5, s18] [s4, s5, s18]
       left
       [1] filter {s18}
       [1] nlj [s4, s5] [s4, s5]
       left
       [1] scan s4 s5 none none none none [] @"c494dfc1-7ed7-45e7-a46d-b253a1e532db" true false
       right
       [1] limit 1
       [1] union [s18] [
       [s12] [1] project [s12 = true]
       [1] filter {s8}
       [1] limit 1
       [1] union [s8] [
       [s6] [1] project [s6 = false]
       [1] filter {! true}
       [1] limit 1
       [1] coscan ,
       [s7] [1] project [s7 = true]
       [1] limit 1
       [1] coscan
       ] ,
       [s13] [1] project [s13 = true]
       [1] filter {s11}
       [1] limit 1
       [1] union [s11] [
       [s9] [1] project [s9 = false]
       [1] filter {! true}
       [1] limit 1
       [1] coscan ,
       [s10] [1] project [s10 = true]
       [1] limit 1
       [1] coscan
       ] ,
       [s14] [1] project [s14 = true]
       [1] filter {true}
       [1] limit 1
       [1] coscan ,
       [s15] [1] project [s15 = true]
       [1] filter {true}
       [1] limit 1
       [1] coscan ,
       [s16] [1] project [s16 = true]
       [1] filter {true}
       [1] limit 1
       [1] coscan ,
       [s17] [1] project [s17 = true]
       [1] limit 1
       [1] coscan
       ]
      
       right
       [1] limit 1
       [1] union [s39] [
       [s33] [1] project [s33 = true]
       [1] filter {let [l18.0 = s23] exists (l18.0) && ! typeMatch (l18.0, 0x00000440) && l18.0 <=> false != 0 && l18.0 <=> 0 != 0}
       [1] limit 1
       [1] union [s23] [
       [s21] [1] project [s21 = false]
       [1] filter {! let [l4.0 = let [l1.0 = s19, l1.1 = 8] fillEmpty (l1.0 <=> l1.1 == 0, exists (l1.0) && typeMatch (l1.0, 0xFFFFFFBF) == exists (l1.1) && typeMatch (l1.1, 0xFFFFFFBF))] exists (l4.0) && ! typeMatch (l4.0, 0x00000440) && l4.0 <=> false != 0 && l4.0 <=> 0 != 0}
       [1] project [s19 = getField (s4, "a")]
       [1] limit 1
       [1] coscan ,
       [s22] [1] project [s22 = let [l3.0 = let [l2.0 = s20, l2.1 = 70] fillEmpty (l2.0 <=> l2.1 == 0, exists (l2.0) && typeMatch (l2.0, 0xFFFFFFBF) == exists (l2.1) && typeMatch (l2.1, 0xFFFFFFBF))] exists (l3.0) && ! typeMatch (l3.0, 0x00000440) && l3.0 <=> false != 0 && l3.0 <=> 0 != 0]
       [1] project [s20 = getField (s4, "b")]
       [1] limit 1
       [1] coscan
       ] ,
       [s34] [1] project [s34 = true]
       [1] filter {let [l17.0 = s28] exists (l17.0) && ! typeMatch (l17.0, 0x00000440) && l17.0 <=> false != 0 && l17.0 <=> 0 != 0}
       [1] limit 1
       [1] union [s28] [
       [s26] [1] project [s26 = false]
       [1] filter {! let [l8.0 = let [l5.0 = s24, l5.1 = 42.5] fillEmpty (l5.0 <=> l5.1 == 0, exists (l5.0) && typeMatch (l5.0, 0xFFFFFFBF) == exists (l5.1) && typeMatch (l5.1, 0xFFFFFFBF))] exists (l8.0) && ! typeMatch (l8.0, 0x00000440) && l8.0 <=> false != 0 && l8.0 <=> 0 != 0}
       [1] project [s24 = getField (s4, "c")]
       [1] limit 1
       [1] coscan ,
       [s27] [1] project [s27 = let [l7.0 = let [l6.0 = s25, l6.1 = 17] fillEmpty (l6.0 <=> l6.1 == 0, exists (l6.0) && typeMatch (l6.0, 0xFFFFFFBF) == exists (l6.1) && typeMatch (l6.1, 0xFFFFFFBF))] exists (l7.0) && ! typeMatch (l7.0, 0x00000440) && l7.0 <=> false != 0 && l7.0 <=> 0 != 0]
       [1] project [s25 = getField (s4, "d")]
       [1] limit 1
       [1] coscan
       ] ,
       [s35] [1] project [s35 = true]
       [1] filter {let [l16.0 = let [l9.0 = s29, l9.1 = 9] fillEmpty (l9.0 <=> l9.1 == 0, exists (l9.0) && typeMatch (l9.0, 0xFFFFFFBF) == exists (l9.1) && typeMatch (l9.1, 0xFFFFFFBF))] exists (l16.0) && ! typeMatch (l16.0, 0x00000440) && l16.0 <=> false != 0 && l16.0 <=> 0 != 0}
       [1] project [s29 = getField (s4, "a")]
       [1] limit 1
       [1] coscan ,
       [s36] [1] project [s36 = true]
       [1] filter {let [l15.0 = let [l10.0 = s30, l10.1 = 200] fillEmpty (l10.0 <=> l10.1 == 0, exists (l10.0) && typeMatch (l10.0, 0xFFFFFFBF) == exists (l10.1) && typeMatch (l10.1, 0xFFFFFFBF))] exists (l15.0) && ! typeMatch (l15.0, 0x00000440) && l15.0 <=> false != 0 && l15.0 <=> 0 != 0}
       [1] project [s30 = getField (s4, "b")]
       [1] limit 1
       [1] coscan ,
       [s37] [1] project [s37 = true]
       [1] filter {let [l14.0 = let [l11.0 = s31, l11.1 = 51.2] fillEmpty (l11.0 <=> l11.1 == 0, exists (l11.0) && typeMatch (l11.0, 0xFFFFFFBF) == exists (l11.1) && typeMatch (l11.1, 0xFFFFFFBF))] exists (l14.0) && ! typeMatch (l14.0, 0x00000440) && l14.0 <=> false != 0 && l14.0 <=> 0 != 0}
       [1] project [s31 = getField (s4, "c")]
       [1] limit 1
       [1] coscan ,
       [s38] [1] project [s38 = let [l13.0 = let [l12.0 = s32, l12.1 = 400] fillEmpty (l12.0 <=> l12.1 == 0, exists (l12.0) && typeMatch (l12.0, 0xFFFFFFBF) == exists (l12.1) && typeMatch (l12.1, 0xFFFFFFBF))] exists (l13.0) && ! typeMatch (l13.0, 0x00000440) && l13.0 <=> false != 0 && l13.0 <=> 0 != 0]
       [1] project [s32 = getField (s4, "d")]
       [1] limit 1
       [1] coscan
       ]
      

       

       Flamegraph is attached

        1. sbe-logical-expr.svg
          141 kB
          Irina Yatsenko

            Assignee:
            alberto.massari@mongodb.com Alberto Massari
            Reporter:
            irina.yatsenko@mongodb.com Irina Yatsenko (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            16 Start watching this issue

              Created:
              Updated:
              Resolved: