Does IMMUTABLE property propagate?

From: Petru Ghita <petrutz(at)venaver(dot)info>
To: pgsql-sql mailing list <pgsql-sql(at)postgresql(dot)org>
Subject: Does IMMUTABLE property propagate?
Date: 2010-03-06 02:44:20
Message-ID: 4B91C184.4090906@venaver.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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;

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?
Is this the recommended way to proceed?

Thank you!

Petru Ghita
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB
I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG
=V/BS
-----END PGP SIGNATURE-----

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2010-03-06 07:55:44 Re: Create functions using a function
Previous Message Plugge, Joe R. 2010-03-05 13:58:28 Re: [SQL] PostgreSQL Active-Active Configuration