phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "yuriy bo (JIRA)" <>
Subject [jira] [Created] (PHOENIX-4663) Ability to left join by index on rhs
Date Wed, 21 Mar 2018 14:37:00 GMT
yuriy bo created PHOENIX-4663:

             Summary: Ability to left join by index on rhs
                 Key: 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 [|] = [|]
where lhs.user_id = 123;
There is global  index on lhs.user_id and there is global index on [|].
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

View raw message