Re: immutable functions vs. join for lookups ?

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)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