Zoltán Borók-Nagy created IMPALA-15675:
------------------------------------------
Summary: Skip bloom filters of FK/PK joins whose PK side is an
unfiltered full scan
Key: IMPALA-15675
URL: https://issues.apache.org/jira/browse/IMPALA-15675
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
A runtime filter from an FK/PK join whose PK side is an unfiltered full scan of
the dimension can only remove fact rows with a NULL (or dangling) foreign key.
Impala still builds and waits for it. In short queries the fact scans wait for
these filters, and every probe row pays the bloom filter lookup.
*Data, TPC-DS on 8 executors:*
* SF100: a prototype that drops such filters (when RELY FK constraints are
declared) and puts <fk> IS NOT NULL on the target instead made the 104-query
total 4.7% faster (geomean 0.95).
* SF3000 with PLANNER=CALCITE: Calcite's JOIN_DERIVE_IS_NOT_NULL_FILTER_RULE
adds <pk> IS NOT NULL to the dimension scan, so the scan no longer counts as
unfiltered and the filters survive. Fact scans wait 0.3-0.7 s for 16 MB bloom
filters that remove 1-5% of rows (q68, q6, q19, q81, q87). In q87 the
customer_sk filters (fpp 0.246) remove only the NULLs (2.4% of rows) and the
scans wait 512-735 ms for them, while the date filter arrives after about 190
ms.
* With RUNTIME_FILTER_MODE=LOCAL, q68 goes from 1.55 s to 0.84 s and q19 from
1.10 s to 0.67 s, but q6 from 1.03 s to 20.6 s, so remote filters cannot simply
be turned off.
*Mixed result at SF3000:* ignoring a <pk> IS NOT NULL conjunct whose column has
a null fraction below 10% changed 38 plans: the short queries gained 1.7 s in
total, the longer ones lost 3.2 s (308.5 s -> 310.0 s on those 38 queries),
with identical results.
*Proposal:* drop such filters, but with a stricter rule than the prototypes and
repeated measurements, e.g. only when the expected rejection (null fraction of
the FK) is below a threshold and the filter's arrival would delay a scan.
Prototype (includes the IS NOT NULL handling with the mixed results above):
https://gerrit.cloudera.org/#/c/25100/
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
