Re: LATERAL

>>>>> "Robert" == Robert Haas <robertmhaas@gmail.com> writes:
>> Just to pick up on some points from the discussion:>> >> 1. LATERAL has to be explicit because it changes the scope
of>>references.  For example, in:>> ... (select ... FROM (select a AS b), (select b)) ...>> the "b" in the second
subselectcould be an outer reference, but>> it can't be a reference to the first subquery; whereas in:>> ... (select
...FROM (select a AS b), LATERAL (select b)) ...>> the "b" in the second subselect refers to the result of the first>>
subselect.
Robert> Can you provide a more complete example?  I'm unable toRobert> construct a working example of this type.  For
example:
Robert> rhaas=# select (select 1 from (select a as b) x, (select b) y) from t1;Robert> ERROR:  subquery in FROM cannot
referto other relations of same queryRobert> level at character 50 

That looks like a bug to me. The spec is explicit that the inner definition
of b is not in scope in the second subquery, and therefore that should parse
as an outer reference.
>> 2. LATERAL in general constrains both the join order and the join>> plan, assuming any lateral references are
actuallymade. 
Robert> Peter seemed to be saying that LATERAL() must syntacticallyRobert> follow the same-level FROM items to which it
refers. Is thatRobert> your understanding also? 

LATERAL references must be to items defined in the same FROM clause and
to the left of the LATERAL.

The relevant language of the spec seems to be:
 a) If TR is contained in a <from clause> FC with no intervening    <query expression>, then the scope clause SC of TR
isthe <select    statement: single row> or innermost <query specification> that    contains FC.  The scope of a range
variableof TR is the <select    list>, <where clause>, <group by clause>, <having clause>, and    <window clause> of
SC,together with every <lateral derived table>    that is simply contained in FC and is preceded by TR, and every
<collectionderived table> that is simply contained in FC and is    preceded by TR, and the <join condition> of all
<joinedtable>s    contained in SC that contain TR. If SC is the <query specification>    that is the <query expression
body>of a simple table query STQ,    then the scope of a range variable of TR also includes the <order    by clause> of
STQ.
>> 4. LATERAL allows some optimizations that aren't currently done, either>> by explicitly rewriting the query, or (in
theory)the optimizer itself>> could consider a lateral plan (I believe Oracle does this). This would>> apply to queries
ofthis form:>> >> SELECT ... FROM t1 LEFT JOIN (t2 JOIN t3 ON (t2.a=t3.a)) on (t1.a=t2.a);>> >> which currently forces
thet2/t3 join to occur first even where t1 is>> small; this could be rewritten with LATERAL as:>> >> SELECT ...>>
FROM t1>>        LEFT JOIN LATERAL (select * from t2 join t3 on (t2.a=t3.a)>>                            where
t2.a=t1.a)s>>        ON true; 
Robert> Well, you haven't actually commuted the joins here - how doRobert> you have in mind for PostgreSQL to execute
this? I'mRobert> guessing that it's something like a nest loop with t1 as theRobert> outer side and the lateral
subqueryas the inner side, soRobert> that the executor repeatedly executesRobert> "select * from t2 join t3 on t2.a =
t3.awhere t2.a = $1"? 

Yup.

The current execution plans for this type of query are completely
disastrous if t1 is small (or qualified so as to be small) and t2 and
t3 are large. Having LATERAL would allow the query to be rewritten to
perform reasonably; a bonus would be for the planner to consider the
lateral join automatically without requiring it to be explicitly
requested.

--
Andrew (irc:RhodiumToad)