[MDEV-23861] Execution plan Created: 2020-10-01  Updated: 2021-10-14  Resolved: 2021-10-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Heiki Laaniste Assignee: Sergei Petrunia
Resolution: Cannot Reproduce Votes: 1
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-16188 Use in-memory PK filters built from r... Closed

 Description   

Ever since upgrading from 10.3.20 to MariaDB 10.4.8 I've been seeing problems with this kind of query.

SELECT count(1)
 
FROM invoice i,
 
  inv_rows ir
 
  LEFT JOIN product p ON (p.id = ir.product_id)
 
  LEFT JOIN temp_attributes a ON (
 
    a.obj_table = 'product'
 
    AND p.id = a.obj_id
 
    AND a.name = 'regular_giftcard'
 
  )
 
WHERE ir.invoice_idRec = i.idRec
 
  AND i.state = 1
 
  AND i.confirmed = 1
 
  AND i.type_id <> 3
 
  AND i.type_id <> 4
 
  AND i.type_id <> 5
 
  AND i.type_id <> 7
 
  AND i.type_id <> 9
 
  AND ir.has_components = 0
 
  AND i.date >= '2019-01-01'
 
  AND i.date <= '2019-09-20';

The optimizer never had any problems with this in the previous version. Now there are many cases when this query takes a long time, over 240s, which is our max_statement_time.
The query works well when I a) calculate engine independent stats for it or b) drop the index on temp_attributes.name column. That index is needed for another query though.

MariaDB [testdb]> set local max_statement_time=10;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
 
 
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
 
MariaDB [testdb]> 
MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
+------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
 
| id   | select_type | table | type       | possible_keys         | key            | key_len | ref                       | rows        | Extra                           |
 
+------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
 
|    1 | SIMPLE      | i     | ALL        | PRIMARY,date,type_id  | NULL           | NULL    | NULL                      | 492879      | Using where                     |
 
|    1 | SIMPLE      | ir    | ref        | invoice_idRec         | invoice_idRec  | 4       | testdb.i.idRec       | 1           | Using where                     |
 
|    1 | SIMPLE      | p     | eq_ref     | PRIMARY               | PRIMARY        | 4       | testdb.ir.product_id | 1           | Using index                     |
 
|    1 | SIMPLE      | a     | ref|filter | obj_id,obj_table,name | obj_table|name | 107|152 | const                     | 301592 (0%) | Using where; Using rowid filter |
 
+------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [testdb]> analyze table temp_attributes PERSISTENT FOR COLUMNS (id, obj_id, obj_table, name, value_text, value_int) 
    -> INDEXES (PRIMARY,obj_id, obj_table, name, value_text,value_int);
 
+-----------------------------+---------+----------+-----------------------------------------+
 
| Table                       | Op      | Msg_type | Msg_text                                |
 
+-----------------------------+---------+----------+-----------------------------------------+
 
| testdb.temp_attributes | analyze | status   | Engine-independent statistics collected |
 
| testdb.temp_attributes | analyze | status   | OK                                      |
 
+-----------------------------+---------+----------+-----------------------------------------+
 
2 rows in set (3.20 sec)
 
 
 
MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
+----------+
 
| count(1) |
 
+----------+
 
|    92819 |
 
+----------+
 
1 row in set (0.93 sec)
 
 
 
MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
+------+-------------+-------+------------+-----------------------+---------------+---------+---------------------------+--------+---------------------------------+
 
| id   | select_type | table | type       | possible_keys         | key           | key_len | ref                       | rows   | Extra                           |
 
+------+-------------+-------+------------+-----------------------+---------------+---------+---------------------------+--------+---------------------------------+
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [testdb]> delete from mysql.column_stats;
 
Query OK, 6 rows affected (0.00 sec)
 
 
 
MariaDB [testdb]> delete from mysql.index_stats;
 
Query OK, 11 rows affected (0.01 sec)
 
 
 
MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ignore index (name) ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
 
 
+----------+
 
| count(1) |
 
+----------+
 
|    92819 |
 
+----------+
 
1 row in set (1.45 sec)
 
 
 
MariaDB [testdb]> 
MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a IGNORE INDEX (name) ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
+------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
 
| id   | select_type | table | type   | possible_keys        | key           | key_len | ref                       | rows   | Extra       |
 
+------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
 
|    1 | SIMPLE      | i     | ALL    | PRIMARY,date,type_id | NULL          | NULL    | NULL                      | 492879 | Using where |
 
|    1 | SIMPLE      | ir    | ref    | invoice_idRec        | invoice_idRec | 4       | testdb.i.idRec       | 1      | Using where |
 
|    1 | SIMPLE      | p     | eq_ref | PRIMARY              | PRIMARY       | 4       | testdb.ir.product_id | 1      | Using index |
 
|    1 | SIMPLE      | a     | ref    | obj_id,obj_table     | obj_id        | 4       | testdb.p.id          | 2      | Using where |
 
+------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [testdb]> show variables like 'version';
 
+---------------+--------------------+
 
| Variable_name | Value              |
 
+---------------+--------------------+
 
| version       | 10.4.8-MariaDB-log |
 
+---------------+--------------------+
 
1 row in set (0.00 sec)
 
 
 
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [testdb]> show create table temp_attributes;
 
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
 
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| temp_attributes | CREATE TABLE `temp_attributes` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `obj_id` int(11) NOT NULL,
 
  `obj_table` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
 
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 
  `type` enum('int','text','double') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
 
  `value_text` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 
  `value_int` int(11) NOT NULL,
 
  `value_double` double NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `obj_id` (`obj_id`),
 
  KEY `obj_table` (`obj_table`),
 
  KEY `value_text` (`value_text`),
 
  KEY `value_int` (`value_int`),
 
  KEY `name` (`name`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1700849 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 STATS_SAMPLE_PAGES=50000 |
 
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [testdb]> show variables like '%persistent%';
 
+--------------------------------------+-------+
 
| Variable_name                        | Value |
 
+--------------------------------------+-------+
 
| innodb_stats_persistent              | ON    |
 
| innodb_stats_persistent_sample_pages | 30    |
 
+--------------------------------------+-------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [testdb]> show indexes from temp_attributes;
 
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table           | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| temp_attributes |          0 | PRIMARY    |            1 | id          | A         |      967119 |     NULL | NULL   |      | BTREE      |         |               |
 
| temp_attributes |          1 | obj_id     |            1 | obj_id      | A         |      967119 |     NULL | NULL   |      | BTREE      |         |               |
 
| temp_attributes |          1 | obj_table  |            1 | obj_table   | A         |          48 |     NULL | NULL   |      | BTREE      |         |               |
 
| temp_attributes |          1 | value_text |            1 | value_text  | A         |       80593 |     NULL | NULL   |      | BTREE      |         |               |
 
| temp_attributes |          1 | value_int  |            1 | value_int   | A         |        7992 |     NULL | NULL   |      | BTREE      |         |               |
 
| temp_attributes |          1 | name       |            1 | name        | A         |         376 |     NULL | NULL   |      | BTREE      |         |               |
 
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
6 rows in set (0.00 sec)



 Comments   
Comment by Marko Mäkelä [ 2020-10-01 ]

MariaDB 10.4.8 was released over a year ago. Is this repeatable with a more recent version? I suspect that MDEV-16402 might play a role. Some regressions remained open until the 10.4.13 release.

Can you produce a repeatable SQL test case, including the table schema and some data?

Comment by Heiki Laaniste [ 2020-10-01 ]

I tested this to same result on mariadb 10.4.13.
I also tested this on the older mariadb 10.3.20 and there the execution plan is good without dropping the index on attributes.name column or using engine independent statistics.

https://www.dropbox.com/s/qz0mxlr7lru2mgt/demo_db_dump.sql.zip?dl=0

This is the mysqldump which I created for demo purpose, contains 4 tables: demo_invoice, demo_inv_rows, demo_product and demo_attributes.
Demo query:

SELECT count(1) FROM demo_invoice i, demo_inv_rows ir LEFT JOIN demo_product p ON (p.id = ir.product_id) 
LEFT JOIN demo_attributes a 
ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';

Comment by Varun Gupta (Inactive) [ 2020-10-02 ]

Can you try to run your query with disabling rowid filter (as a workaround)
SET optimizer_switch='rowid_filter=off';

This line looks very suspicious to me:

  1 | SIMPLE      | a     | ref|filter | obj_id,obj_table,name | obj_table|name | 107|152 | const                     | 301592 (0%) | Using where; Using rowid filter |

Comment by Heiki Laaniste [ 2020-10-02 ]

This changes the plan to good for this query:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 630440554
 
Server version: 10.4.8-MariaDB-log Source distribution
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [demo_db]> explain
 
    -> SELECT count(1) FROM demo_invoice i, demo_inv_rows ir LEFT JOIN demo_product p ON (p.id = ir.product_id) 
    -> LEFT JOIN demo_attributes a 
    -> ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
+------+-------------+-------+------------+-----------------------+----------------+---------+-----------------------+-------------+---------------------------------+
 
| id   | select_type | table | type       | possible_keys         | key            | key_len | ref                   | rows        | Extra                           |
 
+------+-------------+-------+------------+-----------------------+----------------+---------+-----------------------+-------------+---------------------------------+
 
|    1 | SIMPLE      | i     | ALL        | PRIMARY,date,type_id  | NULL           | NULL    | NULL                  | 502172      | Using where                     |
 
|    1 | SIMPLE      | ir    | ref        | invoice_idRec         | invoice_idRec  | 4       | demo_db.i.idRec       | 1           | Using where                     |
 
|    1 | SIMPLE      | p     | eq_ref     | PRIMARY               | PRIMARY        | 4       | demo_db.ir.product_id | 1           | Using index                     |
 
|    1 | SIMPLE      | a     | ref|filter | obj_id,obj_table,name | obj_table|name | 107|152 | const                 | 301592 (0%) | Using where; Using rowid filter |
 
+------+-------------+-------+------------+-----------------------+----------------+---------+-----------------------+-------------+---------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [demo_db]> SET optimizer_switch='rowid_filter=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [demo_db]> explain SELECT count(1) FROM demo_invoice i, demo_inv_rows ir LEFT JOIN demo_product p ON (p.id = ir.product_id)  LEFT JOIN demo_attributes a  ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
 
+------+-------------+-------+--------+-----------------------+---------------+---------+-----------------------+--------+-------------+
 
| id   | select_type | table | type   | possible_keys         | key           | key_len | ref                   | rows   | Extra       |
 
+------+-------------+-------+--------+-----------------------+---------------+---------+-----------------------+--------+-------------+
 
|    1 | SIMPLE      | i     | ALL    | PRIMARY,date,type_id  | NULL          | NULL    | NULL                  | 502172 | Using where |
 
|    1 | SIMPLE      | ir    | ref    | invoice_idRec         | invoice_idRec | 4       | demo_db.i.idRec       | 1      | Using where |
 
|    1 | SIMPLE      | p     | eq_ref | PRIMARY               | PRIMARY       | 4       | demo_db.ir.product_id | 1      | Using index |
 
|    1 | SIMPLE      | a     | ref    | obj_id,obj_table,name | obj_id        | 4       | demo_db.p.id          | 1      | Using where |
 
+------+-------------+-------+--------+-----------------------+---------------+---------+-----------------------+--------+-------------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [demo_db]>

Comment by Heiki Laaniste [ 2020-10-02 ]

Anyway, thanks, I think I'll just turn off this optimizer_switch parameter for good.

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