[MDEV-19474] Histogram statistics are used even with optimizer_use_condition_selectivity=3 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10
Component/s: Optimizer
Labels:
None

Description

Dataset

create table t1(a int);

insert into t1 values (1),(2),(2),(3),(4);

set use_stat_tables='preferably';

set optimizer_use_condition_selectivity=4;

set histogram_size= 255;

analyze table t1;

MariaDB [test]> analyze select * from t1 where a=2;

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 5    | 5.00   |    39.84 |      40.00 | Using where |

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

1 row in set (0.002 sec)

So with optimizer_use_condition_selectivity=4 , we get filtered as 39.84

MariaDB [test]> set optimizer_use_condition_selectivity=3;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> analyze select * from t1 where a=2;

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 5    | 5.00   |    39.84 |      40.00 | Using where |

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

1 row in set (0.002 sec)

So with optimizer_use_condition_selectivity=3 , we get * filtered as 39.84*

MariaDB [test]> flush tables;  // Statistics are flushed, need to read again to use them

Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> analyze select * from t1 where a=2;

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 5    | 5.00   |    25.00 |      40.00 | Using where |

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

1 row in set (0.008 sec)

So the problem here is we don't stop using histogram statistics with optimizer_use_condition_selectivity=3.
When the statistics are flushed, we don't re-read the histogram statistics and that is why we get the different value for filtered after flushing the EITS

Attachments

Activity

Ascending order - Click to sort in descending order

Varun Gupta (Inactive) created issue - 2019-05-14 19:11

Varun Gupta (Inactive) made changes - 2019-05-14 19:19

Field	Original Value	New Value
Description		Dataset {code:sql} create table t1(a int); insert into t1 values (1),(2),(2),(3),(4); set use_stat_tables='preferably'; set optimizer_use_condition_selectivity=4; set histogram_size= 255; analyze table t1; {code} {noformat} MariaDB [test]> analyze select * from t1 where a=2; +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 5 \| 5.00 \| 39.84 \| 40.00 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.002 sec) {noformat} So with optimizer_use_condition_selectivity=4 , we get * filtered as 39.84* {noformat} MariaDB [test]> set optimizer_use_condition_selectivity=3; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> analyze select * from t1 where a=2; +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 5 \| 5.00 \| 39.84 \| 40.00 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.002 sec) {noformat} So with optimizer_use_condition_selectivity=3 , we get * filtered as 39.84* {noformat} MariaDB [test]> flush tables; // Statistics are flushed, need to read again to use them Query OK, 0 rows affected (0.002 sec) MariaDB [test]> analyze select * from t1 where a=2; +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 5 \| 5.00 \| 25.00 \| 40.00 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.008 sec) {noformat} So the problem here is we don't stop using histogram statistics with optimizer_use_condition_selectivity=3. When the statistics are flushed, we don't re-read the histogram statistics and that is why we get the different value for filtered after flushing the EITS

Varun Gupta (Inactive) made changes - 2019-05-14 19:19

Status

Open [ 1 ]

Confirmed [ 10101 ]

Varun Gupta (Inactive) made changes - 2019-05-14 19:23

Description

Dataset

{code:sql}
create table t1(a int);
insert into t1 values (1),(2),(2),(3),(4);
set use_stat_tables='preferably';
set optimizer_use_condition_selectivity=4;
set histogram_size= 255;
analyze table t1;
{code}

{noformat}
MariaDB [test]> analyze select * from t1 where a=2;
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 39.84 | 40.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.002 sec)
{noformat}

So with optimizer_use_condition_selectivity=4 , we get * filtered as 39.84*

{noformat}
MariaDB [test]> set optimizer_use_condition_selectivity=3;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> analyze select * from t1 where a=2;
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 39.84 | 40.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.002 sec)
{noformat}

So with optimizer_use_condition_selectivity=3 , we get * filtered as 39.84*

{noformat}
MariaDB [test]> flush tables; // Statistics are flushed, need to read again to use them
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> analyze select * from t1 where a=2;
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 25.00 | 40.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.008 sec)
{noformat}

So the problem here is we don't stop using histogram statistics with optimizer_use_condition_selectivity=3.
When the statistics are flushed, we don't re-read the histogram statistics and that is why we get the different value for filtered after flushing the EITS

Dataset

{code:sql}
create table t1(a int);
insert into t1 values (1),(2),(2),(3),(4);
set use_stat_tables='preferably';
set optimizer_use_condition_selectivity=4;
set histogram_size= 255;
analyze table t1;
{code}

{noformat}
MariaDB [test]> analyze select * from t1 where a=2;
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 39.84 | 40.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.002 sec)
{noformat}

So with optimizer_use_condition_selectivity=4 , we get *filtered as 39.84*

{noformat}
MariaDB [test]> set optimizer_use_condition_selectivity=3;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> analyze select * from t1 where a=2;
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 39.84 | 40.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.002 sec)
{noformat}

So with optimizer_use_condition_selectivity=3 , we get * filtered as 39.84*

{noformat}
MariaDB [test]> flush tables; // Statistics are flushed, need to read again to use them
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> analyze select * from t1 where a=2;
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 25.00 | 40.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.008 sec)
{noformat}

So the problem here is we don't stop using histogram statistics with optimizer_use_condition_selectivity=3.
When the statistics are flushed, we don't re-read the histogram statistics and that is why we get the different value for filtered after flushing the EITS

Varun Gupta (Inactive) made changes - 2020-05-31 16:57

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2020-05-31 16:57

Assignee	Varun Gupta [ varun ]	Igor Babaev [ igor ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Julien Fritsch made changes - 2020-12-01 12:40

Fix Version/s

10.1 [ 16100 ]

Varun Gupta (Inactive) made changes - 2021-01-26 13:10

Assignee

Igor Babaev [ igor ]

Michael Widenius [ monty ]

Varun Gupta (Inactive) made changes - 2021-01-26 13:10

Assignee

Igor Babaev [ igor ]

Michael Widenius [ monty ]

Michael Widenius made changes - 2021-01-29 14:34

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Michael Widenius made changes - 2021-01-29 14:34

Assignee

Michael Widenius [ monty ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2021-02-09 13:29

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2021-02-16 06:52

Fix Version/s		10.2.37 [ 25112 ]
Fix Version/s		10.3.28 [ 25111 ]
Fix Version/s		10.4.18 [ 25110 ]
Fix Version/s		10.5.9 [ 25109 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Daniel Bartholomew made changes - 2021-02-22 16:01

Fix Version/s		10.2.38 [ 25207 ]
Fix Version/s	10.2.37 [ 25112 ]

Sergei Petrunia made changes - 2021-06-10 10:10

Fix Version/s		10.5.10 [ 25204 ]
Fix Version/s		10.4.19 [ 25205 ]
Fix Version/s		10.3.29 [ 25206 ]
Fix Version/s	10.5.9 [ 25109 ]
Fix Version/s	10.4.18 [ 25110 ]
Fix Version/s	10.3.28 [ 25111 ]

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

Workflow

MariaDB v3 [ 96729 ]

MariaDB v4 [ 156192 ]

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Varun Gupta (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-05-14 19:11

Updated:: 2021-06-10 10:10

Resolved:: 2021-02-16 06:52

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration