Horton

Thread

  1. lateral function as a subquery - WIP patch

    Antonin Houska <antonin.houska@gmail.com> — 2012-03-10T00:36:18Z

    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.

  2. Re: lateral function as a subquery - WIP patch

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-10T01:15:58Z

    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

  3. Re: lateral function as a subquery - WIP patch

    Robert Haas <robertmhaas@gmail.com> — 2012-03-10T04:11:28Z

    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

  4. Re: lateral function as a subquery - WIP patch

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-10T09:16:23Z

    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

  5. Re: lateral function as a subquery - WIP patch

    Robert Haas <robertmhaas@gmail.com> — 2012-03-10T12:56:20Z

    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

  6. Re: lateral function as a subquery - WIP patch

    Antonin Houska <antonin.houska@gmail.com> — 2012-03-10T17:03:48Z

    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.