Ali Alsuliman created ASTERIXDB-3064:
----------------------------------------
Summary: Nested loop join is used rather than a hash join
Key: ASTERIXDB-3064
URL: https://issues.apache.org/jira/browse/ASTERIXDB-3064
Project: Apache AsterixDB
Issue Type: Bug
Components: COMP - Compiler
Affects Versions: 0.9.6
Reporter: Ali Alsuliman
Assignee: Ali Alsuliman
Fix For: 0.9.7
Nested loop join is used where a hash join should be applicable in the below
query:
{code:java}
select count (*) from part p
join partsupp ps on p.p_name LIKE '%green%' and ps.ps_partkey = p.p_partkey
join lineitem l on ps.ps_partkey = l.l_partkey and p.p_partkey = l.l_partkey
and ps.ps_suppkey = l.l_suppkey; {code}
Plan:
{code:java}
distribute result [$$70]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
project ([$$70])
-- STREAM_PROJECT |UNPARTITIONED|
assign [$$70] <- [{"$1": $$79}]
-- ASSIGN |UNPARTITIONED|
aggregate [$$79] <- [agg-sql-sum($$81)]
-- AGGREGATE |UNPARTITIONED|
exchange
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
aggregate [$$81] <- [agg-sql-count(1)]
-- AGGREGATE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
join (and(eq($$71, $$73), eq($$72, $$73), eq($$77, $$78)))
-- NESTED_LOOP |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
join (eq($$72, $$71))
-- HYBRID_HASH_JOIN [$$71][$$72] |PARTITIONED|
exchange
-- HASH_PARTITION_EXCHANGE [$$71] |PARTITIONED|
project ([$$71])
-- STREAM_PROJECT |PARTITIONED|
select (like($$p.getField("p_name"), "%green%"))
-- STREAM_SELECT |PARTITIONED|
assign [$$71] <- [$$p.getField("p_partkey")]
-- ASSIGN |PARTITIONED|
project ([$$p])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$74, $$p] <- Default.part
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- HASH_PARTITION_EXCHANGE [$$72] |PARTITIONED|
project ([$$77, $$72])
-- STREAM_PROJECT |PARTITIONED|
assign [$$77, $$72] <- [$$ps.getField("ps_suppkey"),
$$ps.getField("ps_partkey")]
-- ASSIGN |PARTITIONED|
project ([$$ps])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$75, $$ps] <- Default.partsupp
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- BROADCAST_EXCHANGE |PARTITIONED|
project ([$$78, $$73])
-- STREAM_PROJECT |PARTITIONED|
assign [$$78, $$73] <- [$$l.getField("l_suppkey"),
$$l.getField("l_partkey")]
-- ASSIGN |PARTITIONED|
project ([$$l])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$76, $$l] <- Default.lineitem
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED| {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
