From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Does IMMUTABLE property propagate? |
Date: | 2010-03-06 08:02:42 |
Message-ID: | hmt272$9u8$2@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2010-03-06, Petru Ghita <petrutz(at)venaver(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gianvito Pio | 2010-03-06 11:04:08 | Check type compatibility |
Previous Message | Jasen Betts | 2010-03-06 07:55:44 | Re: Create functions using a function |