2010/6/19 Davor J. <dav...@live.com> > I think I have read what is to be read about queries being prepared in > plpgsql functions, but I still can not explain the following, so I thought > to post it here: > > 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. > > Very simple, right? Now I have very fast AND very slow executing queries on > some 150k records: > > VERY FAST (half a second): > ---------------- > SELECT data*factor(1,2)+offset(1,2) FROM tbl_data; > > VERY SLOW (a minute): > ---------------- > SELECT convert(data, 1, 2) FROM tbl_data; > > The slowness cannot be due to calling a function 150k times. If I define > convert2(float,int,int) to return a constant value, then it executes in > about a second. (still half as slow as the VERY FAST query). > > I assume that factor and offset are cached in the VERY FAST query, and not > in the slow one? If so, why not and how can I "force" it? Currently I need > only one function for conversions. > > Regards, > Davor > > > > Hi, show us the code of those two functions and explain analyze of those queries.
regards Szymon Guz
