[SERVER-73589] Heuristic to auto-parameterize less for complex expressions Created: 03/Feb/23  Updated: 29/Oct/23  Resolved: 20/Mar/23

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

Type: Improvement Priority: Major - P3
Reporter: David Storch Assignee: Anna Wawrzyniak
Resolution: Fixed Votes: 0
Labels: pm2697-m3
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Sprint: QE 2023-02-20, QE 2023-03-06, QE 2023-03-20, QE 2023-04-03
Participants:
Story Points: 5

 Description   

The auto-parameterization code used for SBE and the SBE plan cache currently auto-parameterizes all constants inside a match expression (at least for those types of expressions eligible for auto-parameterization). If the expression is complex, this means that it can end up with hundreds of parameters. Auto-parameterization is currently useful in combination with the SBE plan cache – if we see the same query again but with different parameter values, we can use the cached plan rather than recompiling from scratch.

However, we have seen evidence in our benchmarks that there is also a performance cost associated with auto-parameterization. The auto-parameterized plans have variable references to read values out of the RuntimeEnvironment instead of inlined constants, and also the plans themselves might have to sometimes be more complex in order to ensure that they can work if arbitrary constants are bound-in.

In this ticket, we should run some performance experiments in order to choose a good heuristic for when to avoid parameterizing. For instance, perhaps we can avoid parameterizing any constants inside an $and or $or if the number of branches exceeds some cutoff – e.g. more than 20 or 50 branches. The exact numbers would hopefully be empirically derived.



 Comments   
Comment by Githook User [ 17/Mar/23 ]

Author:

{'name': 'Anna Wawrzyniak', 'email': 'anna.wawrzyniak@mongodb.com', 'username': 'anna-wawrzyniak'}

Message: SERVER-73589 - Heuristic to auto-parameterize less for complex expressions
Branch: master
https://github.com/mongodb/mongo/commit/2972faf253242d5f9d0bb680dc317b58ca177ee8

Comment by Anna Wawrzyniak [ 03/Mar/23 ]

Perf test results:

variation autoparam enabled autoparam disabled improvement
MatchOrWithManyChildClauses_20.Crud.ftdc 1356664146 1344089808.18 0.94%
MatchOrWithManyChildClauses_20.find.ftdc 1356536714 1343957283.24 0.94%
MatchOrWithManyChildClauses_50.Crud.ftdc 2781442550 2750054262.04 1.14%
MatchOrWithManyChildClauses_50.find.ftdc 2781270882 2749878541.34 1.14%
MatchOrWithManyChildClauses_100.Crud.ftdc 5119319642 5094107057.08 0.49%
MatchOrWithManyChildClauses_100.find.ftdc 5119072932 5093753290.82 0.50%
MatchOrWithManyChildClauses_200.Crud.ftdc 9795325994 9667996186.70 1.32%
MatchOrWithManyChildClauses_200.find.ftdc 9794794332 9667552660.20 1.32%
MatchOrWithManyChildClauses_500.Crud.ftdc 23938925639 23533307433.56 1.72%
MatchOrWithManyChildClauses_500.find.ftdc 23937646374 23532450152.18 1.72%
MatchOrWithManyChildClauses_1000.Crud.ftdc 47198949313 46457608493.60 1.60%
MatchOrWithManyChildClauses_1000.find.ftdc 47197404867 46455994235.02 1.60%
MatchOrWithManyChildClauses_2000.Crud.ftdc 95440434519 92368496256.46 3.33%
MatchOrWithManyChildClauses_2000.find.ftdc 95437333797 92363469658.08 3.33%
MatchOrWithManyChildClauses_5000.Crud.ftdc 259571744673 229571594951.34 13.07%
MatchOrWithManyChildClauses_5000.find.ftdc 259559706621 229564098185.10 13.07%

It seems like it makes sense to set a threshold around ~500-1000 to disable autoparameterization.
Will add a new query knob internalQueryAutoParameterizationMaxParameterCount that will disable autoparameterization if the number of generated parameters was to be larger than the limit.

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