Hello Quanlong Huang, Kurt Deschler, Csaba Ringhofer, Impala Public Jenkins,
I'd like you to reexamine a change. Please visit
http://gerrit.cloudera.org:8080/19682
to look at the new patch set (#2).
Change subject: IMPALA-12006: Improve cardinality estimation for joins
involving multiple conjuncts
......................................................................
IMPALA-12006: Improve cardinality estimation for joins involving multiple
conjuncts
When an inner or outer join involves conjuncts such as the following:
SELECT * FROM t1 inner join
(SELECT a2, MAX(b2) as max_b2 FROM t2 GROUP BY a2) s2
ON t1.a1 = s2.a2 AND t1.b1 = s2.max_b2
the stats for the second conjunct involving the MAX expression gets
added to the 'otherEqJoinStats' list. These stats were being used
only when no other equijoin conjunct (involving base columns) was
present but not for the above case, thus leading to over-estimation.
The main change in this patch is an improvement to the cardinality
estimate for such cases by considering the conjuncts in the
'otherEqJoinStats list' in combination with the equijoin conjuncts.
A second change is the addition of a new boolean query option:
use_multiplied_selectivity_for_join
It defaults to false. Given multiple independent join conjuncts
C1,C2..Cn having comparable selectivity, we sometimes see over-estimation
by several orders of magnitude. Setting the above query option to
TRUE allows the individual selectivities (fraction between 0 and 1)
of the conjuncts to be multiplied instead of taking the minimum. Note
that this applies to all the joins in the query. In the future we can
consider per join hints but in a complex query (or a view that is
shared among users), it is cumbersome to identify exactly where to
set hints.
Testing:
- Added planner test with a combination of outer and inner join
(with use_multiplied_selectivity_for_join enabled and disabled)
- Ran PlannerTest and TpcdsPlannerTest
TODO: run additional tests
Change-Id: I845d778a58404af834f7501fc8157a5a4b4bcc35
---
M be/src/service/query-options.cc
M be/src/service/query-options.h
M common/thrift/ImpalaService.thrift
M common/thrift/Query.thrift
M fe/src/main/java/org/apache/impala/planner/JoinNode.java
M testdata/workloads/functional-planner/queries/PlannerTest/card-outer-join.test
6 files changed, 139 insertions(+), 11 deletions(-)
git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/82/19682/2
--
To view, visit http://gerrit.cloudera.org:8080/19682
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings
Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: I845d778a58404af834f7501fc8157a5a4b4bcc35
Gerrit-Change-Number: 19682
Gerrit-PatchSet: 2
Gerrit-Owner: Aman Sinha <[email protected]>
Gerrit-Reviewer: Aman Sinha <[email protected]>
Gerrit-Reviewer: Csaba Ringhofer <[email protected]>
Gerrit-Reviewer: Impala Public Jenkins <[email protected]>
Gerrit-Reviewer: Kurt Deschler <[email protected]>
Gerrit-Reviewer: Quanlong Huang <[email protected]>
