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: "'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:23:48
Message-ID: 02d801c8d239$9dc9b530$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.


In response to

Responses

pgsql-sql by date

Next:From: Fernando HeviaDate: 2008-06-19 18:32:53
Subject: Re: using calculated column in where-clause
Previous:From: Scott MarloweDate: 2008-06-18 20:47:08
Subject: Re: using calculated column in where-clause

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