From: Petru Ghita <petrutz@venaver.info>
To: Greg Stark <stark@mit.edu>
Cc: pgsql-sql mailing list <pgsql-sql@postgresql.org>
Subject: Re: Does IMMUTABLE property propagate?
Date: Sat, 06 Mar 2010 17:16:45 +0100
Message-ID: <4B927FED.60209@venaver.info> (raw)
In-Reply-To: <407d949e1003060645i408e0648te67dfd722172f76a@mail.gmail.com>
References: <4B91C184.4090906@venaver.info>
<407d949e1003060645i408e0648te67dfd722172f76a@mail.gmail.com>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Documentation states:
IMMUTABLE indicates that the function cannot modify the database and
always returns the same result when given the same argument values;
that is, it does not do database lookups or otherwise use information
not directly present in its argument list. If this option is given,
any call of the function with all-constant arguments can be
immediately replaced with the function value.
"..immediately replaced with the function value" doesn't mean that the
results of a previously evaluated function for the same parameters are
stored and reused?
The problem here is exactly about evaluating the expression several
times as the result is exactly the same for all the columns in the query.
Greg Stark wrote:
>
> The immutable property had nothing to do with caching results.
> Postgres never caches the results of functions. The immutable
> property is used top determine if it's safe to use indexes or other
> plans that avoid evaluating an expression repeatedly.
>
>> On 6 Mar 2010 02:45, "Petru Ghita" <petrutz@venaver.info
>> <mailto: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;
>
> 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
>>
>>
- --
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
<mailto:pgsql-sql@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuSf+0ACgkQt6IL6XzynQSREQCfQsZpH/cWzMTqVBv4/2D4X+Ib
uBYAniJwbox3bPA4dG/x4vmr0FY+icO9
=8Rvn
-----END PGP SIGNATURE-----
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: petrutz@venaver.info, stark@mit.edu
Subject: Re: Does IMMUTABLE property propagate?
In-Reply-To: <4B927FED.60209@venaver.info>
* 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