lateral function as a subquery - WIP patch

From: Antonin Houska <antonin(dot)houska(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: lateral function as a subquery - WIP patch
Date: 2012-03-10 00:36:18
Message-ID: 4F5AA202.9020906@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 Content-Type Size
lateral_func.patch text/x-patch 7.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-03-10 00:50:29 Re: pg_crypto failures with llvm on OSX
Previous Message David E. Wheeler 2012-03-10 00:36:08 Advisory Lock BIGINT Values