[MDEV-17039] Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.0.37, 10.1.36, 10.2.18, 10.3.10, 10.4.0
Component/s: Optimizer
Labels:
None

Description

MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),

    (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);

Query OK, 15 rows affected (0.01 sec)

Records: 15  Duplicates: 0  Warnings: 0

Case 1:

MariaDB [test]> set @@optimizer_use_condition_selectivity=4;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= PREFERABLY;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain  SELECT 1 FROM t1 AS t1_outer WHERE

    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);

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

| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref             | rows | Extra                    |

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

|    1 | PRIMARY      | t1_outer    | index  | a             | a            | 10      | NULL            |   16 | Using where; Using index |

|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | test.t1_outer.a |    1 |                          |

|    2 | MATERIALIZED | t1          | range  | NULL          | a            | 5       | NULL            |    5 | Using index for group-by |

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

3 rows in set (0.01 sec)

Case 2:

MariaDB [test]> set @@optimizer_use_condition_selectivity=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= NEVER;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain  SELECT 1 FROM t1 AS t1_outer WHERE

    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);

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

| id   | select_type  | table       | type  | possible_keys | key  | key_len | ref                | rows | Extra                    |

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

|    1 | PRIMARY      | <subquery2> | ALL   | distinct_key  | NULL | NULL    | NULL               |    4 |                          |

|    1 | PRIMARY      | t1_outer    | ref   | a             | a    | 5       | <subquery2>.max(b) |    4 | Using index              |

|    2 | MATERIALIZED | t1          | range | NULL          | a    | 5       | NULL               |    4 | Using index for group-by |

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

3 rows in set (0.00 sec)

Attachments

Issue Links

is part of

MDEV-15253 Default optimizer setting changes for MariaDB 10.4

Closed

Activity

Ascending order - Click to sort in descending order

Varun Gupta (Inactive) created issue - 2018-08-23 07:18

Varun Gupta (Inactive) made changes - 2018-08-23 07:24

Field	Original Value	New Value
Description		{noformat} MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); Query OK, 0 rows affected (0.02 sec) MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), -> (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); Query OK, 15 rows affected (0.01 sec) Records: 15 Duplicates: 0 Warnings: 0 MariaDB [test]> set @@optimizer_use_condition_selectivity=4; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> set @@use_stat_tables= PREFERABLY; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+ \| 1 \| PRIMARY \| t1_outer \| index \| a \| a \| 10 \| NULL \| 16 \| Using where; Using index \| \| 1 \| PRIMARY \| <subquery2> \| eq_ref \| distinct_key \| distinct_key \| 4 \| test.t1_outer.a \| 1 \| \| \| 2 \| MATERIALIZED \| t1 \| range \| NULL \| a \| 5 \| NULL \| 5 \| Using index for group-by \| +------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+ 3 rows in set (0.01 sec) MariaDB [test]> set @@optimizer_use_condition_selectivity=1; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> set @@use_stat_tables= NEVER; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+ \| 1 \| PRIMARY \| <subquery2> \| ALL \| distinct_key \| NULL \| NULL \| NULL \| 4 \| \| \| 1 \| PRIMARY \| t1_outer \| ref \| a \| a \| 5 \| <subquery2>.max(b) \| 4 \| Using index \| \| 2 \| MATERIALIZED \| t1 \| range \| NULL \| a \| 5 \| NULL \| 4 \| Using index for group-by \| +------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+ 3 rows in set (0.00 sec) {noformat}

Varun Gupta (Inactive) made changes - 2018-08-23 07:26

Description

