[
https://issues.apache.org/jira/browse/ASTERIXDB-3341?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812462#comment-17812462
]
ASF subversion and git services commented on ASTERIXDB-3341:
------------------------------------------------------------
Commit b7cce6e2ea2226c68e0ae66840d00214bd759a96 in asterixdb's branch
refs/heads/master from Vijay Sarathy
[ https://gitbox.apache.org/repos/asf?p=asterixdb.git;h=b7cce6e2ea ]
[ASTERIXDB-3341][COMP]: CBO choosing bad indexes (CH2 Q8)
Change-Id: Iee097528ef3792bfb55636a140e2519b67bcc29e
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/18102
Tested-by: Jenkins <[email protected]>
Reviewed-by: <[email protected]>
Reviewed-by: Vijay Sarathy <[email protected]>
> 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)
