Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.1(EOL), 10.2(EOL), 10.4(EOL), 10.5, 10.6, 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
-
-
- Stalled
-
- relates to
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
For the 2 query, can you please post the following variables used:
use_stat_tables
in_predicate_conversion_threshold
optimizer_switch
optimizer_use_condition_selectivity
join_cache_level
Where analyze table run on the involved tables?
I have run "query 2" and the iwork query on the 10.7-selecitivity branch, without and without "analyze table", and all queries was < 1 second independent on the setting of optimizer_use_condition_selectivity
The explain I get for the iwork query with optimizer_use_condition_selectivity=4 is:
-------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | <subquery6> | ALL | distinct_key | NULL | NULL | NULL | 1256 | |
| 1 | PRIMARY | node | eq_ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX02 | PRIMARY | 4 | dbs.node_link.CHILD_NODENBR | 1 | Using where |
| 1 | PRIMARY | <subquery8> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where |
| 1 | PRIMARY | <subquery4> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where |
| 1 | PRIMARY | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX03 | 3 | const | 166 | Using where |
| 1 | PRIMARY | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition; Using where; FirstMatch((sj-nest)) |
| 6 | MATERIALIZED | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX03 | 3 | const | 30 | Using where |
| 6 | MATERIALIZED | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition; Using where |
| 8 | MATERIALIZED | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX01 | 153 | const | 446 | Using where; Using index |
| 8 | MATERIALIZED | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition |
| 4 | MATERIALIZED | node | ref | PRIMARY,NODE_COMPOSITE_IDX,NODE_IDX01,NODE_IDX03 | NODE_IDX03 | 3 | const | 6 | Using where |
| 4 | MATERIALIZED | node_link | ref | NODE_LINK_IDX,NODE_LINK_IDX01,NODE_LINK_IDX02,NODE_LINK_IDX03 | NODE_LINK_IDX03 | 9 | dbs.node.NODENBR,const | 41 | Using index condition |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Using selectivity > 1 on any version before 11.0 is not advisable. If it works, then things are good. If not, better to not use it and instead test to see if 11.0 fixes the issue.
There are several problems with the selectivity code before 11.0 and there is not any 'easy fix' that can be done. It's not advisable to do a big fix of 100-1000 lines of code in an otherwise stable release as there
is a big change to get notable regressions if trying to do that.
"Using selectivity > 1 on any version before 11.0 is not advisable. " <-- Is this documented?
If not, then this should be documented before this bug is closed.
This is not a bug, but a reflection of the state of MariaDB before 11.0
00affc324cd69f5e00befe195de930c4df7b2f32 is a null merge
Can you find the commit that caused the issue to happen? Elena should have some tools for that...