Details
Description
This is a public part of TODO-3823.
For certain data distributions, histogram code can produce selectivity>1.0 for a column=const equality on a DOUBLE_PREC_HB histogram (I think one can achieve the same on SINGLE_PREC_HB, the point is that it's not JSON_HB histogram).
create table t10 (a int); |
-- Fill the table, will provide INSERTs below
|
analyze table t10 persistent for all; |
set optimizer_trace=1;
|
explain select * from t10 where a in (91303);
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t10 | ALL | NULL | NULL | NULL | NULL | 99840 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) from information_schema.optimizer_trace\G |
*************************** 1. row ***************************
|
json_detailed(json_extract(trace, '$**.selectivity_for_columns')): [ |
[
|
{
|
"column_name": "a", |
"ranges": |
["91303 <= a <= 91303"], |
"selectivity_from_histogram": 18.28543534 |
}
|
]
|
]
|
INSERTs (synthetic data):
create table bucket(a int); # This holds how many rows we hold in a bucket. |
insert into bucket select 1 from seq_1_to_780; |
|
create table one_third_of_bucket(a int); # one-third of a bucket |
insert into one_third_of_bucket select 1 from seq_1_to_260; |
|
create table t10 (a int); |
insert into t10 select 0 from bucket, seq_1_to_4; |
|
insert into t10 select 8693 from one_third_of_bucket; |
insert into t10 select 8694 from one_third_of_bucket; |
insert into t10 select 8695 from one_third_of_bucket; |
|
|
insert into t10 select 34783 from one_third_of_bucket; |
insert into t10 select 34784 from one_third_of_bucket; |
insert into t10 select 34785 from one_third_of_bucket; |
|
|
insert into t10 select 34785 from bucket, seq_1_to_8; |
|
insert into t10 select 65214 from one_third_of_bucket; |
insert into t10 select 65215 from one_third_of_bucket; |
insert into t10 select 65216 from one_third_of_bucket; |
|
insert into t10 select 65216 from bucket, seq_1_to_52; |
|
insert into t10 select 65217 from one_third_of_bucket; |
insert into t10 select 65218 from one_third_of_bucket; |
insert into t10 select 65219 from one_third_of_bucket; |
|
insert into t10 select 65219 from bucket; |
|
|
insert into t10 select 73913 from one_third_of_bucket; |
insert into t10 select 73914 from one_third_of_bucket; |
insert into t10 select 73915 from one_third_of_bucket; |
|
insert into t10 select 73915 from bucket, seq_1_to_40; |
|
|
insert into t10 select 78257 from one_third_of_bucket; |
insert into t10 select 78258 from one_third_of_bucket; |
insert into t10 select 78259 from one_third_of_bucket; |
|
insert into t10 select 91300 from one_third_of_bucket; |
insert into t10 select 91301 from one_third_of_bucket; |
insert into t10 select 91302 from one_third_of_bucket; |
|
insert into t10 select 91302 from bucket, seq_1_to_6; |
|
insert into t10 select 91303 from one_third_of_bucket; |
insert into t10 select 91304 from one_third_of_bucket; |
insert into t10 select 91305 from one_third_of_bucket; |
|
insert into t10 select 91305 from bucket, seq_1_to_8; |
|
insert into t10 select 99998 from one_third_of_bucket; |
insert into t10 select 99999 from one_third_of_bucket; |
insert into t10 select 100000 from one_third_of_bucket; |
Attachments
Issue Links
- relates to
-
MDEV-31327 Histogram range selectivity estimates should be merged, not added.
- Stalled