Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Explain for Q8 shows negative selectivity when run against DBT3 scales >= 10. I tried with SF10, and SF30. Output:
EXPLAIN EXTENDED
select o_year, sum(case when nation = 'IRAQ' then volume else 0 end) / sum(volume) as mkt_share
from
(
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and s_nationkey = n2.n_nationkey
and o_orderdate between date('1995-01-01') and date ('1996-12-31')
and p_type = 'STANDARD ANODIZED BRASS'
) as all_nations
group by o_year
order by o_year;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | 20.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | n1 | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3.region.r_regionkey | 5 | 100.00 | Using index |
| 1 | SIMPLE | n2 | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using join buffer (flat, BNL join) |
| 1 | SIMPLE | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3.n2.n_nationkey | 12000 | 100.00 | Using index |
| 1 | SIMPLE | customer | ref | PRIMARY,i_c_nationkey | i_c_nationkey | 5 | dbt3.n1.n_nationkey | 180000 | 100.00 | Using index |
| 1 | SIMPLE | part | ALL | PRIMARY | NULL | NULL | NULL | 6000000 | -0.11 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | orders | ref | PRIMARY,i_o_orderdate,i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 15 | 49.88 | Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity | i_l_suppkey | 5 | dbt3.supplier.s_suppkey | 599 | 100.00 | Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gliffy Diagrams
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
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Dataset for a small example
The exact steps to reproduce are: on a fresh server run:
use test;
source /tmp/mdev4350-another-case-dataset.sql
set histogram_size=100;
set histogram_type='SINGLE_PREC_HB';
analyze table t1 persistent for all;
set optimizer_use_condition_selectivity=4;
set use_stat_tables='complementary';
explain extended select * from t1 where a='';
A fix for the bug has been pushed into maria-10.0-mwl253.
I also experience a similar effect on a small dataset.
My settings:
--------------------------------------------------
---------------+--------------------------------------------------
---------------+What I get:
set optimizer_use_stat_tables='complementary';
set optimizer_use_condition_selectivity=4;
MariaDB [j10]> explain extended select * from t1 where a='';
-----
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-------------------------------------------------------------------------1 row in set, 1 warning (0.00 sec)