This is a followup to MDEV-28073.

Problem description

Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes.

An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit.

The fix for MDEV-28073 addresses these issues to some extent and was pushed into 10.6. This MDEV is about more comprehensive fix.

Solution

TODO description. Copy from:
https://jira.mariadb.org/browse/MDEV-28073?focusedCommentId=225701&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-225701

Join prefix pruning is now controlled by @@optimizer_prune_level variable. The new pruning is done when @@optimizer_prune_level=2, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting @@optimizer_prune_level=1 (the default before 10.10).

The tree

It is preview-10.10-MDEV-28852-join-pruning