I recently upgraded from 10.2 to 10.4 and had massive performance problems since then (plus 50% CPU load, very slow performance of many cron jobs which now take double the time). While I am still searching for many of the reasons, I found one query in particular in which MariaDB does not use a primary index, which leads to the query taking indefinitely long (1 hour+) instead of about 110ms. This is that query:
Many of the parts of the query are not important, what is important are all the LEFT JOINs I am doing - because each left join is using the PRIMARY index of a table, matching exactly one entry (or no entry), yet with the very last LEFT JOIN of the alias g2 MariaDB is not using that PRIMARY index, leading to the EXPLAIN output in mariadb_explain1.png I attached to this issue.
When I add "FORCE INDEX FOR JOIN (PRIMARY)" after "g2" in the query, then MariaDB correctly joins the table and you can see the EXPLAIN in mariadb_explain2.png attached to this issue.
I switched off all new optimizer switches since MariaDB 10.2, which did not solve this problem, so I am guessing this must be some other kind of bug. As you can see from the EXPLAIN MariaDB knows it could use the PRIMARY index, yet decides against it to do "Using where; Using join buffer", which is a million times slower unfortunately.