Do PostgreSQL pl/pgSQL function callouts introduce performance costs?

Question

This post started with a simple question which my initial searches didn't turn up answers to. Consider the following function:

CREATE OR REPLACE FUNCTION sys.return_jsonb(return_code shared.return_code, return_msg shared.return_msg)
RETURNS jsonb LANGUAGE sql
AS $function$
  SELECT format('{"result": "%s","message": "%s"}', return_code, return_msg)::jsonb; -- AS result;
$function$
;

Consider two scenarios:

RETURN (SELECT sys.return_jsonb(success_code, success_msg))

and

RETURN format('{"result": "%s","message": "%s"}', return_code, return_msg)::jsonb;

The question: Will the function callout be significantly more expensive than the inline version?

Answer 1

It occurred to me that this question would not be difficult to answer empirically on my own. However as it worked out, I found myself answering two questions: one posed by me, one raised by others who commented.

My question first:

CREATE OR REPLACE FUNCTION sys.return_jsonb_tester(return_code shared.return_code, return_msg shared.return_msg)
RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
declare
  return_jsonb jsonb;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    -- v1
    --SELECT sys.return_jsonb(return_code, return_msg) INTO return_jsonb;

    -- v2
    --SELECT format('{"result": "%s","message": "%s"}', return_code, return_msg)::jsonb INTO return_jsonb;
  END LOOP;
  RETURN return_jsonb;
END ;
$function$
;

The function callout (v1) took about 0.2 seconds. the inline version (v2) took about 0.2 seconds.

Essentially no difference. So I can abstract out the boilerplate code at no cost at all, best I can tell.

I share my question and my own answer for the next person who might find this result informative.

But our story doesn't end here.

In my OP, I used LANGUAGE plpgsql for the callout routine sys.return_jsonb(). In point of fact, at this point I found the callout to be a little more expensive than the inline, but not enough so to be of concern. However a_horse_with_no_name comment suggested that I use LANGUAGE sql instead.

I tried this suggestion out. And it was horribly inefficient coming out of the gate--orders of magnitude slower. So I stayed with LANGUAGE plpgsql.

Then another commenter Laurenz Albe suggested a redo, this time without IMMUTABLE. So I ran the experiments again. Four flavors as implied by the names provided here. With times shown:

SELECT sys.sql_immutable('00001', 'It''s awesome!'); -- 1.10s
SELECT sys.sql_not_immutable('00001', 'It''s awesome!'); -- 0.19s
SELECT sys.plpgsql_immutable('00001', 'It''s awesome!'); -- 0.24s
SELECT sys.plpgsql_not_immutable('00001', 'It''s awesome!'); -- 0.26s

So the upshot of all this is that, thanks to these comments I did use the callout, but with the ideal strategory of using LANGUAGE sql but not using IMMUTABLE.

When I was done, the callout basically the same speed as the inline. Final results are what I posted up top in answering my own question. No cost to using a callout. Laurenz points out that "Actually, you are inlining that way, because PostgreSQL will inline SQL functions when appropriate, and removing the IMMUTABLE made it appropriate in this case." The numbers are telling the same story, so that makes sense.

Ironically, my OP subject line asked about callouts to IMMUTABLE functions, but I have now modified that. When all was said and done, the best choice was a callout to a "MUTABLE" SQL function.