[MDEV-22248] Optimizer chooses wrong strategy on delete Created: 2020-04-15  Updated: 2023-11-20

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.12
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Oli Sennhauser Assignee: Lena Startseva
Resolution: Unresolved Votes: 1
Labels: subquery
Environment:

Ubuntu 18.04, n.a.

Attachments: File cdrs_test.sql.gz    
Issue Links:
Relates
relates to MDEV-22415 Single table UPDATE/DELETE doesn't us... Open
relates to MDEV-28246 Optimizer uses all partitions during ... Closed

 Description   

5 different queries which should do the same thing but 3 different execution plans:

1st case customer complained (cdrs_process is much smaller than cdrs):

EXPLAIN
 
DELETE FROM cdrs
 
 WHERE uniqueid IN (SELECT uniqueid FROM cdrs_process)
 
;
 
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+
 
| id   | select_type        | table        | type            | possible_keys | key     | key_len | ref  | rows   | Extra       |
 
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+
 
|    1 | PRIMARY            | cdrs         | ALL             | NULL          | NULL    | NULL    | NULL | 197418 | Using where |
 
|    2 | DEPENDENT SUBQUERY | cdrs_process | unique_subquery | PRIMARY       | PRIMARY | 122     | func | 1      | Using index |
 
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+

Can/should the optimizer not do this automatically with the new features?

Rewriting to multi-table delete helped. We have seen some strange behaviour with aliasing but could not reproduce it. The next CEP seems ok:

EXPLAIN
 
DELETE c
 
  FROM cdrs AS c JOIN cdrs_process AS cp
 
 WHERE c.uniqueid = cp.uniqueid
 
;
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
|    1 | SIMPLE      | cp    | index  | PRIMARY       | start   | 10      | NULL             | 300  | Using index |
 
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+


EXPLAIN
 
DELETE cdrs
 
  FROM cdrs JOIN cdrs_process ON cdrs.uniqueid = cdrs_process.uniqueid
 
;
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 
| id   | select_type | table        | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 
|    1 | SIMPLE      | cdrs_process | index  | PRIMARY       | start   | 10      | NULL                       | 300  | Using index |
 
|    1 | SIMPLE      | cdrs         | eq_ref | PRIMARY       | PRIMARY | 122     | test.cdrs_process.uniqueid | 1    |             |
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+


EXPLAIN
 
DELETE c
 
  FROM cdrs AS c JOIN cdrs_process AS cp ON c.uniqueid = cp.uniqueid
 
;
 
 
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
|    1 | SIMPLE      | cp    | index  | PRIMARY       | start   | 10      | NULL             | 300  | Using index |
 
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

But this one is a bit less optimal compared to the previous 3:

EXPLAIN
 
DELETE c
 
  FROM cdrs AS c JOIN cdrs_process AS cp USING(uniqueid)
 
;
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+
 
|    1 | SIMPLE      | cp    | ALL    | PRIMARY       | NULL    | NULL    | NULL             | 300  |       |
 
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |       |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+

Should be reproducible with the attached test data.

 Comments   
Comment by Sergei Petrunia [ 2020-05-12 ]

oli, thanks for reporting this.
The issue with the subquery is a known issue, initially filed as MDEV-22415.

I am not sure why joining with USING (...) produces different plan from joining with ON, this will need to be investigated.

Comment by Oli Sennhauser [ 2021-06-01 ]

Still a problem in 10.3 to 10.5.
Affects a paying customer:
SQL> EXPLAIN
DELETE FROM domain_model_orderposition
WHERE uid IN (SELECT uid FROM temp_orderpositions_delete);
----------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

----------------------------------------------------------------------------------------------------+

1 PRIMARY domain_model_orderposition ALL NULL NULL NULL NULL 4078701 Using where
2 DEPENDENT SUBQUERY temp_orderpositions_delete ALL NULL NULL NULL NULL 13 Using where

----------------------------------------------------------------------------------------------------+

Execution time:
Query OK, 13 rows affected (28.509 sec)

With SELECT it does materialization:

SQL> EXPLAIN
SELECT * FROM domain_model_orderposition
WHERE uid IN (SELECT uid FROM temp_orderpositions_delete);

