> "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote in message > news:25116.1277047267@xxxxxxxxxxxxxxxx >> "Davor J." <DavorJ@xxxxxxxx> 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). -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
