Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

238
Visualizações
Do PostgreSQL pl/pgSQL function callouts introduce performance costs?

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?

over 4 years ago · Santiago Trujillo
1 Respostas
Responde à pergunta

0

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.

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda