pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (18+ messages)  latest in thread

Message-ID: <87u0m4j5p0.fsf@stark.xeocode.com>
Permalink:  ../87u0m4j5p0.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87u0m4j5p0.fsf@stark.xeocode.com

 · 

reply

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