From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karl Czajkowski <karlcz@isi.edu>
cc: pgsql-performance@postgresql.org
Subject: Re: optimizing immutable vs. stable function calls?
In-reply-to: <20170118223633.GC23081@moraine.isi.edu>
References: <20170118223633.GC23081@moraine.isi.edu>
Comments: In-reply-to Karl Czajkowski <karlcz@isi.edu>
	message dated "Wed, 18 Jan 2017 14:36:33 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <14963.1484780049.1@sss.pgh.pa.us>
Date: Wed, 18 Jan 2017 17:54:09 -0500
Message-ID: <14964.1484780049@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Karl Czajkowski <karlcz@isi.edu> writes:
> The query planner does not seem to
> recognize that it can eliminate redundant calls to a STABLE function.

No, it doesn't.

> In my case, the function call does not take any arguments and is thus
> trivially independent of row data, and appears in a WHERE clause being
> compared to constants. Why wouldn't the optimizer treat this case the
> same as IMMUTABLE?

"The same as IMMUTABLE" would be to reduce the function to a constant at
plan time, which would be the wrong thing.  It would be valid to execute
it only once at query start, but there's no built-in mechanism for that.

But you could force it by putting it in a sub-SELECT, that is if you
don't like the performance of

      SELECT ... slow_stable_function() ...

try this:

      SELECT ... (SELECT slow_stable_function()) ...

That works because it's an uncorrelated sub-query, which gets evaluated
just once per run.  But the overhead associated with that mechanism is
high enough that forcing it automatically for every stable function would
be a loser.  I'd recommend doing it only where it *really* matters.

			regards, tom lane


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