[
https://issues.apache.org/jira/browse/DRILL-1162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16099172#comment-16099172
]
Jinfeng Ni commented on DRILL-1162:
-----------------------------------
Let me explain a little of background about DRILL-2236. DRILL-2236 was
introduced, since at that time Drill was using VolcanoPlanner for join order
planning. The VolcanoPlanner is known to run slow for join ordering,
especially when # of tables joined is large. To speed up the join planning, we
disable {{SwapJoinRule}}. As a result, Drill might end up with a join plan with
bigger input on right side (the hashtable build side) for HashJoin. DRILL-2236
is a kind of solution to address the situation when the join order end up with
bigger table on right side for HashJoin.
In DRILL-2958, we introduced another way to do join planning, by leveraging a
{{HepPlanner}}. The HepPlanner would consider swap join order. However, the
patch in DRILL-2236 may still make sense, just in case where the HepPlanner
ends up with a join order with bigger build side.
In general, I feel the idea of getting bigger on probe side always makes sense.
The question is how to define which table is bigger. Currently, Drill only use
an estimated row count as the criteria. It's possible that the estimated row
count is not accurate, and hence the planner is making a bad choice. But the
situation whether we get a bad plan due to bad row estimate does not mean that
it's a bad idea to have bigger table on the probe side.
Regarding OOM for this issue, if OOM did not happen after disable
`enable_hashjoin_swap' option, it might be that the row count estimation is off
from the real number.
Regarding the bug reported originally in DRILL-2236, we need to both disable
'planner.enable_hashjoin_swap` and 'planner.enable_hep_opt` (which decides
whether Volcano or Hep is used.
{code}
alter session set `planner.enable_hep_opt` = false;
alter session set `planner.enable_hashjoin_swap` = false;
explain plan for
. . . . . . . . . . . > select c.c_custkey, c.c_name, n.n_name
. . . . . . . . . . . > from cp.`tpch/nation.parquet` n,
cp.`tpch/customer.parquet` c
. . . . . . . . . . . > where n.n_nationkey = c.c_nationkey;
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(c_custkey=[$0], c_name=[$1], n_name=[$2])
00-02 Project(c_custkey=[$3], c_name=[$4], n_name=[$1])
00-03 HashJoin(condition=[=($0, $2)], joinType=[inner])
00-05 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=classpath:/tpch/nation.parquet]],
selectionRoot=classpath:/tpch/nation.parquet, numFiles=1,
usedMetadataFile=false, columns=[`n_nationkey`, `n_name`]]])
00-04 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=classpath:/tpch/customer.parquet]],
selectionRoot=classpath:/tpch/customer.parquet, numFiles=1,
usedMetadataFile=false, columns=[`c_nationkey`, `c_custkey`, `c_name`]]])
{code}
Notice that in the above table, the bigger table {{customer}} is on build side,
which is not ideal query plan.
> 25 way join ended up with OOM
> -----------------------------
>
> Key: DRILL-1162
> URL: https://issues.apache.org/jira/browse/DRILL-1162
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Flow, Query Planning & Optimization
> Reporter: Rahul Challapalli
> Assignee: Volodymyr Vysotskyi
> Priority: Critical
> Fix For: Future
>
> Attachments: error.log, oom_error.log
>
>
> git.commit.id.abbrev=e5c2da0
> The below query results in 0 results being returned
> {code:sql}
> select count(*) from `lineitem1.parquet` a
> inner join `part.parquet` j on a.l_partkey = j.p_partkey
> inner join `orders.parquet` k on a.l_orderkey = k.o_orderkey
> inner join `supplier.parquet` l on a.l_suppkey = l.s_suppkey
> inner join `partsupp.parquet` m on j.p_partkey = m.ps_partkey and l.s_suppkey
> = m.ps_suppkey
> inner join `customer.parquet` n on k.o_custkey = n.c_custkey
> inner join `lineitem2.parquet` b on a.l_orderkey = b.l_orderkey
> inner join `lineitem2.parquet` c on a.l_partkey = c.l_partkey
> inner join `lineitem2.parquet` d on a.l_suppkey = d.l_suppkey
> inner join `lineitem2.parquet` e on a.l_extendedprice = e.l_extendedprice
> inner join `lineitem2.parquet` f on a.l_comment = f.l_comment
> inner join `lineitem2.parquet` g on a.l_shipdate = g.l_shipdate
> inner join `lineitem2.parquet` h on a.l_commitdate = h.l_commitdate
> inner join `lineitem2.parquet` i on a.l_receiptdate = i.l_receiptdate
> inner join `lineitem2.parquet` o on a.l_receiptdate = o.l_receiptdate
> inner join `lineitem2.parquet` p on a.l_receiptdate = p.l_receiptdate
> inner join `lineitem2.parquet` q on a.l_receiptdate = q.l_receiptdate
> inner join `lineitem2.parquet` r on a.l_receiptdate = r.l_receiptdate
> inner join `lineitem2.parquet` s on a.l_receiptdate = s.l_receiptdate
> inner join `lineitem2.parquet` t on a.l_receiptdate = t.l_receiptdate
> inner join `lineitem2.parquet` u on a.l_receiptdate = u.l_receiptdate
> inner join `lineitem2.parquet` v on a.l_receiptdate = v.l_receiptdate
> inner join `lineitem2.parquet` w on a.l_receiptdate = w.l_receiptdate
> inner join `lineitem2.parquet` x on a.l_receiptdate = x.l_receiptdate;
> {code}
> However when we remove the last 'inner join' and run the query it returns
> '716372534'. Since the last inner join is similar to the one's before it, it
> should match some records and return the data appropriately.
> The logs indicated that it actually returned 0 results. Attached the log file.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
