Note: starting from 10.5 and fix for MDEV-9750, there other limits one can hit in the range optimizer. The optimizer makes a note in the trace when hitting them

examples:

                  "setup_range_conditions": [

                    {

                      "enforce_sel_arg_weight_limit": {

                        "index": "PRIMARY",

                        "old_weight": 105600,

                        "new_weight": 1560

                      }

                    }

                  ],

                      "sel_arg_weight_heuristic": {

                        "key1_field": "year_id",

                        "key2_field": "location_id",

                        "key1_weight": 30,

                        "key2_weight": 3519

                      }

Johnston could you review this?

MAX_SEL_ARGS is "the maximum number of SEL_ARG objects that may be allocated during range analysis" (range analysis is performed on a per-table basis) .
MAX_SEL_ARGS is a #define in opt_range.cc, it is not visible to the user.

max_sel_arg_weight is a user-visible variable. It is "the maximum weight of a SEL_ARG graph that the optimizer may construct". The definitions of SEL_ARG graph and its weight are rather convoluted.

If the optimizer allocated N SEL_ARG objects, the SEL_ARG graph it constructed may have a weight that's less, equal, or greater than N.

Note:

Optimizer trace will get these keywords for hitting the weight limit:

• sel_arg_weight_heuristic
• enforce_sel_arg_weight_limit

The patch for this MDEV added:

• sel_arg_alloc_limit_hit

Summary for documentation:

The optimizer may cause high memory usage or long processing time when analyzing complex WHERE clauses.
It has multiple limits which tell it to short-cut the analysis if it gets too time and/or memory intensive.
One of these limit is MAX_SEL_ARGS constant which is a #define in the source code. After this bugfix, the optimizer will write a sel_arg_alloc_limit_hit record in the Optimizer Trace when it hits the MAX_SEL_ARGS limitation.