Skip site navigation (1) Skip section navigation (2)

Re: using calculated column in where-clause

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: "'Fernando Hevia'" <fhevia(at)ip-tel(dot)com(dot)ar>,"'Scott Marlowe'" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "'Patrick Scharrenberg'" <pittipatti(at)web(dot)de>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: using calculated column in where-clause
Date: 2008-06-19 18:32:53
Message-ID: 02dc01c8d23a$e2ab4370$8f01010a@iptel.com.ar (view raw or flat)
Thread:
Lists: pgsql-sql
 
> > -----Mensaje original-----
> > De: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com] Enviado el: 
> > MiƩrcoles, 18 de Junio de 2008 17:47
> > Para: Fernando Hevia
> 
> > >
> > > For complex calculations I have obtained better performance using 
> > > nested queries. For example:
> > >
> > > select a, b, c select
> > >   ( select a, b, a*b as c from ta) subquery1 where c = 2;
> > >
> > > This nesting is probably overhead in such a simple case as
> > this, but
> > > in more complex ones and specially with volatile 
> functions it will 
> > > provide an improvement.
> > 
> > I was under the impresion from previous discussions that the query 
> > planner flattened these out to be the same query.  Do you get 
> > different query plans when you re-arrange this way?
> > 
> 
> Take a look at this example (tried on 8.2.7 & 8.1.11):
> 
> create or replace function test(p1 integer, p2 integer) 
> returns integer[] as $BODY$ declare
>    retval   integer[];
> begin
>    raise info 'called test(%, %)', p1, p2;
>    retval[0] = p1 + p2;
>    retval[1] = p1 * p2;
>    retval[2] = p1 - p2;
>    return retval;
> end;
> $BODY$
> LANGUAGE 'plpgsql' IMMUTABLE;
> 
> -- In this case function test is called three times:
> pg=# select (test(1, 2))[0] as sum, (test(1, 2))[1] as prod, 
> (test(1, 2))[2] as dif;
> INFO:  called test(1, 2)
> INFO:  called test(1, 2)
> INFO:  called test(1, 2)
>  sum | prod | dif
> -----+------+-----
>    3 |    2 |  -1
> (1 row)
> 
> 
> -- In this case function test is called only once:
> pg=# select res[0] as sum, res[1] as prod, res[2] as dif from 
> pg-# (select (test(1, 2))::integer[] as res) t ;
> INFO:  called test(1, 2)
>  sum | prod | dif
> -----+------+-----
>    3 |    2 |  -1
> (1 row)
> 
> I assume the second form will perform better since test is 
> being called only once.
> I might be missing something in this assumption but at first 
> glance it seems pretty straightforward.
> 
> Regards,
> Fernando.
> 
--Follow up--

When I use one of the inner-query columns as a condition for the outer-query
the function is being called again:

pg=# select res[0] as sum, res[1] as prod, res[2] as dif from
pg-# (select (test(1, 2))::integer[] as res) t
pg-# where res[0] = 3;
INFO:  called test(1, 2)
INFO:  called test(1, 2)
 sum | prod | dif
-----+------+-----
   3 |    2 |  -1
(1 row)

Seems this blows away my theory, at least part of it.


In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2008-06-19 18:38:24
Subject: Re: using calculated column in where-clause
Previous:From: Fernando HeviaDate: 2008-06-19 18:23:48
Subject: Re: using calculated column in where-clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group