From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: Dawid Kuroczko <qnex42@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: immutable functions vs. join for lookups ?
Date: 18 Apr 2005 14:33:15 -0400
Message-ID: <87u0m4j5p0.fsf@stark.xeocode.com> (raw)
In-Reply-To: <22219.1113839449@sss.pgh.pa.us>
References: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local>
<22219.1113839449@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> writes:
> It would be interesting sometime to try to teach the planner about
> inlining SQL-language functions to become joins. That is, given
>
> create function id2name(int) returns text as
> 'select name from mytab where id = $1' language sql stable;
>
> select uid, id2name(uid) from othertab where something;
>
> I think that in principle this could automatically be converted to
>
> select uid, name from othertab left join mytab on (uid = id) where something;
The Inlining of the function is presumably a side-issue. I have tons of
queries that use subqueries in the select list for which the same behaviour
would be appropriate.
Things like
select uid, (select name from mytab where id = uid) as name from othertab ...
> There are some pitfalls though, particularly that you'd have to be able to
> prove that the function's query couldn't return more than one row (else the
> join might produce more result rows than the original query).
Or just have a special join type that has the desired behaviour in that case.
Ie, pretend the query was really
SELECT * FROM othertab LEFT SINGLE JOIN mytab ...
Where "LEFT SINGLE JOIN" is an imaginary syntax that doesn't actually have to
exist in the parser, but exists in the planner/executor and behaves
differently in the case of duplicate matches.
Actually I could see such a syntax being useful directly too.
--
greg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com, qnex42@gmail.com
Subject: Re: immutable functions vs. join for lookups ?
In-Reply-To: <87u0m4j5p0.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox