Re: immutable functions vs. join for lookups ?

От: Tom Lane
Тема: Re: immutable functions vs. join for lookups ?
Дата: ,
Msg-id: 6776.1113599567@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: immutable functions vs. join for lookups ?  (Enrico Weigelt)
Ответы: Re: immutable functions vs. join for lookups ?  (Enrico Weigelt)
Список: 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, )

Enrico Weigelt <> writes:
> c) CREATE FUNCTION id2username(oid) RETURNS text
>     LANGUAGE 'SQL' IMMUTABLE AS '
>     SELECT username AS RESULT FROM users WHERE uid = $1';

This is simply dangerous.  The function is *NOT* immutable (it is
stable though).  When ... not if ... your application breaks because
you got the wrong answers, you'll get no sympathy from anyone.

The correct question to ask was "if I make a stable function like
this, is it likely to be faster than the join?".  The answer is
"probably not; at best it will be equal to the join".  The best the
planner is likely to be able to do with the function-based query
is equivalent to a nestloop with inner indexscan (assuming there is
an index on users.uid).  If that's the best plan then the join case
should find it too ... but if you are selecting a lot of items rows
then it won't be the best plan.

            regards, tom lane


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

От: Kevin Brown
Дата:
Сообщение: Re: How to improve db performance with $7K?
От: Hannes Dorbath
Дата:
Сообщение: Re: Query Optimizer Failure / Possible Bug