> > -----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. > --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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
