9d4321 introduces a performance regression in the AverageLatency metric on both the normalized and denormalized TPC-H schema for query 5. Note that AverageLatency measures the total execution time for a single "cold" run of the query on scale 1 of TPC-H.

The shape of the denormalized query is:

[$match, $unwind, $match, $unwind, $lookup, $unwind, $group, $project, $sort]

After diffing, it looks like the part of the pipeline where executionTimeMillisEstimate starts to differ significantly is where the pipeline has a $lookup; that stage and all subsequent stages take more than double the time to execute.

We see the same thing on the normalized schema, which has a different shape with more nested $lookups:

[<5x nested $lookups>, $unwind, $group, $project, $sort]

Attaching explains for commits 76a0ce (commit before regression was introduced) and 76a0ce on both schemas. Note that the same regression happens on a standalone variant where the feature flag is enabled.

Useful evergreen patches:

• Test BF-24702 with feature flags on + genny logging explains
• Test BF-24702 at 76a0ce