I am looking at this now.
What is unique with this query is:

• 24 different tables
• 7 using 'ref' access (with one expected row), one using 'ALL' and the 16 using eq_ref

This means that almost all combinations has the same cost, which means that the optimizer is going trough roughly 24! = 6.2E+23 combinations.

After discussing this with Sergei Petrunia, we come up with the following ideas to speed up the optimizer stage:

• When searching for combinations, if we find a set of tables at the end with 'eq_ref', skip back to the first not eq_ref table and continue searching from there.
• After going trough tables, remember the lowest possible cost for adding the table. When checking if we should cut of the search, add the minimum possible cost for the renaming tables to the current cost when comparing with the current best cost.

This should cut down the number of combinations notable.

A comment on the current patch:
All queries that has a several EQ_REF joins (joins on primary or unique keys which all key parts usable) should be faster. The more EQ_REF joins, the better performance when choosing plan.

I am now working on several new ideas of how to get down the combinations checked by the optimizer. Some of them will be in 10.6,
the others will be in 10.9 or 10.10. Will update this ticket with more information in 1-2 weeks

I am almost done with this task. The current work consist of:

• Fixed an issue with table order getting scrambled. This allows the greedy_optimizer to find good plans faster in the
  case that smaller tables should be used first (common case).
• Improved table pruning in optimizer by updating the current key_dependency as part of calculating plans.
  This allows us to prune some plans earlier that we couldn't do before (as we didn't know before if all keys where already usable)
• Improve pruning in greedy_search by sorting tables during search
  This allows us to find good plans faster, which allows us to skip more bad plans
• Add eq_ref optimization (something similar as MySQL has but totally different approach). This chains EQ_REF tables (tables that can use an unique key to find a row) when doing optimization, which significantly reduces search combinations. This is optimization is enabled when setting
  optimizer_prune_level=2 (which is now the new default).

The effect of all this is a significant improvement of the speed of greedy_optimizer when table pruning is enabled.
I did a quick comparison with MySQL 5.7 (which has eq_ref chaining optimization) by running greedy_search.test on the resulting code:
Test for optimized builds:
--optimizer_trace
Test for optimized builds:
mysql: main.greedy_optimizer 1956 ms
mariadb: main.greedy_optimizer prune_level=1 1773 ms
mariadb: main.greedy_optimizer prune_level=2 1369 ms

In MariaDB 10.6 default, the above tests takes a VERY long time.

Of the above work, the first 2 parts of the work are scheduled to be added to MariaDB 10.6 and the last works are scheduled for 10.10

You can find this work in bb-10.6-monty (until it is pushed to 10.6 and 10.10)

Commit https://github.com/MariaDB/server/commit/b729896d00e was pushed and released in 10.6.8.

As far as I understand, it fixes some cases. Other cases will be fixed in 10.10, to avoid confusion they should get a new MDEV number.

monty, that case that rob.schwyzer@mariadb.com is talking about — was it supposed to be fixed in 10.6 or in 10.10?

The code in bb-10.6-monty# was updated since we spoked last time with the changes that has already gone into 10.6 (which is just some small improvements to greedy_search() as list in my post above). This is why optimizer_prune_level could not be changed.
The big changes are in bb-10.10-monty, which is in QA testing and will be pushed to 10.10 as soon as QA gives a green light.

The state of the original test case:
In current 10.6 (that doesn't have optimizer_prune_level=2)
source xpo_testcase_explain.sql takes 0.092 second with default search_depth 62 and 0.032 seconds with search_depth 16
In bb-10.10-monty with default settings (prune_level=2, search_dept 62):
source xpo_testcase_explain.sql takes 0.026 seconds

Note that in 10.6, the main reason it is faster than before was the EQ_REF shortcuting that was done in April. This only solves special cases where there are a lot of EQ_REF tables. The code in 10.10 is more general and can solve a wider range of cases.