From: | Szymon Guz <mabewlun(at)gmail(dot)com> |
---|---|
To: | "Davor J(dot)" <DavorJ(at)live(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Slow function in queries SELECT clause. |
Date: | 2010-06-20 11:23:33 |
Message-ID: | AANLkTimB8-0KZrRbddqgxnZ5TjdgF2t3fFbu2lvx-2V0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2010/6/19 Davor J. <DavorJ(at)live(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Davor J. | 2010-06-20 11:53:52 | Re: Slow function in queries SELECT clause. |
Previous Message | AI Rumman | 2010-06-20 08:55:45 | join vs exists |