Thread: Convincing STABLE functions to run once
Hello all,
I’ve run into this issue in several contexts recently, and wonder if folks here can help clear up my understanding of function volatility. I often have functions which are not truly immutable (they do something minor, like read in configuration information), but the functions themselves are fairly expensive, so I want them to run just once per query. At face value, I feel like STABLE should do what I want, but often it does not. Here is a simple example of what I am talking about (tested on 9.1.9):
--------------------------------------------------------------------------
CREATE TABLE t1(id INT PRIMARY KEY, val TEXT);
-- Using numbers as "text" for convenience
INSERT INTO t1 SELECT generate_series(1,1000), random() * 1000;
-- The real function reads configuration from the DB, and so
-- cannot be truthfully IMMUTABLE
--
-- This function returns 'text' to better match my real case,
-- but is otherwise just for demonstration
--
CREATE OR REPLACE FUNCTION passthru(myval text)
RETURNS text
LANGUAGE plpgsql
STABLE STRICT
AS $function$
DECLARE
BEGIN
RAISE NOTICE 'test';
RETURN myval;
END;
$function$
;
EXPLAIN ANALYZE select * from t1 where val like passthru('55') || '%';
ALTER FUNCTION passthru(text) IMMUTABLE;
EXPLAIN ANALYZE select * from t1 where val like passthru('55') || '%';
-------------------------------------------------------------------------------------
If you run this, you should see two things:
1) When STABLE, the function still runs many, many times (see notices), despite having a fixed input.
2) When switching to IMMUTABLE, the function runs just once (as expected) and the query is orders of magnitude faster.
Is STABLE working as it should in this example? I’ve searched around, and in some threads I see explanations that STABLE only /allows/ the planner to run the function once, but the planner is free to run it as many times as it sees fit. If this is the case, is there a way to alter the function to tell the planner, “trust me, you only want to run this once per query”? In effect, it seems I want something between IMMUTABLE and the current interpretation of STABLE, maybe a SUPERSTABLE designation or something.
I have also seen that wrapping the function in a subselect is a workaround, but it seems unusual to require such a workaround for what seems like a common need.
Thanks for any insight you might have!
Sincerely,
Dan
Dan Wells <dbw2@calvin.edu> writes:
> I've run into this issue in several contexts recently, and wonder if
> folks here can help clear up my understanding of function volatility. I
> often have functions which are not truly immutable (they do something
> minor, like read in configuration information), but the functions
> themselves are fairly expensive, so I want them to run just once per
> query. At face value, I feel like STABLE should do what I want, but
> often it does not.
STABLE tells the system it's *okay* to run the function fewer times than
naive SQL semantics might suggest. There's no *guarantee* that any such
optimization will happen (and in fact, about the only special thing that
currently happens for STABLE functions is that they're considered okay
to use in indexscan qualifications).
What I'd suggest is sticking the expensive function call into a CTE
(a WITH clause). We do guarantee only-once eval for CTEs.
regards, tom lane
On Tue, Sep 9, 2014 at 10:23 AM, Dan Wells <dbw2@calvin.edu> wrote: > Hello all, > > I’ve run into this issue in several contexts recently, and wonder if folks > here can help clear up my understanding of function volatility. I often > have functions which are not truly immutable (they do something minor, like > read in configuration information), but the functions themselves are fairly > expensive, so I want them to run just once per query. At face value, I feel > like STABLE should do what I want, but often it does not. Here is a simple > example of what I am talking about (tested on 9.1.9): > > -------------------------------------------------------------------------- > CREATE TABLE t1(id INT PRIMARY KEY, val TEXT); > > -- Using numbers as "text" for convenience > INSERT INTO t1 SELECT generate_series(1,1000), random() * 1000; > > -- The real function reads configuration from the DB, and so > -- cannot be truthfully IMMUTABLE > -- > -- This function returns 'text' to better match my real case, > -- but is otherwise just for demonstration > -- > CREATE OR REPLACE FUNCTION passthru(myval text) > RETURNS text > LANGUAGE plpgsql > STABLE STRICT > AS $function$ > DECLARE > BEGIN > RAISE NOTICE 'test'; > RETURN myval; > END; > $function$ > ; This is kinda off topic but I'd like to point out your 'passthru' function is a wonderful debugging trick. I write it like this: CREATE OR REPLACE FUNCTION Notice(anyelement) RETURNS anyelement AS $$ BEGIN RAISE NOTICE '%', $1; RETURN $1; END; $$ LANGUAGE PLPGSQL; The reason why that's so useful is that when you have complicated functions that depend on each other it can be kind of a pain to adjust complicated SQL so that it 'raise notices' values you'd want to see -- the passthrough function makes it a snap without adjusting query behavior. merlin
On 09 Sep 2014, at 17:23, Dan Wells <dbw2@calvin.edu> wrote: > I often have functions which are not truly immutable (they do something minor, like read in configuration information),but the functions themselves are fairly expensive, so I want them to run just once per query. At face value,I feel like STABLE should do what I want, but often it does not. Assuming that the part that makes these functions expensive is not the reading of the configuration information, perhapsyou can split your functions such that the expensive part goes into an IMMUTABLE function that takes those (STABLE)configuration values as input? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.