Re: Slow function in queries SELECT clause.

> "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.
>>
> 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.

A note on performance here: If I declare the fast SQL function
fnc_unit_convert() as STRICT or as SECURITY DEFINER, then I suddenly get
slow performance again (i.e. no apparent inlining).