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
regards, jan