Re: Does IMMUTABLE property propagate?

On 2010-03-06, Petru Ghita <petrutz@venaver.info> wrote:
>  
> Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
> IMMUTABLE, does the query planner cache the result of f3 and reuse it
> or if you want to get a little more speed you better explicitly define
> yourself f3 as IMMUTABLE?
>
> I had an aggregate query like:
>
> select id,
>        sum(p1*f1(a)/f2(b) as r1,
>        sum(p2*f1(a)/f2(b) as r2,
>        ...
>        sum(pn*f1(a)/f2(b) as rn
>
> ...
> group by id;


should be smart enough to know that.

> Where f1(x) and f2(x) were defined as IMMUTABLE.
> By the experiments I ran looks like after defining a new function
> f3(a,b):= f1(a)/f2(b) and rewriting the query as:
>
> select id,
>        sum(p1*f3(a,b) as r1,
>        sum(p2*f3(a,b) as r2,
>        ...
>        sum(pn*f3(a,b) as rn
>
> ...
> group by id;
>
> *Looks like* I got a little (5%) improvement in performance of the
> query. Is there a way to find out if the function is re-evaluated each
> time?

add a " raise notce 'here'; " to it (if plpgsql)

more likely 5% is the function call overhead.