Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: lateral_func.patch
Description: text/x-patch (7.1 KB)

Responses

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group