To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: Does IMMUTABLE property propagate?
Date: 6 Mar 2010 08:02:42 GMT
Organization: Dis (not Dat) Organisation
Lines: 45
Message-ID: <hmt272$9u8$2@reversiblemaps.ath.cx>
References: <4B91C184.4090906@venaver.info>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: slrn/pre1.0.0-2 (Linux)

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.