Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3.34, 10.5.15
-
None
-
Debian9/10
Description
Hello,
I am observing strange change in execution plans, can you please have a look and assess, whether this is within expectations or a bug.
One of our teams reacently upgraded from 10.1.37 to 10.3.34, and was hit by performance issues, one of them being related to in_predicate_conversion_threshold.
After diggint into it and some additional testing (on 10.5.15 sandbox) I've observed following things:
- optimization as described in 'https://mariadb.com/kb/en/conversion-of-big-in-predicates-into-subqueries/' activates for SELECT, but does not seem to work for DELETE statements (and possibly other DMLs)
- after even a single 'close to the bigint unsigned upper limit' value is added into the IN list, execution plan for SELECT is changed to ALL/full table scan (if count of elements in IN list is below in_predicate_conversion_threshold) instead of range scan, for DELETE it is always full table scan (ironically, the conversion of IN list into subquery would be beneficial in this case)
|
10.5.15 test data |
create table `dummy` (`a` bigint unsigned not null, `b` int not null, `c` varchar(255) not null, primary key (`a`)); |
|
|
insert into `dummy` select seq, (seq-922337200000000), concat("test_", seq) from seq_922337203685477_to_922337204685477; |
insert into `dummy` select seq, (seq-922337205000000), concat("test_", seq) from seq_922337205000000_to_922337255000000; |
insert into `dummy` select seq, (seq-13246244583740000000), concat("test_", seq) from seq_13246244583744864994_to_13246244583745064994; |
For a 5001 IN list conversion kicked in, when disabled, a range scan is used. So far so good.
analyze select * from dummy where a in (922337203685477, (n+1) * 4999, 922337203690477); |
|
|
-- # @@local.in_predicate_conversion_threshold = 1000
|
-- # +------+--------------+-------------+--------+---------------+---------+---------+--------------+------+---------+----------+------------+----------------+
|
-- # | a | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
-- # +------+--------------+-------------+--------+---------------+---------+---------+--------------+------+---------+----------+------------+----------------+
|
-- # | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 5001 | 5001.00 | 100.00 | 100.00 | |
|
-- # | 1 | PRIMARY | dummy | eq_ref | PRIMARY | PRIMARY | 8 | tvc_0._col_1 | 1 | 1.00 | 100.00 | 100.00 | Using where |
|
-- # | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 5001 | 5001.00 | 100.00 | 100.00 | |
|
-- # | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
-- # +------+--------------+-------------+--------+---------------+---------+---------+--------------+------+---------+----------+------------+----------------+
|
-- # 0.019sec
|
|
|
-- # @@local.in_predicate_conversion_threshold = 0
|
-- # +------+-------------+-------+-------+---------------+---------+---------+------+------+---------+----------+------------+-------------+
|
-- # | a | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
-- # +------+-------------+-------+-------+---------------+---------+---------+------+------+---------+----------+------------+-------------+
|
-- # | 1 | SIMPLE | dummy | range | PRIMARY | PRIMARY | 8 | NULL | 5001 | 5001.00 | 100.00 | 100.00 | Using where |
|
-- # +------+-------------+-------+-------+---------------+---------+---------+------+------+---------+----------+------------+-------------+
|
-- # 0.116 sec |
When 13246244583744864994 is added into the IN list, plan is changed slightly, as long as conversion was activated. When it didn't, full table scan was used instead of range. Scanning 51M rows (3G) for 5001 IN list doesn't seem as the most optimal plan.
analyze select * from dummy where a in (922337203685477, (n+1) * 4998, 922337203690476, 13246244583744864994); |
|
|
-- # @@local.in_predicate_conversion_threshold = 1000
|
-- # +------+-------------+------------+--------+---------------+---------+---------+--------------+------+---------+----------+------------+----------------------------+
|
-- # | a | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
-- # +------+-------------+------------+--------+---------------+---------+---------+--------------+------+---------+----------+------------+----------------------------+
|
-- # | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 5001 | 5001.00 | 100.00 | 100.00 | Start temporary |
|
-- # | 1 | PRIMARY | dummy | eq_ref | PRIMARY | PRIMARY | 8 | tvc_0._col_1 | 1 | 1.00 | 100.00 | 100.00 | Using where; End temporary |
|
-- # | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
-- # +------+-------------+------------+--------+---------------+---------+---------+--------------+------+---------+----------+------------+----------------------------+
|
-- # 0.031sec
|
|
|
-- # @@local.in_predicate_conversion_threshold = 0
|
-- # +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|
-- # | a | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
-- # +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|
-- # | 1 | SIMPLE | dummy | ALL | PRIMARY | NULL | NULL | NULL | 50850729 | 100.00 | Using where |
|
-- # +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|
-- # 2 hours 16 min 23.739 sec |
When select is replaced with delete, no conversion takes place (no matter how many items are in the IN list) and again a full table scan is used.
analyze delete from dummy where a in (922337203685477, (n+1) * 4998, 922337203690476, 13246244583744864994); |
|
|
-- # @@local.in_predicate_conversion_threshold = 1000
|
-- # +------+-------------+-------+------+---------------+------+---------+------+----------+-------------+----------+------------+-------------+
|
-- # | a | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
-- # +------+-------------+-------+------+---------------+------+---------+------+----------+-------------+----------+------------+-------------+
|
-- # | 1 | SIMPLE | dummy | ALL | NULL | NULL | NULL | NULL | 51035518 | 51200003.00 | 100.00 | 0.01 | Using where |
|
-- # +------+-------------+-------+------+---------------+------+---------+------+----------+-------------+----------+------------+-------------+
|
-- # 10 hours 50 min 31.552 sec |