Repeat execution of stable expressions

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Repeat execution of stable expressions
Date: 2012-03-05 23:15:39
Message-ID: CAHyXU0zM7WCnQ8035Yq2PTFmnmyFWknoT0WAnd8dThyAG1+cmQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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;

If f returns a,b etc. This is true if the function f() is marked
stable or immutable. That it does this for immutable functions is
pretty awful but it's the stable case that I find much more
interesting -- most non-trivial functions that read from the database
are stable. Shouldn't the server be able to detect that function only
needs to be run once? By the way, this isn't just happening with
function calls. I've noticed the same behavior in queries like this:

create view v as
select
(select foo from foo where ...) as foo_1,
(select foo from foo where ...) as foo_2,
from complicated_query;

that when you query from v, you can sometimes see exploding subplans
such that when you pull a field from foo_1, it reruns the lookup on
foo.

So my question is this:
Can stable functions and other similar query expressions be optimized
so that they are not repeat evaluated like that without breaking
anything?

merlin

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Peter van Hardenberg 2012-03-06 00:41:39 Re: Repeat execution of stable expressions
Previous Message Tomas Vondra 2012-03-05 23:03:58 Re: SSD and RAID