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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Hevia | 2008-06-19 18:32:53 | Re: using calculated column in where-clause |
Previous Message | Scott Marlowe | 2008-06-18 20:47:08 | Re: using calculated column in where-clause |