Slow function in queries SELECT clause.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux