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 |
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 |