pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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: Mon, 18 Apr 2005 11:50:49 -0400
Message-ID: <22219.1113839449@sss.pgh.pa.us> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local>

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
>> d) self-join with a function ;)
>> EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
>> FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
>> aaa USING (n);

> That's pretty clever.  
> It sure seems like the server was not caching the results of the
> function...maybe the server thought it was to small a table to bother?  

No, it probably flattened the subquery on sight (looking at the actual
EXPLAIN output would confirm or disprove that).  You could prevent the
flattening by adding OFFSET 0 in the subquery.  However, the SELECT
DISTINCT sub-sub-query is expensive enough, and the join itself is
expensive enough, that you would need an *enormously* expensive
id2username() function to make this a win.

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;

which is much more amenable to join optimization.  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).

			regards, tom lane



view thread (18+ messages)  latest in thread

Message-ID: <22219.1113839449@sss.pgh.pa.us>
Permalink:  ../22219.1113839449@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/22219.1113839449@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com, qnex42@gmail.com
  Subject: Re: immutable functions vs. join for lookups ?
  In-Reply-To: <22219.1113839449@sss.pgh.pa.us>

* 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