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
