Desempenho da função

Estritamente falando, o termo "procedimentos armazenados" aponta para procedimentos SQL no Postgres, introduzidos com o Postgres 11. Relacionado:

• Quando usar o procedimento armazenado/função definida pelo usuário?

Existem também funções , fazendo quase, mas não exatamente o mesmo, e essas estão lá desde o início.

Funções com LANGUAGE sqlsão basicamente apenas arquivos em lote com comandos SQL simples em um wrapper de função (e, portanto, atômico, sempre executado dentro de uma única transação) aceitando parâmetros. Todas as instruções em uma função SQL são planejadas de uma só vez , o que é sutilmente diferente de executar uma instrução após a outra e pode afetar a ordem em que os bloqueios são feitos.

Para mais, a linguagem mais madura é PL/pgSQL ( LANGUAGE plpgsql). Ele funciona bem e foi aprimorado a cada lançamento na última década, mas serve melhor como cola para comandos SQL. Não se destina a cálculos pesados ​​(exceto com comandos SQL).

As funções PL/pgSQL executam consultas como instruções preparadas . A reutilização de planos de consulta em cache elimina algumas despesas gerais de planejamento e os torna um pouco mais rápidos do que instruções SQL equivalentes, o que pode ser um efeito perceptível dependendo das circunstâncias. Também pode ter efeitos colaterais como nesta pergunta relacionada:

• Problemas de PL/pgSQL quando a função é usada duas vezes (problema de cache?)

Isso traz as vantagens e desvantagens das declarações preparadas – conforme discutido no manual . Para consultas em tabelas com distribuição irregular de dados e parâmetros variáveis, o SQL dinâmicoEXECUTE pode ter um desempenho melhor quando o ganho de um plano de execução otimizado para o(s) parâmetro(s) fornecido(s) supera o custo de replanejamento.

Como os planos de execução genéricos do Postgres 9.2 ainda são armazenados em cache para a sessão, mas, citando o manual :

Isso ocorre imediatamente para instruções preparadas sem parâmetros; caso contrário, ocorrerá somente após cinco ou mais execuções produzirem planos cujo custo médio estimado (incluindo despesas gerais de planejamento) seja mais caro do que a estimativa de custo do plano genérico.

Obtemos o melhor dos dois mundos na maioria das vezes (menos algumas sobrecargas adicionais) sem (ab)usar EXECUTE. Detalhes em Novidades do PostgreSQL 9.2 do PostgreSQL Wiki .

O Postgres 12 introduz a variável de servidorplan_cache_mode adicional para forçar planos genéricos ou personalizados. Para casos especiais, use com cuidado.

Você pode ganhar muito com funções do lado do servidor que evitam viagens de ida e volta adicionais para o servidor de banco de dados de seu aplicativo. Faça com que o servidor execute o máximo possível de uma só vez e retorne apenas um resultado bem definido.

Evite o aninhamento de funções complexas, especialmente funções de tabela ( RETURNING SETOF recordou TABLE (...)). As funções são caixas pretas que se apresentam como barreiras de otimização para o planejador de consultas. Eles são otimizados separadamente, não no contexto da consulta externa, o que torna o planejamento mais simples, mas pode resultar em planos menos que perfeitos. Além disso, o custo e o tamanho do resultado das funções não podem ser previstos de forma confiável.

A exceção a essa regra são funções SQL simples ( LANGUAGE sql), que podem ser "inlineadas" - se algumas pré-condições forem atendidas . Leia mais sobre como o planejador de consultas funciona nesta apresentação de Neil Conway (coisas avançadas).

No PostgreSQL, uma função sempre é executada automaticamente dentro de uma única transação . Tudo isso dá certo ou nada. Se ocorrer uma exceção, tudo será revertido. Mas há tratamento de erros ...

É também por isso que as funções não são exatamente "procedimentos armazenados" (mesmo que esse termo seja usado às vezes de forma enganosa). Alguns comandos como VACUUM, CREATE INDEX CONCURRENTLYou CREATE DATABASEnão podem ser executados dentro de um bloco de transação, então eles não são permitidos em funções. (Nem em procedimentos SQL, ainda, a partir do Postgres 11. Isso pode ser adicionado mais tarde.)

Eu escrevi milhares de funções plpgsql ao longo dos anos.

Alguns DOs:

• Use SQL como linguagem de função quando possível, pois o PG pode inline as instruções
• Use IMMUTABLE / STABLE / VOLATILE corretamente, pois o PG pode armazenar resultados em cache se for imutável ou estável
• Use STRICT corretamente, pois o PG pode retornar nulo se qualquer entrada for nula em vez de executar a função
• Considere PL/V8 quando você não puder usar SQL como linguagem de função. É mais rápido que PL/pgSQL em alguns testes não científicos que eu executei
• Use LISTEN / NOTIFY para processos de execução mais longa que podem ocorrer fora da transação
• Considere usar funções para implementar a paginação, pois a paginação baseada em chave pode ser mais rápida do que a paginação baseada em LIMIT
• Certifique-se de testar suas funções por unidade

De um modo geral, mover a lógica do aplicativo para o banco de dados significará que é mais rápido - afinal, ele estará sendo executado mais próximo dos dados.

Acredito (mas não tenho 100% de certeza) que as funções da linguagem SQL são mais rápidas do que aquelas que usam outras linguagens porque não exigem troca de contexto. A desvantagem é que nenhuma lógica de procedimento é permitida.

PL/pgSQL é a linguagem mais madura e completa em recursos - mas para desempenho, C pode ser usado (embora só beneficie funções computacionalmente intensivas)

Você pode fazer algumas coisas muito interessantes usando funções definidas pelo usuário (UDF) no postgresql. Por exemplo, existem dezenas de idiomas possíveis que você pode usar. O pl/sql e o pl/pgsql integrados são capazes e confiáveis ​​e usam um método sandbox para evitar que os usuários façam algo terrivelmente perigoso. As UDFs escritas em C oferecem o máximo em poder e desempenho, pois são executadas no mesmo contexto que o próprio banco de dados. No entanto, é como brincar com fogo, porque mesmo pequenos erros podem causar grandes problemas, com falhas nos back-ends ou corrupção de dados. As linguagens custome pl, como pl/R, pl/ruby, pl/perl e assim por diante, fornecem a capacidade de escrever camadas de banco de dados e de aplicativo nas mesmas linguagens. Isso pode ser útil, pois significa que você não precisa ensinar um programador perl java ou pl/pgsql etc a escrever uma UDF.

Por último, há a linguagem pl/proxy . Essa linguagem UDF permite que você execute seu aplicativo em dezenas ou mais servidores postgresql de back-end para fins de dimensionamento. Ele foi desenvolvido pelo pessoal do Skype e basicamente permite uma solução de escala horizontal para um homem pobre. É surpreendentemente fácil de escrever também.

Agora, quanto à questão do desempenho. Esta é uma área cinzenta. Você está escrevendo um aplicativo para uma pessoa? Ou por 1.000? ou por 10.000.000? A maneira como você cria seu aplicativo e usa UDFs dependerá MUITO de como você está tentando dimensionar. Se você está escrevendo para milhares e milhares de usuários, então a principal coisa que você quer fazer é reduzir ao máximo a carga no banco de dados. UDFs que reduzem a quantidade de dados sendo movidos para fora e de volta para o banco de dados ajudarão a reduzir a carga de E/S. No entanto, se eles começarem a aumentar a carga da CPU, podem ser um problema. De um modo geral, reduzir a carga de E/S é a primeira prioridade, e garantir que as UDFs sejam eficientes para não sobrecarregar suas CPUs é a próxima.