Início / dba / Perguntas / 294532
Accepted
LaVache
Asked: 2021-06-20 01:59:01 +0800 CST

Como uma função barata em um SELECT pode tornar toda a consulta lenta?

  • 772

Estou usando o Postgres 13.3 com consultas internas e externas que produzem apenas uma única linha (apenas algumas estatísticas sobre contagens de linhas).

Não consigo descobrir por que o Query2 abaixo é muito mais lento que o Query1. Eles devem basicamente ser quase exatamente os mesmos, talvez alguns ms de diferença no máximo ...

Consulta1: leva 49 segundos

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count
FROM t1;

Query2: leva 4 minutos e 30 segundos

E eu adicionei apenas uma linha:

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count,
            -- the line below is the only difference to Query1:
            util.divide_ints_and_get_percentage_string(todo_count, all_count) AS todo_percentage
FROM t1;

Antes desse ponto, e com algumas colunas extras na consulta externa (o que deveria ter feito quase nenhuma diferença), a consulta inteira era incrivelmente lenta, como 25 minutos, o que acho que foi devido ao inlining talvez? Daí a OFFSET 0adição em ambas as consultas (o que ajuda muito).

Eu também tenho trocado entre usar os CTEs acima vs subconsultas, mas com o OFFSET 0incluído não parece fazer nenhuma diferença.

Definições das funções que estão sendo chamadas em Query2:

CREATE OR REPLACE FUNCTION util.ratio_to_percentage_string(FLOAT, INTEGER) RETURNS TEXT AS $$ BEGIN
    RETURN ROUND($1::NUMERIC * 100, $2)::TEXT || '%';
END; $$ LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(BIGINT, BIGINT) RETURNS TEXT AS $$ BEGIN
    
    RETURN CASE 
        WHEN $2 > 0 THEN util.ratio_to_percentage_string($1::FLOAT / $2::FLOAT, 2)
        ELSE 'divide_by_zero' 
        END
        ;

END; $$ LANGUAGE plpgsql IMMUTABLE;

Como você pode ver, é uma função muito simples, que está sendo chamada apenas uma vez, a partir de uma única linha que a coisa toda produz. Como isso pode causar uma desaceleração tão grande? E por que está afetando se o Postgres inline a subconsulta inicial / CTE? (Ou o que mais pode estar acontecendo aqui?)

Além disso, não importa o que a função faz, simplesmente substituí-la por uma função que não faz nada além de retornar uma TEXTstring hellocausa exatamente a mesma lentidão da consulta interna inicial. Portanto, não se trata de algo que a função "faça", mas mais como algum tipo de efeito "gato de Schrödinger", onde coisas na consulta externa estão afetando como a consulta interna é executada inicialmente. Por que uma pequena mudança simples na consulta externa (que basicamente tem efeito zero no desempenho) afeta a consulta interna inicial?

EXPLAIN ANALYZEsaídas:

postgresql postgresql-performance

2 respostas

  1. Best Answer

    A função inlining é importante e se aplica aqui também. Sua função PL/pgSQL não pode ser embutida. (Além de ser um exagero chamar outra função para a expressão trivial.) Mas como ainda é muito barato e só é chamado uma vez, não é o problema aqui.

    Se você usa o OFFSET 0hack ou WITH CTE t1 AS MATERIALIZEDo , isso evita avaliações repetidas. (Se você for usar o OFFSET 0hack, você também pode usar uma subconsulta um pouco mais barata, mas a maneira limpa no Postgres moderno é umaMATERIALIZED CTE.) Esse também não é o problema . (Ou não mais, depois que você impediu com sucesso a avaliação repetida.)

    A questão mais importante é o paralelismo . As funções do usuário são PARALLEL UNSAFEpor padrão.O manual:

    PARALLEL UNSAFEindica que a função não pode ser executada em modo paralelo e a presença de tal função em uma instrução SQL força um plano de execução serial. Este é o padrão.

    AudaciosoMinha ênfase em

    Seu 1º plano de consulta (rápido) mostra 2x Parallel Seq Scane 1xParallel Index Only Scan .
    Seu segundo plano de consulta (lento) não tem consultas paralelas. Dano feito.

    Solução

    Marque suas funções PARALLEL SAFE(porque elas se qualificam!) e o problema desaparece. Relacionado:

    Melhor solução

    Executei testes de desempenho com algumas variantes. Ver:

    Esta função equivalente é substancialmente mais rápida e pode ser embutida:

    CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(bigint, bigint)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT CASE WHEN $2 = 0 THEN 'divide_by_zero' 
            ELSE round($1 * 100 / $2::numeric, 2)::text || '%' END  -- explicit cast!
