Zoltán Borók-Nagy created IMPALA-15654:
------------------------------------------
Summary: Calcite: null rejection misses filters such as
coalesce(x, 0) > 0, so TPC-DS q78 keeps its outer joins
Key: IMPALA-15654
URL: https://issues.apache.org/jira/browse/IMPALA-15654
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
Calcite keeps both outer joins of TPC-DS q78, although WHERE rejects
NULL-extended rows:
{noformat}
from ss
left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and
ws_customer_sk=ss_customer_sk)
left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and
cs_customer_sk=ss_customer_sk)
where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2002
{noformat}
q78 takes 187.0 s on master (TPC-DS SF10000, 12 executors, PLANNER=CALCITE,
MT_DOP=8, best of 3). The store_sales aggregate (4.33B groups) probes builds of
1.30B and 2.56B rows to return 88 rows. Joined first, ws and cs give 246.27K
rows to filter the store_sales scan.
*Cause* (master 15cc045e2d)
* FILTER_INTO_JOIN (ImpalaCoreRules.java:107-111) converts an outer join only
if Strong proves a conjunct NULL, but coalesce(NULL, 0) > 0 is FALSE. The
original planner evaluates conjuncts with NULL slots (Analyzer.java:4556-4582).
* As inner joins, ws x cs is never tried: no join condition connects them
(ImpalaLoptOptimizeJoinRule.java:810).
*Measured* with a two-part prototype: null rejection by evaluation;
independence estimates and derived ws-cs equalities for aggregate joins. Same
setup, CTE_THRESHOLD=1, one session; other changes make the base faster than
master:
{noformat}
base 154.7 s
null rejection only 151.4 s
both parts 134.7 s
both parts + RUNTIME_FILTER_WAIT_TIME_MS=180000 60.2 s
constant join key fix only (IMPALA-?) 135.5 s
both parts + constant join key fix 129.5 s
{noformat}
* At the default wait, the gain overlaps IMPALA-?.
* Most of the gain needs the scan to wait for this late filter. With all
adopted prototypes, including per-filter waits, it waits 57.7 s, returns 14.58M
rows instead of 5.50B, and q78 takes 62.1 s.
* Only q78 changes in TPC-DS. The tpcds workload's OR form gains nothing.
* Needs tests for the original planner's wrong-result bugs (IMPALA-15439,
IMPALA-15440, IMPALA-15441).
*Proposal:*
* Add evaluation-based null rejection, on by default.
* Add the aggregate-join changes separately, after a TPC-DS/TPC-H EXPLAIN diff.
* Allow long waits for highly selective late filters, unlike IMPALA-12358.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
