Início / dba / Perguntas / 338084
Accepted
onurmx
Asked: 2024-03-27 15:00:59 +0800 CST

O desempenho da função é pior do que a consulta bruta no PostgreSQL

  • 772

Estou tentando extrair todo o desempenho do meu banco de dados PostgreSQL e também quero abstrair minhas definições de consulta da minha camada de aplicativo.

Para fazer isso, estou usando uma função com valor de tabela. No entanto, percebi que as funções apresentam desempenho pior do que as consultas brutas.

Minha definição de tabela é

CREATE TABLE public.entry
(
    id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    topic_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    nstd_upvote_count INT NOT NULL,
    nstd_downvote_count INT NOT NULL,
    create_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    update_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    CONSTRAINT pk_entry PRIMARY KEY (id),
    CONSTRAINT fk_entry_topic_id_topic FOREIGN KEY (topic_id) REFERENCES public.topic(id),
    CONSTRAINT fk_entry_user_id_user FOREIGN KEY (user_id) REFERENCES public."user"(id)
);

CREATE INDEX ix_entry_topic_id_create_timestamp ON entry
(
    topic_id ASC,
    create_timestamp ASC
);

CREATE INDEX ix_entry_user_id_create_timestamp ON entry
(
    user_id ASC,
    create_timestamp ASC
);

Minha definição de função é:

CREATE OR REPLACE FUNCTION public.udf_entry_get_by_id
(
    p_id BIGINT
)
RETURNS TABLE
(
    id BIGINT
    ,topic_id BIGINT
    ,user_id BIGINT
    ,content TEXT
    ,create_timestamp TIMESTAMP(6) WITH TIME ZONE
    ,update_timestamp TIMESTAMP(6) WITH TIME ZONE
)
LANGUAGE sql
AS $func$
SELECT
    e.id
    ,e.topic_id
    ,e.user_id
    ,e.content
    ,e.create_timestamp
    ,e.update_timestamp
FROM public.entry AS e
WHERE e.id = p_id
$func$ STABLE;

Como chamo minha função:

SELECT * FROM udf_entry_get_by_id(13642);

Explique Analise:

Index Scan using pk_entry on entry e  (cost=0.29..2.50 rows=1 width=289) (actual time=0.029..0.032 rows=1 loops=1)
  Index Cond: (id = '13642'::bigint)
Planning Time: 0.237 ms
Execution Time: 0.059 ms

Minha consulta bruta:

SELECT e.id, e.topic_id, e.user_id, e.content, e.create_timestamp, e.update_timestamp FROM public.entry AS e WHERE e.id = 13642;

Explique Analise:

SELECT e.id, e.topic_id, e.user_id, e.content, e.create_timestamp, e.update_timestamp FROM public.entry AS e WHERE e.id = 13642;

Index Scan using pk_entry on entry e  (cost=0.29..2.50 rows=1 width=289) (actual time=0.030..0.033 rows=1 loops=1)
  Index Cond: (id = 13642)
Planning Time: 0.112 ms
Execution Time: 0.063 ms

Minha pergunta é: o tempo de planejamento afeta o desempenho da minha aplicação? Se as funções com valor de tabela são armazenadas em cache, por que o tempo de planejamento das funções é muito maior?

Obrigado pela ajuda.

postgresql

2 respostas

  1. Best Answer

    As funções SQL com valor de tabela não são armazenadas em cache de forma alguma; portanto, do ponto de vista do desempenho, isso é uma perda líquida.

    Há duas coisas que você pode considerar:

    1. escrevendo uma função PL/pgSQL, que utilizará um plano genérico a partir da sexta execução:

      CREATE FUNCTION public.udf_entry_get_by_id (p_id BIGINT) RETURNS TABLE (...)
   LANGUAGE plpgsql STABLE AS
'BEGIN
   RETURN QUERY
      SELECT e.id,
             e.topic_id,
             e.user_id,
             e.content,
             e.create_timestamp,
             e.update_timestamp
      FROM public.entry AS e
      WHERE e.id = p_id;
END;';

    2. escrever uma função SQL com a sintaxe de confirmação padrão, que não armazenará em cache o plano de consulta, mas economizará o esforço de análise da consulta:

      CREATE FUNCTION public.udf_entry_get_by_id (p_id BIGINT) RETURNS TABLE (...)
   STABLE
BEGIN ATOMIC
   SELECT e.id,
          e.topic_id,
          e.user_id,
          e.content,
          e.create_timestamp,
          e.update_timestamp
   FROM public.entry AS e
   WHERE e.id = p_id;
END;
    • 0

  2. A diferença entre seus dois EXPLAIN ANALYZE é de apenas 121 microssegundos, o que é extremamente pequeno.

    EXPLAIN ANALYZE tem alguma sobrecarga e, em consultas tão rápidas, outras sobrecargas, como rede e análise, também se tornam significativas. Ele executa a consulta apenas uma vez, o que significa que o efeito de outras coisas que a máquina está fazendo ao mesmo tempo pode ser significativo, portanto haverá alguma variação, que pode ser muito maior que 121 µs. Basicamente, com tempos tão curtos, não use apenas uma tentativa com EXPLAIN ANALYZE para tomar decisões de otimização.

    Para avaliar essas consultas, é mais preciso executá-las na sua aplicação, muitas vezes, calculando a média do tempo, o que dá uma resposta mais relevante.

    Dito isto, se 121µs são importantes para o seu desempenho, então isso significa...

    • Você está executando a consulta um zilhão de vezes em loop

    Essa é uma má ideia: você obterá um desempenho muito melhor reunindo uma lista de IDs das linhas que deseja capturar e colocando-as todas em uma consulta usando IN(), ou unnest(), ou JOIN com VALUES(). .. ou mover tudo para um JOIN.

    Se você usar um ORM e selecionar uma lista de linhas, às vezes você o pegará em flagrante fazendo uma consulta por linha para buscar relações com as linhas que foram selecionadas. Se o ORM não for burro, ele deverá poder ser configurado para fazê-lo da maneira correta, conforme explicado no parágrafo acima. Se não for possível evitar isso, não é adequado para uso.

    • Você está executando a consulta um zilhão de vezes, mas ela não está em loop

    Por exemplo, você tem um servidor que lida com muitas solicitações e cada um precisa fazer uma dessas consultas. Neste caso você não pode usar o truque acima. Mas se você usar o pool de conexões, poderá usar instruções preparadas persistentes ou funções plpgsql. Você deve verificar se o pool não reinicializa a sessão do banco de dados com DISCARD ALL entre cada uso, pois isso exclui todos os planos armazenados em cache.

    • 0

relate perguntas