Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.18, 10.5.9
Description
When using set optimizer_use_condition_selectivity=1 we get a better query plan than for set optimizer_use_condition_selectivity=[2|3|4|5]
set optimizer_use_condition_selectivity=1;
|
analyze SELECT sysapproval_approver0.`sys_id`
|
FROM ((sysapproval_approver sysapproval_approver0
|
INNER JOIN task task1
|
ON sysapproval_approver0.`sysapproval` = task1.`sys_id`
|
AND (( task1.`sys_domain_path` = '/'
|
OR task1.`sys_domain_path` LIKE '!!!/!!#/!!$/%'
|
OR task1.`sys_domain_path` LIKE '!!!/!!!/%' )))
|
INNER JOIN task task2
|
ON task1.`parent` = task2.`sys_id`
|
AND (( task2.`sys_domain_path` = '/'
|
OR task2.`sys_domain_path` LIKE '!!!/!!#/!!$/%'
|
OR task2.`sys_domain_path` LIKE '!!!/!!!/%' )))
|
WHERE task2.`sys_id` LIKE '8e7792a7dbfffb00fff8a345ca961934%'
|
AND ( sysapproval_approver0.`sys_domain_path` = '/'
|
OR sysapproval_approver0.`sys_domain_path` LIKE '!!!/!!#/!!$/%'
|
OR sysapproval_approver0.`sys_domain_path` LIKE '!!!/!!!/%' )
|
ORDER BY sysapproval_approver0.`order`
|
LIMIT 0, 50 ;
|
+------+-------------+-----------------------+-------+------------------------------------------------------+---------------------------+---------+-----------------+------+--------+----------+------------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-----------------------+-------+------------------------------------------------------+---------------------------+---------+-----------------+------+--------+----------+------------+----------------------------------------------+
|
| 1 | SIMPLE | task2 | range | PRIMARY,sys_class_name_2,sys_domain_path | PRIMARY | 96 | NULL | 1 | 0.00 | 100.00 | 100.00 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | task1 | ref | PRIMARY,sys_class_name_2,sys_domain_path,task_parent | task_parent | 99 | tt.task2.sys_id | 255 | NULL | 100.00 | NULL | Using index condition; Using where |
|
| 1 | SIMPLE | sysapproval_approver0 | ref | sysapproval_approver_ref5,sys_domain_path | sysapproval_approver_ref5 | 99 | tt.task1.sys_id | 176 | NULL | 100.00 | NULL | Using index condition; Using where |
|
+------+-------------+-----------------------+-------+------------------------------------------------------+---------------------------+---------+-----------------+------+--------+----------+------------+----------------------------------------------+
|
3 rows in set (0.001 sec)
|
|
|
set optimizer_use_condition_selectivity=[2|3|4|5]; -- use 2 or 3 or 4 or 5
|
|
|
+------+-------------+-----------------------+--------+------------------------------------------------------+---------------------------+---------+--------------------------------------+---------+------------+----------+------------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-----------------------+--------+------------------------------------------------------+---------------------------+---------+--------------------------------------+---------+------------+----------+------------+----------------------------------------------------+
|
| 1 | SIMPLE | sysapproval_approver0 | range | sysapproval_approver_ref5,sys_domain_path | sysapproval_approver_ref5 | 99 | NULL | 1856810 | 1195428.00 | 100.00 | 100.00 | Using index condition; Using where; Using filesort |
|
| 1 | SIMPLE | task1 | eq_ref | PRIMARY,sys_class_name_2,sys_domain_path,task_parent | PRIMARY | 96 | tt.sysapproval_approver0.sysapproval | 1 | 0.00 | 100.00 | 100.00 | Using where |
|
| 1 | SIMPLE | task2 | eq_ref | PRIMARY,sys_class_name_2,sys_domain_path | PRIMARY | 96 | tt.task1.parent | 1 | NULL | 100.00 | NULL | Using where |
|
+------+-------------+-----------------------+--------+------------------------------------------------------+---------------------------+---------+--------------------------------------+---------+------------+----------+------------+----------------------------------------------------+
|
3 rows in set (15.906 sec)
|
|
A workaround has been discovered:
set optimizer_switch="extended_keys=off";
|
extended_keys=off produces a much better query plan, returning results in 0.001 seconds even when set optimizer_use_condition_selectivity=4.
Attachments
Issue Links
- is blocked by
-
MDEV-26974 Improve selectivity and related costs in optimizer
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Stalled
-
- mentioned in
-
Page Failed to load
-
Page Failed to load
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Activity
I have checked the test case from the pull request with my new optimizer code and I get the same result file.
(In other words, my tree doesn't have this issue).
The part of the code that solves this particular issue is probably that I sort the ranges to first try the range with the least rows.
This gets the code to prefer the primary key, which solves this particular issue.
I will talk with Petrunia if we can take that particular sort code and add that to 10.4.8 and see if helps a bit, until I am ready with
the optimizer selectivity fixes.
Sorry, I missed that optimizer_use_condition_selectivity=4 is now default. I agree that we should try to fix the biggest issues in selectivity in 10.4, but the real fixes has to wait for 10.9 (as there are a LOT of them and they need proper testing before we can even consider back porting any of them)
There is several known issues with optimizer_use_condition_selectivity=4.
I have been working on fixing selectivity and other related optimizer issues for the last 2 months and it's still a couple of months before I will be completely done.
See MDEV-26974, "Improve selectivity and related costs in optimizer"
My hope is that the code will be in 10.9. Until then, don't use optimizer_use_condition_selectivity=4.
When fixing this bug please also keep the findings in MDEV-22537 in mind.
The issue was that calc_cond_selectivity_for_table preferred ranges with
many parts and when deciding on which selectivity to use.
Fixed by going through ranges according to the number of rows in the range.
This ensures that selectivity from ranges with few rows will be preferred
over ranges with many rows for indexes that uses the same columns.