$func$;

    Mais importante, é o LANGUAGE sqlque permite a função inlining , (ao contrárioLANGUAGE plpgsql de ). Ver:

    Notavelmente , precisamos desse elenco explícito ::text. O operador de concatenação ||é resolvido para uma das várias funções internas, dependendo dos tipos de dados envolvidos, e nem todas são IMMUTABLE. Sem o cast explícito, o Postgres escolheria uma variante que é only STABLE, e isso discordaria da declaração da função e impediria o inlining da função. Detalhes sorrateiros! Relacionado:

    Corrigido um problema de lógica enquanto estava nisso: $2 = 0verifica a divisão por zero corretamente (ao contrário$2 > 0 de ). Agora, count(*)nunca pode ser negativo, mas como você coloca a lógica em uma função, ela fica isolada dessa pré-condição.

    Ou apenas coloque a expressão simples na consulta diretamente. Nenhuma chamada de função. Isso não é suscetível a nenhum dos problemas mencionados.

    • 8

  2. Parece que você atingiu algum tipo de barreira de otimização no PostgreSQL em que suas funções, em vez de serem avaliadas uma vez após o CTE, estão sendo avaliadas várias vezes!

    O que eu faria no seu caso é o seguinte:

    (as before)

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count,
            
-- the CASE statement below is the only difference to your 
-- original query - it does the same thing, but removes the
-- function calls.

-- All your function calls appear to do is call a TEXT 
-- representation of a percentage. The CASE statement
-- calculates the percentage "directly" and returns the
-- desired string.

  CASE
    WHEN t1.allcount = 0 THEN 'divide by zero' 

    -- (or maybe an actual 0 might be suitable? 
    -- It avoids the cast to ::TEXT below)

    ELSE ((t1.todo_count::REAL/t1.allcount::REAL) * 100)::TEXT
  AS todo_percentage
FROM t1;

    O uso dos casts to ::REALsignifica que você obterá "apenas" uma porcentagem com precisão de 6 casas decimais (consulte a documentação do PostgreSQL aqui ), mas raramente encontrei situações em que mais do que isso fosse necessário. FLOATsem precisão é, de fato, a DOUBLE PRECISION(15 lugares).

    Da documentação:

    O PostgreSQL também suporta as notações padrão SQL float e float(p) para especificar tipos numéricos inexatos. Aqui, p especifica a precisão mínima aceitável em dígitos binários. O PostgreSQL aceita float(1) a float(24) como selecionando o tipo real, enquanto float(25) a float(53) seleciona dupla precisão. Valores de p fora do intervalo permitido desenham um erro. float sem precisão especificada é considerado como precisão dupla.

    Existem outras maneiras e meios de fazer o que você precisa...

    Dê uma olhada aqui para algumas sugestões do site PostgreSQL se você não precisar de uma contagem exata e uma porcentagem exata . E então há (mais uma) resposta magistral de @Erwin Brandstetter aqui - ele fornece algumas maneiras de atingir seu objetivo e explica os prós e contras de cada um ...

    Alguns pontos de encerramento:

    • Suas funções: você parece ter muitos problemas para executar o que é (ou pelo menos deveria ser) as etapas finais de formatação muito antes de serem necessárias. Muitos argumentariam que o que você está fazendo em suas funções deve ser feito na camada cliente/apresentação. Eu me absteria de realizar esse tipo de manipulação pelo menos até a última etapa do SQL! Bancos de dados são para armazenar dados, não para apresentá-los!

      Outra solução (se você deseja insistir absolutamente em usar suas funções) pode ser envolver sua consulta em outra SELECTe fazer com que as funções operem nos resultados dessa consulta - isso deve remover a cerca de otimização (veja o exemplo aqui )! Um pouco complicado talvez, mas suas funções também são!

    • Após sua edição, o que você chama "some kind of "Schrödinger's cat" effecté, de fato, "optimisation fence"um problema com os CTEs há anos. Ele deveria ser corrigido pela WITH cte_name AS [ NOT ] [MATERIALIZED] (...diretiva (veja aqui ). A partir dessa resposta, sua consulta não está livre de efeitos colaterais!

      Agora, você dirá "mas, tudo o que ele faz é calcular uma porcentagem...", mas o otimizador não pode saber disso antecipadamente e "não se arrisca" e parece estar avaliando sua função várias vezes em vez de uma.

    • Por fim, apontei que você obviamente não nos forneceu todas as informações necessárias - há nomes de tabelas no PLAN que não aparecem em sua pergunta, o que implica para mim que você está consultando VIEWs, o que pode muito bem ser um fator de confusão.

      eu sugiro que você forneça um caso de teste em dbfiddle.uk (com tabelas base subjacentes), as visualizações que você cria nessas e em todas as suas consultas e funções - caso contrário, nenhuma ajuda adicional poderá ser fornecida.

    Sua analogia com "Schrödinger's cat"talvez seja particularmente adequada - não temos todas as informações - faça o postuladoVIEW s postulados existem ou não? Eles são VIEWs em VIEWs? Eles fazem VIEWalguma coisa? Se a VIEWé DROPacionado no meio de uma floresta e ninguém o ouve, será que foi realmente DROPacionado?

    Sem uma divulgação completa, podemos ser de pouca ajuda. No que me diz respeito, respondi à pergunta conforme solicitado e (de acordo com você) forneci soluções que funcionam. Concedido, pode não ser totalmente satisfatório, mas com o que temos, é o melhor que você vai conseguir!

    • 2

relate perguntas