[
https://issues.apache.org/jira/browse/ASTERIXDB-3341?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ian Maxon updated ASTERIXDB-3341:
---------------------------------
Labels: triaged (was: )
> CBO choosing bad indexes (CH2 Q8)
> ---------------------------------
>
> Key: ASTERIXDB-3341
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-3341
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: COMP - Compiler
> Reporter: Vijay Sarathy
> Assignee: Vijay Sarathy
> Priority: Major
> Labels: triaged
>
> For CH2 Q8:
> explain SELECT DATE_PART_STR(o.o_entry_d, 'year') AS l_year,
> ROUND((SUM(CASE WHEN n2.n_name = 'Germany' THEN ol.ol_amount ELSE 0
> END) / SUM(ol.ol_amount)),2) AS mkt_share
> FROM item i, supplier su, stock s, orders o, o.o_orderline ol, customer c,
> nation n1, nation n2, region r
> WHERE i.i_id = s.s_i_id
> AND ol.ol_i_id = s.s_i_id
> AND ol.ol_supply_w_id = s.s_w_id
> AND s.s_w_id * s.s_i_id MOD 10000 = su.su_suppkey
> AND c.c_id = o.o_c_id
> AND c.c_w_id = o.o_w_id
> AND c.c_d_id = o.o_d_id
> AND n1.n_nationkey = string_to_codepoint(c.c_state)[0]
> AND n1.n_regionkey = r.r_regionkey
> AND ol.ol_i_id < 1000
> AND r.r_name = 'Europe'
> AND su.su_nationkey = n2.n_nationkey
> AND o.o_entry_d BETWEEN '2017-01-01 00:00:00.000000' AND '2018-12-31
> 00:00:00.000000'
> AND i.i_data LIKE '%b'
> AND i.i_id = ol.ol_i_id
> GROUP BY DATE_PART_STR(o.o_entry_d, 'year')
> ORDER BY l_year;
> CBO is picking the index on o_entry_d which is not a good index. It should
> pick the index on ol_i_id, which has a much lower selectivity.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
