Re: Slow function in queries SELECT clause.

Thanks Tom,

Your concepts of "inlining" and "black box" really cleared things up for me.
With fnc_unit_convert() written in SQL and declared as STABLE I indeed have
fast performance now.

I appreciate the note on the IMMUTABLE part. The table contents should not
change in a way to affect the functions. So, as far as I understand the
Postgres workings, this shouldn't pose a problem.

Regards,
Davor

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in message
news:25116(dot)1277047267(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> "Davor J." <DavorJ(at)live(dot)com> writes:
>> Suppose 2 functions: factor(int,int) and offset(int, int).
>> Suppose a third function: convert(float,int,int) which simply returns
>> $1*factor($2,$3)+offset($2,$3)
>> All three functions are IMMUTABLE.
>
> You should write the third function as a SQL function, which'd allow it
> to be inlined.
>
>> VERY FAST (half a second):
>> ----------------
>> SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;
>
> In this case both factor() calls are folded to constants, hence executed
> only once.
>
>> VERY SLOW (a minute):
>> ----------------
>> SELECT convert(data, 1, 2) FROM tbl_data;
>
> Without inlining, there's no hope of any constant-folding here.
> The optimizer just sees the plpgsql function as a black box and
> can't do anything with it.
>
> BTW, your later mail shows that the factor() functions are not really
> IMMUTABLE, since they select from tables that presumably are subject to
> change. The "correct" declaration would be STABLE. If you're relying
> on constant-folding to get reasonable application performance, you're
> going to have to continue to mislabel them as IMMUTABLE; but be aware
> that you're likely to have issues any time you do change the table
> contents. The changes won't get reflected into existing query plans.
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>