phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From yuriy boyun <boyun.yu...@gmail.com>
Subject join without big scans
Date Mon, 19 Mar 2018 07:29:10 GMT
Hello
have the following query
select *
from lhs left join rhs on lhs.id = 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.
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.
Is it possible to force the plan I need for left join case?

Mime
View raw message