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
- depends on
-
SERVER-69638 ☂️ ABT in SBE Stage Builders
- Closed
- related to
-
SERVER-66457 Large $elemMatch queries are slow in SBE
- Closed