Optimizer search depth default triggers performance issues.

New description

A query with join doing many eq_ref accesses is slower in 10.5 and 10.6 than it was in 10.4.

The slowness comes from join optimizer considering very large number of very similar query plans (basically, different permutations of eq_ref(t1), eq_ref(t2),eq_ref(t3),...).

Setting low optimizer_search_depth is a workaround, but it's still a workaround.

MariaDB 10.10 works fast, much faster than even 10.4. In 10.10, the speed was improved by fix for MDEV-28073. pruned_by_hanging_leaf optimization makes the join optimizer to not consider permutations of eq_ref accesses.

Original description

(note: sounds odd, we've had optimizer_search_depth=62 forever and didn't change that)

The change to default optimizer_search_depth=62 results in a performance issue in all tested 10.6 versions, affecting users upgrading from 10.4. Setting optimizer_search_depth=0 solves the problem in 10.6 environments, but in 10.11 and 11.4 environments the problem is not present at all, despite the default optimizer_search_depth=62.

The issue happens when the search tree is larger than expected for possible execution paths. This is not too rare in shops with well-normalized data. This particular reproduction involves 24 joins on 21 tables.