[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) added a comment - 2018-08-23 08:21 - edited

* thread #2, stop reason = step over

    frame #0: 0x0000000100554e42 mysqld`best_access_path(join=0x0000625000024378, s=0x0000625000025fd0, remaining_tables=0, idx=0, disable_jbuf=<unavailable>, record_count=1, pos=0x00006250000264f8, loose_scan_pos=0x0000000000000004) at sql_select.cc:6278

   6275	        account here for range/index_merge access. Find out why this is so.

   6276	      */

   6277	      tmp= record_count *

-> 6278	        (s->quick->read_time +

   6279	         (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);

   6281	      loose_scan_opt.check_range_access(join, idx, s->quick);

Target 0: (mysqld) stopped.

(lldb) p s->found_records

(ha_rows) $4 = 8

(lldb) p rnd_records

(double) $5 = 15

Here s->found_records has estimates from the GROUP BY optimization(loosescan)
while the rnd_records which we get through selectivity analysis does not take into account GROUP BY optimization

Varun Gupta (Inactive) added a comment - 2018-08-23 08:21 - edited * thread #2, stop reason = step over frame #0: 0x0000000100554e42 mysqld`best_access_path(join=0x0000625000024378, s=0x0000625000025fd0, remaining_tables=0, idx=0, disable_jbuf=<unavailable>, record_count=1, pos=0x00006250000264f8, loose_scan_pos=0x0000000000000004) at sql_select.cc:6278 6275 account here for range/index_merge access. Find out why this is so. 6276 */ 6277 tmp= record_count * -> 6278 (s->quick->read_time + 6279 (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE); 6280 6281 loose_scan_opt.check_range_access(join, idx, s->quick); Target 0: (mysqld) stopped. (lldb) p s->found_records (ha_rows) $4 = 8 (lldb) p rnd_records (double) $5 = 15 Here s->found_records has estimates from the GROUP BY optimization(loosescan) while the rnd_records which we get through selectivity analysis does not take into account GROUP BY optimization

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) added a comment - 2018-08-23 10:40

Patch
http://lists.askmonty.org/pipermail/commits/2018-August/012817.html

Varun Gupta (Inactive) added a comment - 2018-08-23 10:40 Patch http://lists.askmonty.org/pipermail/commits/2018-August/012817.html

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 added a comment - 2018-08-26 19:33

Review comments in the reply email. Ok to push after addressed.

Sergei Petrunia added a comment - 2018-08-26 19:33 Review comments in the reply email. Ok to push after addressed.

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