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.