[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Repeat execution of stable expressions
From:       Merlin Moncure <mmoncure () gmail ! com>
Date:       2012-03-06 19:29:16
Message-ID: CAHyXU0wpNRmSMUCu_s0=4GnN25UXt5nKhdHGsLjqgNnoy1L9rA () mail ! gmail ! com
[Download RAW message or body]

On Tue, Mar 6, 2012 at 10:21 AM, Jan Otto <asche@me.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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]