Thread: lateral function as a subquery - WIP patch
Hello, in the related discussions mentioned on TODO list http://archives.postgresql.org/pgsql-hackers/2009-09/msg00292.php http://archives.postgresql.org/pgsql-hackers/2009-10/msg00991.php (The 1st is rather on SQL, I didn't focuss on it yet.) the implementation is discussed from optimizer/executor's point of view. I'm wondering why not to address the problem at earlier stage: rewrite the range function to a subquery. For example: SELECT * FROM a, b, func(a.i, b.j) as c, d WHERE a.i=b.j and b.j = d.k and c>1 may become SELECT * FROM a, b, <subquery> as c, d WHERE a.i=b.j and b.j = d.k and c>1 where <subquery> is SELECT func(a.i, b.j) FROM a,b WHERE a.i=b.j The WHERE clause of the original query is considered a list of ANDed subclauses. Given 'rt_index' is range table index of the function, only those subclauses are used in the substitution subquery having RT index lower than 'rt_index'. Even with such a partial qualification the subquery can safely exclude (from function calls) rows that the main query won't need anyway. Note that 1. This is rather an alternative to the optimizer/executor focused approach that the past discussions covered. I'm aware of questions about SQL conformance. 2. I only propose this for functions, not for general queries. 3. This draft does not deal with record-returning functions (Although I might have some idea how to treat them.). Is there any obvious reason not to go this way? Attached is my (experimental) implementation. Kind regards, Tony.
Attachment
Antonin Houska <antonin.houska@gmail.com> writes:
> For example:
> SELECT *
> FROM a, b, func(a.i, b.j) as c, d
> WHERE a.i=b.j and b.j = d.k and c>1
> may become
> SELECT *
> FROM a, b, <subquery> as c, d
> WHERE a.i=b.j and b.j = d.k and c>1
> where <subquery> is
> SELECT func(a.i, b.j)
> FROM a,b
> WHERE a.i=b.j
Um ... how do you get the subquery result rows to join to only the
correct rows of the other tables? This looks like an unconstrained join
to me, which is not what I believe the SQL spec for LATERAL to be, and
it doesn't seem especially useful either. (If a subquery could do what
people wanted, we'd not be hearing all the requests for LATERAL.)
regards, tom lane
On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Antonin Houska <antonin.houska@gmail.com> writes: >> For example: > >> SELECT * >> FROM a, b, func(a.i, b.j) as c, d >> WHERE a.i=b.j and b.j = d.k and c>1 > >> may become > >> SELECT * >> FROM a, b, <subquery> as c, d >> WHERE a.i=b.j and b.j = d.k and c>1 > >> where <subquery> is > >> SELECT func(a.i, b.j) >> FROM a,b >> WHERE a.i=b.j > > Um ... how do you get the subquery result rows to join to only the > correct rows of the other tables? This looks like an unconstrained join > to me, which is not what I believe the SQL spec for LATERAL to be, and > it doesn't seem especially useful either. (If a subquery could do what > people wanted, we'd not be hearing all the requests for LATERAL.) I think LATERAL is intended as more or less an unconstrained nested loop with the lateral expression on the inner side, parameterized by value from the outer side. Typically it's a SRF. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Um ... how do you get the subquery result rows to join to only the
>> correct rows of the other tables? �This looks like an unconstrained join
>> to me, which is not what I believe the SQL spec for LATERAL to be, and
>> it doesn't seem especially useful either. �(If a subquery could do what
>> people wanted, we'd not be hearing all the requests for LATERAL.)
> I think LATERAL is intended as more or less an unconstrained nested
> loop with the lateral expression on the inner side, parameterized by
> value from the outer side. Typically it's a SRF.
Um ... if it's parameterized by values from a "current row" of the outer
side, then it's not an unconstrained join. That would be like doing an
inner indexscan join and producing a cross-join result.
regards, tom lane
On Sat, Mar 10, 2012 at 4:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Um ... how do you get the subquery result rows to join to only the >>> correct rows of the other tables? This looks like an unconstrained join >>> to me, which is not what I believe the SQL spec for LATERAL to be, and >>> it doesn't seem especially useful either. (If a subquery could do what >>> people wanted, we'd not be hearing all the requests for LATERAL.) > >> I think LATERAL is intended as more or less an unconstrained nested >> loop with the lateral expression on the inner side, parameterized by >> value from the outer side. Typically it's a SRF. > > Um ... if it's parameterized by values from a "current row" of the outer > side, then it's not an unconstrained join. That would be like doing an > inner indexscan join and producing a cross-join result. True. I just meant that no join filter was implied. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 03/10/2012 02:15 AM, Tom Lane wrote: > Um ... how do you get the subquery result rows to join to only the > correct rows of the other tables? The subquery just restricts the set of rows that the function has to evaluate. The main query is supposed to perform the join. I understand, such a join causes repeated scan of the function if the function is on the inner side. > This looks like an unconstrained join > to me, which is not what I believe the SQL spec for LATERAL to be, o.k., then just forget about my proposal. Thanks for your comments anyway, Tony H.