{noformat}
MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
    -> (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0

MariaDB [test]> set @@optimizer_use_condition_selectivity=4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= PREFERABLY;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| 1 | PRIMARY | t1_outer | index | a | a | 10 | NULL | 16 | Using where; Using index |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | test.t1_outer.a | 1 | |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 5 | Using index for group-by |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
3 rows in set (0.01 sec)

MariaDB [test]> set @@optimizer_use_condition_selectivity=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= NEVER;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 4 | |
| 1 | PRIMARY | t1_outer | ref | a | a | 5 | <subquery2>.max(b) | 4 | Using index |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 4 | Using index for group-by |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
3 rows in set (0.00 sec)

{noformat}

{noformat}
MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
    -> (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0
{noformat}

*Case 1*:
{noformat}
MariaDB [test]> set @@optimizer_use_condition_selectivity=4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= PREFERABLY;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| 1 | PRIMARY | t1_outer | index | a | a | 10 | NULL | 16 | Using where; Using index |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | test.t1_outer.a | 1 | |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 5 | Using index for group-by |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
3 rows in set (0.01 sec)
{noformat}

*Case 2*:
{noformat}
MariaDB [test]> set @@optimizer_use_condition_selectivity=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= NEVER;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 4 | |
| 1 | PRIMARY | t1_outer | ref | a | a | 5 | <subquery2>.max(b) | 4 | Using index |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 4 | Using index for group-by |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
3 rows in set (0.00 sec)
{noformat}

Varun Gupta (Inactive) made changes - 2018-08-23 07:59

Description

{noformat}
MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
    -> (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0
{noformat}

*Case 1*:
{noformat}
MariaDB [test]> set @@optimizer_use_condition_selectivity=4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= PREFERABLY;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| 1 | PRIMARY | t1_outer | index | a | a | 10 | NULL | 16 | Using where; Using index |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | test.t1_outer.a | 1 | |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 5 | Using index for group-by |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
3 rows in set (0.01 sec)
{noformat}

*Case 2*:
{noformat}
MariaDB [test]> set @@optimizer_use_condition_selectivity=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= NEVER;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 4 | |
| 1 | PRIMARY | t1_outer | ref | a | a | 5 | <subquery2>.max(b) | 4 | Using index |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 4 | Using index for group-by |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
3 rows in set (0.00 sec)
{noformat}

{noformat}
MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
    (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0
{noformat}

*Case 1*:
{noformat}
MariaDB [test]> set @@optimizer_use_condition_selectivity=4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= PREFERABLY;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
| 1 | PRIMARY | t1_outer | index | a | a | 10 | NULL | 16 | Using where; Using index |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | test.t1_outer.a | 1 | |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 5 | Using index for group-by |
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
3 rows in set (0.01 sec)
{noformat}

*Case 2*:
{noformat}
MariaDB [test]> set @@optimizer_use_condition_selectivity=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @@use_stat_tables= NEVER;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
    -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 4 | |
| 1 | PRIMARY | t1_outer | ref | a | a | 5 | <subquery2>.max(b) | 4 | Using index |
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 4 | Using index for group-by |
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
3 rows in set (0.00 sec)
{noformat}

Varun Gupta (Inactive) made changes - 2018-08-23 10:19

Summary

Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selecitivity=4

Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selecitivity=4 and use_stat_tables= PREFERABLY

Varun Gupta (Inactive) made changes - 2018-08-23 10:20

Summary

Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selecitivity=4 and use_stat_tables= PREFERABLY

Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY

Varun Gupta (Inactive) made changes - 2018-08-23 10:44

Status

Open [ 1 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2018-08-23 10:44

Assignee	Varun Gupta [ varun ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Petrunia made changes - 2018-08-26 19:33

Assignee	Sergei Petrunia [ psergey ]	Varun Gupta [ varun ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Varun Gupta (Inactive) made changes - 2018-08-28 23:07

Fix Version/s		10.0.37 [ 22917 ]
Fix Version/s		10.1.36 [ 23117 ]
Fix Version/s		10.2.18 [ 23112 ]
Fix Version/s		10.3.10 [ 23140 ]
Fix Version/s		10.4.0 [ 23115 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Varun Gupta (Inactive) made changes - 2018-09-19 10:39

Link

This issue is part of ~~MDEV-15253~~ [ ~~MDEV-15253~~ ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89038 ]

MariaDB v4 [ 154841 ]

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Varun Gupta (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-08-23 07:18

Updated:: 2018-09-19 10:39

Resolved:: 2018-08-28 23:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration