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?