------------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------------------------------------------------------+

1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 13  
1 PRIMARY domain_model_orderposition eq_ref PRIMARY PRIMARY 4 westlotto.temp_orderpositions_delete.uid 1  
2 MATERIALIZED temp_orderpositions_delete ALL NULL NULL NULL NULL 13  

------------------------------------------------------------------------------------------------------------------------------+

With multi-table delete it does materialization:

SQL> EXPLAIN
DELETE domain_model_orderposition
FROM domain_model_orderposition
WHERE uid IN (SELECT uid FROM temp_orderpositions_delete)
;

------------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------------------------------------------------------+

1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 13  
1 PRIMARY domain_model_orderposition eq_ref PRIMARY PRIMARY 4 westlotto.temp_orderpositions_delete.uid 1  
2 MATERIALIZED temp_orderpositions_delete ALL NULL NULL NULL NULL 13  

------------------------------------------------------------------------------------------------------------------------------+

Ececution time:
Query OK, 13 rows affected (0.004 sec)

Comment by Oli Sennhauser [ 2021-06-01 ]

I was told that optimizers from other RDBMS can do materialization in this case.
We did the example with TEMPORARY tables but it happens also with normal real tables.

Comment by Oli Sennhauser [ 2021-06-04 ]

I just did a test with a competitive product. With v8.0 this problem is solved. In 5.7 it also exists.

Comment by Oli Sennhauser [ 2022-01-04 ]

We had the same case again today with an UPDATE statement. Same solution. I was told that MS SQL Server also solves query without problems.

Comment by Sergei Petrunia [ 2023-11-03 ]

Most of this should have been fixed in MDEV-7487. I'm not sure about the cause of the difference between the query with = and the query with USING.

lstartseva, could you check this?
Could you also attempt to construct a testcase for MDEV-22415 and see if it is fixed as Igor guessed it would be?

Comment by Lena Startseva [ 2023-11-20 ]

After fix in in MDEV-7487 all 5 queries have the same plan:

EXPLAIN
 
DELETE FROM cdrs
 
 WHERE uniqueid IN (SELECT uniqueid FROM cdrs_process)
 
;
 
 
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 
| id   | select_type | table        | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 
|    1 | PRIMARY     | cdrs_process | index  | PRIMARY       | PRIMARY | 122     | NULL                       | 300  | Using index |
 
|    1 | PRIMARY     | cdrs         | eq_ref | PRIMARY       | PRIMARY | 122     | test.cdrs_process.uniqueid | 1    |             |
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+


EXPLAIN
 
DELETE c
 
  FROM cdrs AS c JOIN cdrs_process AS cp
 
 WHERE c.uniqueid = cp.uniqueid
 
;
 
 
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
|    1 | SIMPLE      | cp    | index  | PRIMARY       | PRIMARY | 122     | NULL             | 300  | Using index |
 
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+


EXPLAIN
 
DELETE cdrs
 
  FROM cdrs JOIN cdrs_process ON cdrs.uniqueid = cdrs_process.uniqueid
 
;
 
 
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 
| id   | select_type | table        | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 
|    1 | SIMPLE      | cdrs_process | index  | PRIMARY       | PRIMARY | 122     | NULL                       | 300  | Using index |
 
|    1 | SIMPLE      | cdrs         | eq_ref | PRIMARY       | PRIMARY | 122     | test.cdrs_process.uniqueid | 1    |             |
 
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
 



EXPLAIN
 
DELETE c
 
  FROM cdrs AS c JOIN cdrs_process AS cp ON c.uniqueid = cp.uniqueid
 
;
 
 
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
|    1 | SIMPLE      | cp    | index  | PRIMARY       | PRIMARY | 122     | NULL             | 300  | Using index |
 
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+


EXPLAIN
 
DELETE c
 
  FROM cdrs AS c JOIN cdrs_process AS cp USING(uniqueid)
 
;
 
 
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
 
|    1 | SIMPLE      | cp    | index  | PRIMARY       | PRIMARY | 122     | NULL             | 300  | Using index |
 
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
 
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

Generated at Thu Feb 08 09:13:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.