Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
None
-
None
Description
Create the dataset as specified by MDEV-4363.
MariaDB [j10]> set use_stat_tables='preferably';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [j10]> set optimizer_use_condition_selectivity=4;
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
The point of this test is to check skewed data distributions.
The value of 178 is very frequent:
MariaDB [j10]> select (select count(*) from t5 where col2 = 178 ) /(select count(*) from t5);
|
+--------------------------------------------------------------------------+
|
| (select count(*) from t5 where col2 = 178 ) /(select count(*) from t5) |
|
+--------------------------------------------------------------------------+
|
| 0.3301 |
|
+--------------------------------------------------------------------------+
|
1 row in set (0.18 sec)
|
What does EXPLAIN think:
MariaDB [j10]> explain extended select count(*) from t5 where col2 = 178 ;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10000 | 3.60 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
1 row in set, 1 warning (0.00 sec)
|
it thinks that selectivity is 3.6%.
If i try it with a regular, rare value:
|
|
MariaDB [j10]> select (select count(*) from t5 where col2 = 179 ) /(select count(*) from t5);
|
+--------------------------------------------------------------------------+
|
| (select count(*) from t5 where col2 = 179 ) /(select count(*) from t5) |
|
+--------------------------------------------------------------------------+
|
| 0.0001 |
|
+--------------------------------------------------------------------------+
|
1 row in set (0.20 sec)
|
|
|
MariaDB [j10]> explain extended select count(*) from t5 where col2 = 179 ;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10000 | 3.60 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
1 row in set, 1 warning (0.00 sec)
|
It looks like histograms do not allow to distinguish between a frequent and a rare value?
Attachments
Issue Links
- relates to
-
MDEV-4145 Take into account the selectivity of single-table range predicates on non-indexed columns when searching for the best execution plan
-
- Closed
-