Re: [SQL] Does IMMUTABLE property propagate?
On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: Petru Ghita petr...@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, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
2010/3/25 Louis-David Mitterrand vindex+lists-pgsql-...@apartia.org: On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: Petru Ghita petr...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: 2010/3/25 Louis-David Mitterrand vindex+lists-pgsql-...@apartia.org: On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: Petru Ghita petr...@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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
Louis-David Mitterrand vindex+lists-pgsql-...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane t...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
2010/3/25 Louis-David Mitterrand vindex+lists-pgsql-...@apartia.org: On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: 2010/3/25 Louis-David Mitterrand vindex+lists-pgsql-...@apartia.org: On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: Petru Ghita petr...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
Greg Stark gsst...@mit.edu writes: On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane t...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
On 2010-03-06, Petru Ghita petr...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
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 petr...@venaver.info wrote: -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- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
-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 petr...@venaver.info mailto:petr...@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- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does IMMUTABLE property propagate?
Petru Ghita petr...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 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- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
