> -----Mensaje original----- > De: Scott Marlowe [mailto:[EMAIL PROTECTED] > 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
