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.

ANALYZE for tables

Run this after loading the dataset so that behavior is deterministic:

analyze table analyserflag           ;

analyze table analyseridentifier     ;

analyze table analysiscalculformula  ;

analyze table analysisdetail         ;

analyze table analysisresultsetting  ;

analyze table analysissettingversion ;

analyze table assembly               ;

analyze table assemblydetail         ;

analyze table assemblylocation       ;

analyze table assemblyorder          ;

analyze table assemblytitle          ;

analyze table assemblytitleset       ;

analyze table operator               ;

analyze table polarity               ;

analyze table resultproducer         ;

analyze table sample                 ;

analyze table sampletype             ;

analyze table subunit                ;

analyze table test                   ;

analyze table testanalyserflag       ;

analyze table useraccount            ;

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.