[MDEV-5984] EITS: Incorrect filtered% value for single-table select with range access Created: 2014-03-31  Updated: 2014-04-02  Resolved: 2014-04-02

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.9
Fix Version/s: 10.0.11

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: eits


 Description   

filtered% value is incorrect for single-table select and two-keypart key.

create table ten(a int);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 
 
create table t2 (a int, b int, col1 varchar(64), col2 varchar(64), key(a,b));
 
insert into t2 select A.a+10*B.a, C.a+10*D.a, 'filler-data1', 'filler-data2' from ten A, ten B, ten C, ten D;
 
 
 
set histogram_size=100;
 
set optimizer_use_condition_selectivity=4;
 
set use_stat_tables='preferably';
 
analyze table t2 persistent for all;

Then, try this:

explain extended select * from t2 where a in (1,2,3) and b in (1,2,3);
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
 
|    1 | SIMPLE      | t2    | range | a             | a    | 10      | NULL |    9 |     3.37 | Using index condition |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+

The entire WHERE clause is used to construct range access. If we use range access, it will give us 9 rows, and after that 'filtered' should be 100%. Instead, it is 3.37%, and again the optimizer will grossly underestimate #rows.

 Comments   
Comment by Sergei Petrunia [ 2014-03-31 ]

Analysis

== calculate_cond_selectivity_for_table() ==

records_in_column_ranges() is invoked for b.
It returns:
(gdb) p rows/ table_records
$127 = 0.033663366336633659

We also see a quick select with used_key_parts=2 and
selectivity 0.000899.

We multiply these two selectivities (this is wrong) and get:
(gdb) p table->cond_selectivity
$132 = 3.0297029702970299e-05

= table_cond_selectivity() ==
sees that quick select is used
removes its selectivity
The remaining selectivity is:
(gdb) print sel
$136 = 0.033663366336633666

Comment by Sergei Petrunia [ 2014-03-31 ]

Committed a patch.

Generated at Thu Feb 08 07:08:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.