-
Type:
Task
-
Resolution: Gone away
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Query Execution
-
2
Latest results show that this workload has a ~30% regression:
| lookup_with_only_unsharded_colls | RunLookups.LookupOneToFewUnshardedToUnsharded | -31.25842023 |
Note that this is the query in question:
[ {$match: {int: {$lte: 4}}}, {$lookup: { from: "Collection1", let: {int0: "$int"}, pipeline: [{$match: {$expr: {$eq: ["$$int0", "$int"]}}}], as: "joined"}}, {$project: {str: 0, "joined.str": 0}}]
And that only the first $match is eligible for SBE Pushdown:
{
"explainVersion" : "2",
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"namespace" : "db.coll",
"indexFilterSet" : false,
"parsedQuery" : {
"int" : {
"$lte" : 4
}
},
"queryHash" : "A8FA3240",
"planCacheKey" : "89EFE34A",
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"queryPlan" : {
"stage" : "COLLSCAN",
"planNodeId" : 1,
"filter" : {
"int" : {
"$lte" : 4
}
},
"direction" : "forward"
},
"slotBasedPlan" : {
"slots" : "$$RESULT=s5 env: { s3 = 1673294088424 (NOW), s7 = 4, s1 = TimeZoneDatabase(Asia/Jayapura...Australia/Hobart) (timeZoneDB), s2 = Nothing (SEARCH_META) }",
"stages" : "[1] filter {(traverseF(s4, lambda(l1.0) { ((l1.0 <= s7) ?: false) }, false) ?: false)} \n[1] scan s5 s6 none none none none [s4 = int] @\"8921938a-f5dc-4556-896b-1b9381e9f125\" true false "
}
},
"rejectedPlans" : [ ]
}
}
},
{
"$lookup" : {
"from" : "Collection1",
"as" : "joined",
"let" : {
"int0" : "$int"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$eq" : [
"$$int0",
"$int"
]
}
}
}
]
}
},
{
"$project" : {
"str" : false,
"joined" : {
"str" : false
},
"_id" : true
}
}
],
"serverInfo" : {
"host" : "ip-10-122-15-159",
"port" : 27017,
"version" : "6.3.0-alpha-833-g14311b5",
"gitVersion" : "14311b5b2491c609c7d1e08d13913410c2022afc"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
"internalQueryProhibitBlockingMergeOnMongoS" : 0,
"internalQueryMaxAddToSetBytes" : 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600,
"internalQueryFrameworkControl" : "tryBonsai"
},
"command" : {
"aggregate" : "coll",
"pipeline" : [
{
"$match" : {
"int" : {
"$lte" : 4
}
}
},
{
"$lookup" : {
"from" : "Collection1",
"let" : {
"int0" : "$int"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$eq" : [
"$$int0",
"$int"
]
}
}
}
],
"as" : "joined"
}
},
{
"$project" : {
"str" : 0,
"joined.str" : 0
}
}
],
"explain" : true,
"cursor" : { },
"lsid" : {
"id" : UUID("c5fa033f-f963-4fb1-b709-75c24ea632d4")
},
"$db" : "db"
},
"ok" : 1
}