[
https://issues.apache.org/jira/browse/PHOENIX-16?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13888384#comment-13888384
]
Maryann Xue commented on PHOENIX-16:
------------------------------------
Actually o.o_orderkey < 100 should be put in on clause, leaving "c.c_custkey
<10" in the where clause:
select * from customer c inner join orders o on c.c_custkey=o.o_custkey and
o.o_custkey<100 where c.c_custkey<10;
The above query should work fine and give good performance.
The reason why "c.c_custkey<10" is currently not allowed in the on clause is
this could cause ambiguity in semantics.
So basically, what's in the ON clause is used as criteria for retrieving data
from the right-hand side table, while the WHERE clause is used to filter the
final result. But here, since this is a inner join, both filtering conditions
can be applied in early stages before performing join. We will add this
optimization soon.
> Join Query optimization
> -----------------------
>
> Key: PHOENIX-16
> URL: https://issues.apache.org/jira/browse/PHOENIX-16
> Project: Phoenix
> Issue Type: Improvement
> Affects Versions: 3.0.0
> Reporter: abhishek
> Assignee: Maryann Xue
>
> select * from t1 inner join t2 on t1.col1 = t2.col2 where t1.id < 1000 and
> t2.id < 1000
> For above query, phoenix first join the tables t1 and t2, and then apply the
> filter t1.id < 1000 and t2.id < 1000. This is very inefficient. This can be
> easily fixed by pushing the filter operation before the join query.
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
