On Tue, Mar 6, 2012 at 10:21 AM, Jan Otto <asche@xxxxxx> wrote: > hi, > >> I've complained many times that >> select (f()).*; >> >> will execute f() once for each returned field of f() since the server >> essentially expands that into: >> >> select f().a, f().b; >> >> try it yourself, see: >> create function f(a out text, b out text) returns record as $$ >> begin >> perform pg_sleep(1); >> a := 'a'; b := 'b'; end; >> $$ language plpgsql immutable; > > > i ran into this regularly too. when f() is expensive then i try to rewrite the query so that the > function only get called once per row. > > # explain analyze select (f()).*; > QUERY PLAN > ------------------------------------------------------------------------------------------ > Result (cost=0.00..0.51 rows=1 width=0) (actual time=2001.116..2001.117 rows=1 loops=1) > Total runtime: 2001.123 ms > > # explain analyze select f.* from f() as f; > QUERY PLAN > ------------------------------------------------------------------------------------------------------- > Function Scan on f (cost=0.25..0.26 rows=1 width=64) (actual time=1000.928..1000.928 rows=1 loops=1) > Total runtime: 1000.937 ms yeah -- that's pretty neat, but doesn't seem fit a lot of the cases I bump into. In particular, when stuffing composite types in the field list. You need the type to come back as a scalar so you can expand it a wrapper (especially when layering views). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
