[SERVER-64976] TPC-H query 5 executes >2x as slowly Created: 28/Mar/22  Updated: 29/Oct/23  Resolved: 05/Apr/22

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

Type: Bug Priority: Major - P3
Reporter: Alya Berciu Assignee: Anton Korshunov
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File denorm_450ef9.json     File denorm_76a0ce.json     PNG File explain_diff_denorm.png     File norm_450ef9.json     File norm_76a0ce.json    
Issue Links:
Depends
Backwards Compatibility: Fully Compatible
Operating System: ALL
Sprint: QO 2022-04-04, QO 2022-04-18
Participants:
Linked BF Score: 135

 Description   

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:



 Comments   
Comment by Githook User [ 05/Apr/22 ]

Author:

{'name': 'Anton Korshunov', 'email': 'anton.korshunov@mongodb.com', 'username': 'antkorsh'}

Message: SERVER-64976 Caching of sub-pipelines should be allowed with explain
Branch: master
https://github.com/mongodb/mongo/commit/33720b4cbb38b4dc5ab98f9bde68a62451e93745

Generated at Thu Feb 08 06:01:35 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.