Slow function in queries SELECT clause.
От | Davor J. |
---|---|
Тема | Slow function in queries SELECT clause. |
Дата | |
Msg-id | hvj6cg$2dna$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: Slow function in queries SELECT clause.
Re: Slow function in queries SELECT clause. |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: