[SERVER-60724] Filter with a complex logical expression is slow in SBE Created: 14/Oct/21  Updated: 27/Oct/23  Resolved: 19/Jan/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Irina Yatsenko (Inactive) Assignee: Alberto Massari
Resolution: Gone away Votes: 0
Labels: pm2697-m3, sbe
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File sbe-logical-expr.svg    
Issue Links:
Depends
depends on SERVER-69638 ☂️ ABT in SBE Stage Builders Closed
Related
related to SERVER-66457 Large $elemMatch queries are slow in SBE Closed
Operating System: ALL
Sprint: QE 2023-02-06
Participants:
Story Points: 0

 Description   

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



 Comments   
Comment by Alberto Massari [ 19/Jan/23 ]

There will be more improvements, but if we already achieved to regain the 60% gap, the ticket has no point in being still open

Comment by Kyle Suarez [ 22/Oct/21 ]

ethan.zhang, eric.cox and irina.yatsenko, we are sending these SBE performance issues to the $group epic. Let us know if you think it belongs in a separate project.

Generated at Thu Feb 08 05:50:34 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.