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@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. > > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
