Re: Repeat execution of stable expressions

On Tue, Mar 6, 2012 at 10:21 AM, Jan Otto <asche(at)me(dot)com> 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