yuriy bo created PHOENIX-4663:
---------------------------------
Summary: Ability to left join by index on rhs
Key: PHOENIX-4663
URL: https://issues.apache.org/jira/browse/PHOENIX-4663
Project: Phoenix
Issue Type: Improvement
Reporter: yuriy bo
It's not possible to execute the following OLTP query using indexes
select *
from lhs left join rhs on [lhs.id|http://lhs.id/] =
[rhs.parent.id|http://rhs.parent.id/]
where lhs.user_id = 123;
There is global index on lhs.user_id and there is global index on
[rhs.parent.id|http://rhs.parent.id/].
Condition by lhs.user_id is very selective so I would like the following
execution plan:
filter table LHS by index USER_ID and then scan RHS by index PARENT_ID.
I can't force phoenix to use "Foreign Key to Primary Key Join Optimization" on
rhs table the problem seems to be related to the fact that left join builds RHS
table into hash table and than uses LHS table for scan so this prevents
"Foreign Key to Primary Key Join Optimization".
if I use INNER JOIN instead left join and swap tables join order then I got the
plan I need and the query executes in milliseconds.
For example it would work if RIGHT JOIN would build RHS table( as opposed LHS
now) as hash and then skip scan LHS by ids.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
