Re: immutable functions vs. join for lookups ?

От: Tom Lane
Тема: Re: immutable functions vs. join for lookups ?
Дата: ,
Msg-id: 22219.1113839449@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: immutable functions vs. join for lookups ?  ("Merlin Moncure")
Ответы: Re: immutable functions vs. join for lookups ?  (Greg Stark)
Список: pgsql-performance

Скрыть дерево обсуждения

immutable functions vs. join for lookups ?  (Enrico Weigelt, )
 Re: immutable functions vs. join for lookups ?  (Tom Lane, )
  Re: immutable functions vs. join for lookups ?  (Enrico Weigelt, )
   Re: immutable functions vs. join for lookups ?  (Jaime Casanova, )
    Re: immutable functions vs. join for lookups ?  (Enrico Weigelt, )
     Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
 Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
 Re: immutable functions vs. join for lookups ?  ("Merlin Moncure", )
  Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
   Re: immutable functions vs. join for lookups ?  ("Jim C. Nasby", )
    Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )
     Re: immutable functions vs. join for lookups ?  (Christopher Kings-Lynne, )
     Re: immutable functions vs. join for lookups ?  (Christopher Kings-Lynne, )
  Re: immutable functions vs. join for lookups ?  (Tom Lane, )
   Re: immutable functions vs. join for lookups ?  (Greg Stark, )
    Re: immutable functions vs. join for lookups ?  (Tom Lane, )
     Re: immutable functions vs. join for lookups ?  (Enrico Weigelt, )
      Re: immutable functions vs. join for lookups ?  (Dawid Kuroczko, )

"Merlin Moncure" <> 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


В списке pgsql-performance по дате сообщения:

От: Bruce Momjian
Дата:
Сообщение: Re: How to improve db performance with $7K?
От: Alex Turner
Дата:
Сообщение: Re: How to improve db performance with $7K?