"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
