Very suboptimal join order is generated for a simple query even if MariaDB query planner knows the other is better in every sense

Description

This is the summary of the bug reported here: https://phabricator.wikimedia.org/T113901

This is the exact CREATE syntax of revision and page:

Regular explain:

Tables have approximately the numbers or rows you see on the autoinc.

Forcing the other plan:

The actual query parsed has no issue:

Even ignoring the indexes does not work (it is not related to covering index):

I think this is a bug on the optimizer.

Running ANALYZE on both tables, even with use-stat-tables=PREFERABLY does not help (and it shouldn't, row counts are ok, it is that for some reason maria refuses to use the clearly better plan -that itself understands).

This could be a specific configuration we have, you can see most of it at:
https://git.wikimedia.org/blob/operations%2Fpuppet.git/1423255184712f7fde17dee338c7ac1519ee44ee/templates%2Fmariadb%2Fproduction.my.cnf.erb

Environment

Linux with MariaDB-like packages

Assignee

Sergei Petrunia

Reporter

Jaime Crespo

Components

Sprint

None

Fix versions

Affects versions

Priority

Major
Configure