Re: Interesting case of IMMUTABLE significantly hurting performance

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Interesting case of IMMUTABLE significantly hurting performance
Дата
Msg-id 12240.1376507119@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Interesting case of IMMUTABLE significantly hurting performance  (Craig Ringer <craig@2ndquadrant.com>)
Список pgsql-performance
Craig Ringer <craig@2ndquadrant.com> writes:
> It's interesting that this variant doesn't seem to be slow:

> create or replace function to_datestamp_immutable(
>     time_int double precision
> ) returns date as $$
>   select date_trunc('day', timestamp 'epoch' + $1 * interval '1
> second')::date;
> $$ language sql immutable;

> and there's no sign it's parsed each time. So it's not just the
> IMMUTABLE flag.

If you're working with timestamp not timestamptz, I think the functions
being called here actually are immutable (they don't have any dependency
on the timezone parameter).  So this function is safely inline-able
and there's no performance hit from multiple executions.

As Pavel mentioned upthread, the safest rule of thumb for SQL functions
that you want to get inlined is to not mark them as to either mutability
or strictness.  That lets the planner inline them without any possible
change of semantics.  (The basic point here is that a function marked
volatile can be expanded to its contained functions even if they're
immutable; but the other way around represents a potential semantic
change, so the planner won't do it.)

            regards, tom lane


В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: queries with DISTINCT / GROUP BY giving different plans
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Index on a range array