Funções customizadas no PostgreSQL: SQL vs PL/pgSQL, inlining, e por que sua função pode estar matando a performance

Uma função customizada que parece trivial pode ser o maior problema de performance de uma query, enquanto outra de aparência idêntica não custa nada. A diferença é invisível no ponto de chamada — minha_funcao(id, data) se lê igual dos dois jeitos — mas fica bem visível no EXPLAIN. Este post cobre as duas decisões que realmente importam: a LANGUAGE da função e sua categoria de volatilidade.

Duas formas de definir uma função

-- Linguagem SQL: uma única instrução, sem lógica procedural
CREATE FUNCTION localizacao_atual(p_animal_id bigint, p_data date)
RETURNS bigint
LANGUAGE sql
STABLE
AS $$
  SELECT localidade_id
  FROM movimentacoes
  WHERE animal_id = p_animal_id AND data_movimentacao <= p_data
  ORDER BY data_movimentacao DESC
  LIMIT 1
$$;

-- PL/pgSQL: procedural, suporta IF/LOOP/exceptions, múltiplas instruções
CREATE FUNCTION localizacao_atual(p_animal_id bigint, p_data date)
RETURNS bigint
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN (
    SELECT localidade_id
    FROM movimentacoes
    WHERE animal_id = p_animal_id AND data_movimentacao <= p_data
    ORDER BY data_movimentacao DESC
    LIMIT 1
  );
END;
$$;

As duas retornam o mesmo valor para a mesma entrada. Os caminhos de execução não têm nada a ver um com o outro.

Inlining: a feature que muda tudo

Quando uma função LANGUAGE sql contém exatamente uma instrução e está marcada IMMUTABLE ou STABLE, o planner do PostgreSQL pode fazer inline dela — literalmente substituir o corpo da função na query ao redor, como se você tivesse escrito à mão uma subconsulta ou join. Uma vez inlineada, o planner enxerga direto através dela: consegue empurrar predicados para baixo, escolher usar um índice em movimentacoes(animal_id, data_movimentacao) e até dobrar a chamada em um join LATERAL dentro do plano de execução.

Funções LANGUAGE plpgsql nunca são inlineadas, independente da volatilidade. São opacas para o planner — uma caixa-preta chamada uma vez por tupla de entrada, sem visibilidade sobre o que ela lê ou quão seletiva é. Até um wrapper PL/pgSQL de uma linha em volta de uma única instrução SQL paga esse custo.

Volatilidade: STABLE, IMMUTABLE, VOLATILE

Volatilidade é uma promessa que você faz ao planner sobre o comportamento da função, e determina quais otimizações são permitidas:

• VOLATILE (padrão se não especificado) — pode retornar resultados diferentes para a mesma entrada mesmo dentro de uma instrução, e pode ter efeitos colaterais (escritas, avanço de sequence). O planner precisa chamá-la para cada linha, sempre, na ordem exata em que as linhas são produzidas. Sem cache, sem reordenação, não paralela-segura por padrão.
• STABLE — mesmo resultado para a mesma entrada dentro de uma única instrução (ainda pode depender do snapshot da transação atual ou configurações de sessão). Segura para avaliar uma vez por instrução, segura para usar em condições de índice em um scan simples, segura para workers paralelos.
• IMMUTABLE — mesmo resultado para a mesma entrada, sempre, independente de qualquer estado do banco. Pode ser usada em um índice funcional, pode ter constant-folding no parse se os argumentos forem literais.

Rotular errado importa nos dois sentidos: marcar uma função STABLE quando ela na verdade depende de estado mutável produz resultados errados depois que o planner faz cache ou reordena. Deixar uma função genuinamente somente-leitura como VOLATILE (o padrão) bloqueia à toa a elegibilidade para inlining, query paralela e cache por instrução.

Transformando uma chamada de função por linha em LATERAL join

Mesmo uma função LANGUAGE sql STABLE inlineável, chamada uma vez por linha de um conjunto grande, ainda é, funcionalmente, um nested loop. Tornar isso explícito com LATERAL dá um plano que você consegue ler e indexar diretamente, com ou sem o wrapper de função:

-- Opaco: funciona, mas esconde o formato do join
SELECT e.animal_id, localizacao_atual(e.animal_id, p.data_estoque) AS localidade_id
FROM parametros p
JOIN animais_do_contrato(p.contrato_id, p.data_estoque) e ON TRUE;

-- LATERAL explícito: mesmo resultado, index scan visível por animal
SELECT e.animal_id, m.localidade_id
FROM parametros p
JOIN animais_do_contrato(p.contrato_id, p.data_estoque) e ON TRUE
CROSS JOIN LATERAL (
  SELECT localidade_id
  FROM movimentacoes
  WHERE animal_id = e.animal_id AND data_movimentacao <= p.data_estoque
  ORDER BY data_movimentacao DESC
  LIMIT 1
) m;

A segunda forma é efetivamente no que uma função LANGUAGE sql inlineada se transforma internamente — escrevê-la de forma explícita só torna o plano legível sem precisar de \df+ para lembrar o que a função faz.

O índice de apoio

Nada disso ajuda sem o índice certo para a busca por animal:

CREATE INDEX idx_movimentacoes_animal_data
  ON movimentacoes (animal_id, data_movimentacao DESC);

Confirme que ele é realmente usado com EXPLAIN (ANALYZE, BUFFERS) — você quer um Index Scan alimentando o LATERAL, não um Seq Scan relendo movimentacoes para cada animal.

Checklist antes de publicar uma função nova

• Uma única instrução, sem controle de fluxo procedural? Use LANGUAGE sql, não plpgsql.
• Somente leitura e determinística dentro de uma instrução? Marque STABLE.
• Somente leitura e determinística independente de qualquer estado do banco? Marque IMMUTABLE, e considere um índice funcional.
• Chamada uma vez por linha de um conjunto grande? Verifique se está sendo inlineada (o EXPLAIN deve mostrar a tabela subjacente, não um Function Scan opaco) e confira se existe índice compatível na tabela consultada internamente.

Detectando isso em produção

Regressões de performance em chamadas de função são especialmente traiçoeiras porque o texto da query chamadora nunca muda — só o corpo da função, ou uma atualização de estatística que não rodou. O PG Monitoring gera fingerprint das queries pelo texto normalizado e rastreia histórico de plano de execução e tempo por fingerprint, então uma função que silenciosamente para de ser inlineada (por exemplo, depois que alguém "ajudou" reescrevendo-a em PL/pgSQL) aparece como uma regressão de plano e latência na mesma query, não como um mistério.