Обсуждение: Does IMMUTABLE property propagate?
-----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-----
<p>The immutable property had nothing to do with caching results. Postgres never caches the results of functions. The immutableproperty is used top determine if it's safe to use indexes or other plans that avoid evaluating an expression repeatedly.<p><blockquotetype="cite">On 6 Mar 2010 02:45, "Petru Ghita" <<a href="mailto:petrutz@venaver.info">petrutz@venaver.info</a>>wrote:<br /><br />-----BEGIN PGP SIGNED MESSAGE-----<br />Hash: SHA1<br /><br /> Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as<br /> IMMUTABLE, does the queryplanner cache the result of f3 and reuse it<br /> or if you want to get a little more speed you better explicitly define<br/> yourself f3 as IMMUTABLE?<br /><br /> I had an aggregate query like:<br /><br /> select id,<br /> sum(p1*f1(a)/f2(b)as r1,<br /> sum(p2*f1(a)/f2(b) as r2,<br /> ...<br /> sum(pn*f1(a)/f2(b) as rn<br /><br/> ...<br /> group by id;<br /><br /> Where f1(x) and f2(x) were defined as IMMUTABLE.<br /><br /> By the experimentsI ran looks like after defining a new function<br /> f3(a,b):= f1(a)/f2(b) and rewriting the query as:<br /><br/> select id,<br /> sum(p1*f3(a,b) as r1,<br /> sum(p2*f3(a,b) as r2,<br /> ...<br /> sum(pn*f3(a,b)as rn<br /><br /> ...<br /> group by id;<br /><br /> *Looks like* I got a little (5%) improvement in performanceof the<br /> query. Is there a way to find out if the function is re-evaluated each<br /> time?<br /> Is thisthe recommended way to proceed?<br /><br /> Thank you!<br /><br /> Petru Ghita<br /> -----BEGIN PGP SIGNATURE-----<br/> Version: GnuPG v1.4.9 (MingW32)<br /> Comment: Using GnuPG with Mozilla - <a href="http://enigmail.mozdev.org/"target="_blank">http://enigmail.mozdev.org/</a><br /><br /> iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB<br/> I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG<br /> =V/BS<br />-----END PGP SIGNATURE-----<br /><font color="#888888"><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote>
-----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-----
Petru Ghita <petrutz@venaver.info> writes:
> "..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?
No, it means what it says: the function is executed once and replaced
with a constant representing the result value.
regards, tom lane
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.
On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: > Petru Ghita <petrutz@venaver.info> writes: > > "..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? > > No, it means what it says: the function is executed once and replaced > with a constant representing the result value. So for example a function like: CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text AS $$declare outtext text;begin outtext= trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i')); return outtext;end;$$ LANGUAGE plpgsql; could/should be declared immutable? Thanks,
2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>: > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> Petru Ghita <petrutz@venaver.info> writes: >> > "..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? >> >> No, it means what it says: the function is executed once and replaced >> with a constant representing the result value. > > So for example a function like: > > > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text > AS $$ > declare > outtext text; > begin > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i')); > return outtext; > end; > $$ > LANGUAGE plpgsql; > yes it should be declared as immutable. plpgsql function is black box for executor, so you have to use some flag. language sql is different, executor see inside, so there you can not do it. Regards Pavel Stehule > could/should be declared immutable? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: > 2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>: > > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: > >> Petru Ghita <petrutz@venaver.info> writes: > >> > "..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? > >> > >> No, it means what it says: the function is executed once and replaced > >> with a constant representing the result value. > > > > So for example a function like: > > > > > > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text > > AS $$ > > declare > > outtext text; > > begin > > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i')); > > return outtext; > > end; > > $$ > > LANGUAGE plpgsql; > > > > yes it should be declared as immutable. plpgsql function is black box > for executor, so you have to use some flag. language sql is different, > executor see inside, so there you can not do it. Hmm, that's interesting. So for simple functions (like my example) it is better to write them in plain sql? And in that case no 'immutable' flag is necessary?
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> writes:
> Hmm, that's interesting. So for simple functions (like my example) it is
> better to write them in plain sql? And in that case no 'immutable' flag
> is necessary?
If it's just a simple SQL expression, then yes write it as a SQL
function. The planner can "inline" those, eliminating the call overhead
that you'll pay with a plpgsql function.
When you're intending to have a SQL function be inlined, it's probably
best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy
anything and it can complicate matters as to whether inlining is legal.
regards, tom lane
On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > When you're intending to have a SQL function be inlined, it's probably > best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy > anything and it can complicate matters as to whether inlining is legal. I'm confused, I thought it was volatile and strict that prevented inlining. -- greg
2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>: > On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: >> 2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>: >> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> >> Petru Ghita <petrutz@venaver.info> writes: >> >> > "..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? >> >> >> >> No, it means what it says: the function is executed once and replaced >> >> with a constant representing the result value. >> > >> > So for example a function like: >> > >> > >> > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text >> > AS $$ >> > declare >> > outtext text; >> > begin >> > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i')); >> > return outtext; >> > end; >> > $$ >> > LANGUAGE plpgsql; >> > >> >> yes it should be declared as immutable. plpgsql function is black box >> for executor, so you have to use some flag. language sql is different, >> executor see inside, so there you can not do it. > > Hmm, that's interesting. So for simple functions (like my example) it is > better to write them in plain sql? And in that case no 'immutable' flag > is necessary? > sure Pavel > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Greg Stark <gsstark@mit.edu> writes:
> On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> When you're intending to have a SQL function be inlined, it's probably
>> best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy
>> anything and it can complicate matters as to whether inlining is legal.
> I'm confused, I thought it was volatile and strict that prevented inlining.
No: we won't inline if the contained expression is more volatile than
what the function is marked as being. This is a hack that prevents the
inlining logic from defeating kluges that people might be using in
certain applications, namely putting "immutable" or "stable" wrapper
functions around functions that are more volatile than that in the eyes
of the system. You can do that to force the planner to treat things as
immutable/stable in certain contexts; but of course the trick wouldn't
work if the inliner opens up the function and exposes its true contents.
But in the other direction, exposing a definition that is less volatile
than the function's declaration cannot break anything.
Similarly, a STRICT marking prevents inlining unless the planner can
prove that the contained expression would act the same as the function
declaration w.r.t. returning null for any null input; and in all but
the simplest cases it can't prove that.
regards, tom lane