Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.5, 10.6, 10.1(EOL), 10.2(EOL), 10.4(EOL), 10.7(EOL)
Description
In MariaDB 10.4 we changed the default of optimizer_use_cond_selectivity
from 1 to to 4 to get better plans for complex queries.
The definition of the values are (from mysqld --help):
1 - use selectivity of index backed range
conditions to calculate the cardinality of a partial join
if the last joined table is accessed by full table scan
or an index scan. This is basically how MariaDB and MySQL 5.5 and before
works.
2 - use selectivity of index backed range conditions to calculate the
cardinality of a partial join in any case
3 - additionally always use selectivity of range conditions that are not backed
by any index to calculate the cardinality of a partial join
4 - use histograms to calculate selectivity of range conditions that are not
backed by any index to calculate the cardinality of a partial join
5 - additionally use selectivity of certain non-range predicates calculated on
record samples
In some case when optimizer_use_cond_selectivity = 2 (or bigger) the optimizer
calculates the selectivity wrong, which can cause it to select a wrong (slow)
plan.
One can find if this is what causes wrong plan by using optimizer_trace
(https://mariadb.com/kb/en/optimizer-trace-overview/). If you see a cost
that is DOUBLE_MAX then you have probably hit this bug.
A temporary workaround, until this problem is fixed, is to set
optimizer_use_cond_selectivity to 1.
If you have this problem, please also attach to this Jira entry:
- The query,
- Table defintions
- Optimizer trace
Attachments
Issue Links
- includes
-
MDEV-21633 Assertion `tmp >= 0' failed in best_access_path with rowid_filter=ON
- Stalled
- relates to
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
- Closed
-
MDEV-25830 optimizer_use_condition_selectivity=4 sometimes produces worse plan than optimizer_use_condition_selectivity=1
- Closed
-
MDEV-28246 Optimizer uses all partitions during an update in MariaDB 10.6.x but not in 10.2.x
- Closed