=20
> > -----Mensaje original-----
> > De: Scott Marlowe [mailto:scott.marlowe@[EMAIL PROTECTED]
Enviado el:=20
> > Mi=E9rcoles, 18 de Junio de 2008 17:47
> > Para: Fernando Hevia
>=20
> > >
> > > For complex calculations I have obtained better performance using=20
> > > nested queries. For example:
> > >
> > > select a, b, c select
> > > ( select a, b, a*b as c from ta) subquery1 where c =3D 2;
> > >
> > > This nesting is probably overhead in such a simple case as
> > this, but
> > > in more complex ones and specially with volatile=20
> functions it will=20
> > > provide an improvement.
> >=20
> > I was under the impresion from previous discussions that the query=20
> > planner flattened these out to be the same query. Do you get=20
> > different query plans when you re-arrange this way?
> >=20
>=20
> Take a look at this example (tried on 8.2.7 & 8.1.11):
>=20
> create or replace function test(p1 integer, p2 integer)=20
> returns integer[] as $BODY$ declare
> retval integer[];
> begin
> raise info 'called test(%, %)', p1, p2;
> retval[0] =3D p1 + p2;
> retval[1] =3D p1 * p2;
> retval[2] =3D p1 - p2;
> return retval;
> end;
> $BODY$
> LANGUAGE 'plpgsql' IMMUTABLE;
>=20
> -- In this case function test is called three times:
> pg=3D# select (test(1, 2))[0] as sum, (test(1, 2))[1] as prod,=20
> (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)
>=20
>=20
> -- In this case function test is called only once:
> pg=3D# select res[0] as sum, res[1] as prod, res[2] as dif from=20
> pg-# (select (test(1, 2))::integer[] as res) t ;
> INFO: called test(1, 2)
> sum | prod | dif
> -----+------+-----
> 3 | 2 | -1
> (1 row)
>=20
> I assume the second form will perform better since test is=20
> being called only once.
> I might be missing something in this assumption but at first=20
> glance it seems pretty straightforward.
>=20
> Regards,
> Fernando.
>=20
--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=3D# 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] =3D 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.
--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
|
