> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:25116.1277047267@sss.pgh.pa.us...
>> "Davor J." <DavorJ@live.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).
