Re: Slow function in queries SELECT clause.
От | Szymon Guz |
---|---|
Тема | Re: Slow function in queries SELECT clause. |
Дата | |
Msg-id | AANLkTimB8-0KZrRbddqgxnZ5TjdgF2t3fFbu2lvx-2V0@mail.gmail.com обсуждение исходный текст |
Ответ на | Slow function in queries SELECT clause. ("Davor J." <DavorJ@live.com>) |
Список | pgsql-performance |
2010/6/19 Davor J. <DavorJ@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
В списке pgsql-performance по дате отправления: