Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
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 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
-