I have a pl/pgsql function which does some operations, and finally returns a calculated value, something like:

select MyMainFunc(col1 , col2) from my_table;

where (col1 , col2) is primary key (so they are unique pairs of values). Value returned from MyMainFunc is something like:

return round(  (2.0*param1)  / (2*param1+ param2+ param3) ,10 );    

param1 , param2 and param3 may be repeated in successive calls to MyMaiFunc.

I read that an IMMUTABLE function returns the same result for the same set of arguments without recalculating the result each time the function is called

Based on that: would creating an IMMUTABLE function for this single line of code, and calling that function within this context yield better performance (I mean by saving time needed for calculation)?

return MyInnerFunc(param1,param2,param3);

I am using PostgreSQL 11, and this is the complete main function in case anyone needs details. It calculates similarity between two topics in an ontology using Wu&Palmer algorithm

CREATE OR REPLACE FUNCTION public."WuPalmerSimilarity"(
    p_topic_code1 integer,
    p_topic_code2 integer)
    RETURNS numeric
    LANGUAGE 'plpgsql'

    COST 100


    v_paths1 json;
    v_paths2 json;

    v_paths_count1 integer;
    v_paths_count2 integer;

    v_index1 integer;
    v_index2 integer;

    v_lca_code integer;
    v_lca_level integer;
    v_temp_level integer;   
    v_topic1_lca_path_length integer;
    v_topic2_lca_path_length integer;   
    v_temp_path1_length integer;
    v_temp_path2_length integer;

    v_ontology_depth integer;

    v_lca record;


    if(p_topic_code1 = p_topic_code2) then return 1.0; end if;

    -- get topic 1 paths
    select path_agg into v_paths1 
    from cso_agg_path where topic_code = p_topic_code1;

    -- -- get topic 2 paths
    select path_agg into v_paths2 
    from cso_agg_path where topic_code = p_topic_code2;

    -- count paths
    v_paths_count1 := json_array_length(v_paths1);
    v_paths_count2 := json_array_length(v_paths2);

    -- initialization
    select max(topic_level) into v_ontology_depth from cso_agg_path;

    v_lca_code := 0;
    v_lca_level := 0;
    v_topic1_lca_path_length:= v_ontology_depth;
    v_topic2_lca_path_length:= v_ontology_depth;

    -- loop over topic 1 paths
    for v_index1 in 0..   v_paths_count1-1 loop                 

        -- loop over topic 2 paths                                             
        for v_index2 in 0..   v_paths_count2-1 loop 

            v_lca := public."LCA"(v_paths1::json->v_index1 , v_paths2::json->v_index2 , 0,null,null) ;

            v_temp_path1_length := v_lca.p_topic1_lca_path_length;
            v_temp_path2_length := v_lca.p_topic2_lca_path_length;

            if(v_lca.p_lca_code <> v_lca_code) then

                select topic_level into v_temp_level
                from cso_agg_path where topic_code = v_lca.p_lca_code;

                if( v_temp_level > v_lca_level or 
                    (v_temp_level = v_lca_level and 
                        ( (v_temp_path1_length + v_temp_path2_length) < (v_topic1_lca_path_length + v_topic2_lca_path_length) ) 
                  ) then 
                    v_lca_code := v_lca.p_lca_code;
                    v_lca_level := v_temp_level;    
                    v_topic1_lca_path_length := v_temp_path1_length;                
                    v_topic2_lca_path_length := v_temp_path2_length;                                                                                                                                                                                                 
                end if;

                if(v_temp_path1_length < v_topic1_lca_path_length) then 
                    v_topic1_lca_path_length := v_temp_path1_length;  end if;                                                                                
                if(v_temp_path2_length < v_topic2_lca_path_length) then
                    v_topic2_lca_path_length := v_temp_path2_length;  end if;                                                                                                                                                            
            end if;

        end loop;

    end loop;

   -- ***** THIS IS THE LINE I AM ASKING ABOUT ************
    return round(  (2.0*v_lca_level)  / (2*v_lca_level +v_topic1_lca_path_length+v_topic2_lca_path_length ) ,10 );  


    --return public."WuPalmer"(v_lca_level , v_topic1_lca_path_length , v_topic2_lca_path_length );
   -- ************************************* --



Given that:

p_lca_level integer,
p_topic1_lca_path_length integer,
p_topic2_lca_path_length integer)

RETURNS numeric
LANGUAGE 'plpgsql'

COST 100



 return round(  (2.0*p_lca_level)  / (2*p_lca_level +p_topic1_lca_path_length+p_topic2_lca_path_length ) ,10 );                                                                                                                                                                       

  • because within one query, my main function will not be called for the same arguments more than once. it is called for unique pairs of arguments. while for each call to the function, that last line of code may be calculated for the same values – Nina Jun 20 '20 at 18:20
  • Please provide your version of Postgres and the complete CREATE FUNCTION statement you currently have. Too much speculation and potential for misunderstandings this way .... – Erwin Brandstetter Jun 21 '20 at 15:07
  • @ErwinBrandstetter : Okay, I did – Nina Jun 21 '20 at 16:24
  • 1
    Defining a function that gets data from the database as immutable is a bug waiting to happen. The function should be declared stable. – a_horse_with_no_name Jun 21 '20 at 20:34
  • @a_horse_with_no_name : thanks for the note. I think you mean the outer function, right? – Nina Jun 22 '20 at 15:29

You should declare the function STABLE, that means that it is guaranteed to return the same values for the same argument during the execution of a single SQL statement.

That is because the function selects from the database, so its values can change from one SQL statement to the next, but every invocation within the same statement will return the same results, because they share the same snapshot of the database.

For performance, STABLE is almost as good as IMMUTABLE, except that the function cannot be evaluated a query planning time, but at execution time (which might make a difference for prepared statements).

See this example:

SELECT id FROM atable WHERE col1 = myfunc(1, 42);


Let's assume atable has 100000 rows.

  • if myfunc is IMMUTABLE, it will be executed only once, when the prepared statement is planned

  • if myfunc is STABLE, it will be executed three times, once for each execution of the prepared statement

  • if myfunc is VOLATILE, it will be executed 300000 times, once for each row of atable that is scanned

  • It's not the outer function that I am asking about. I am just asking about the return statement of it. the return statement accepts integers as arguments – Nina Jun 22 '20 at 15:30
  • 1
    @Nina: looking at the outer function I would guess that you will not see any differences in performance when trying to tune a relatively inexpensive call to another function. The cost and runtime of WuPalmerSimilarity is most probably overshadowing any improvement you can achieve when calling the second function – a_horse_with_no_name Jun 22 '20 at 15:34
  • @a_horse_with_no_name : you mean that it's just not worth it. – Nina Jun 22 '20 at 15:36
  • @nina: correct. If you have performance problems, you should try to improve WuPalmerSimilarity. – a_horse_with_no_name Jun 22 '20 at 15:37
  • 1
    Oh, I see. It wasn't clear for me which function you meant. Yes, the function in RETURN can be immutable, but it makes no difference: It will probably be inlined, and it must be evaluated once per function call anyway. – Laurenz Albe Jun 22 '20 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.