From: "Davor J." <DavorJ@live.com>
Subject: Re: Slow function in queries SELECT clause.
Date: Thu, 12 Aug 2010 14:00:36 +0200
Organization: Hub.Org Networking Services
Lines: 35
Message-ID: <i40nqt$s83$1@news.hub.org>
References: <hvj6cg$2dna$1@news.hub.org> <25116.1277047267@sss.pgh.pa.us>
	<hvn2ij$25ql$1@news.hub.org>
To: pgsql-performance@postgresql.org

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