From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: Does IMMUTABLE property propagate?
Date: 6 Mar 2010 08:02:42 GMT
Message-ID: <hmt272$9u8$2@reversiblemaps.ath.cx> (raw)
References: <4B91C184.4090906@venaver.info>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: Does IMMUTABLE property propagate?
In-Reply-To: <hmt272$9u8$2@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox