Início / dba / Perguntas / 301847
Accepted
Tom
Asked: 2021-10-29 12:46:22 +0800 CST

Alto custo na consulta do PostgreSQL

  • 772

Tenho uma tabela que possui mais de 10.000.000 registros, e estou criando uma consulta que retorna cerca de 4436 registros.

Acontece que me dá a impressão de que o custo da consulta para chegar ao último registro é muito alto.

Index Scan using idx_name on task  (cost=0.28..142102.57 rows=3470 width=34) (actual time=14.690..22.894 rows=4436 loops=1)
"  Index Cond: ((situation = ANY ('{0,1,2,3,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[])) AND (deadline < CURRENT_TIMESTAMP))"
Planning Time: 1.335 ms
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.654 ms, Inlining 0.000 ms, Optimization 1.214 ms, Emission 13.163 ms, Total 16.030 ms
Execution Time: 24.758 ms

Esse nível de custo é aceitável ou esse índice precisa ser melhorado?

Índice:

CREATE INDEX idx_name ON task (situation, deadline, approved)
WHERE
deadline IS NOT NULL AND
situation <> ALL ('{4,5}'::integer[]) AND
approved = 'N';

Minha consulta:

SELECT
        task.deadline,
        task.id
    FROM
        task
    WHERE
        task.deadline IS NOT NULL
        AND task.situation IN ('0', '1', '2', '3', '6' ,'7' ,'8','9','10','11','12','13','14','15','16','17','18','19','20')
        AND task.situation NOT IN ('4', '5')
        AND task.deadline < CURRENT_TIMESTAMP
        AND task.approved = 'N';
postgresql postgresql-performance

3 respostas

  1. Conforme sugerido em um comentário, você não deve observar o custo da consulta, mas sim o tempo de execução real (ou você não deve se incomodar com nada disso se o tempo de execução for aceitável). O custo estimado do plano não é uma indicação de nada, exceto a quantidade relativa de vários recursos que o Postgres estima que pode precisar gastar para executar esse plano específico em comparação com outros planos possíveis .

    Observar o valor de custo absoluto não diz absolutamente nada; compará-lo com os custos de outros planos informa qual deles o Postgres considera mais eficiente, com base nas informações que o otimizador possui.

    Veja também este Q&A .

    Olhando para a boca do cavalo, pode-se ver isso:

    /*-------------------------------------------------------------------------
 *
 * costsize.c
 *    Routines to compute (and set) relation sizes and path costs
 *
 * Path costs are measured in arbitrary units established by these basic
 * parameters:
 *
 *  seq_page_cost       Cost of a sequential page fetch
 *  random_page_cost    Cost of a non-sequential page fetch
 *  cpu_tuple_cost      Cost of typical CPU time to process a tuple
 *  cpu_index_tuple_cost  Cost of typical CPU time to process an index tuple
 *  cpu_operator_cost   Cost of CPU time to execute an operator or function
 *  parallel_tuple_cost Cost of CPU time to pass a tuple from worker to leader backend
 *  parallel_setup_cost Cost of setting up shared memory for parallelism
...

    e mais adiante nesse caminho:

    # - Planner Cost Constants -

#seq_page_cost = 1.0            # measured on an arbitrary scale
#random_page_cost = 4.0         # same scale as above
#cpu_tuple_cost = 0.01          # same scale as above
#cpu_index_tuple_cost = 0.005       # same scale as above
#cpu_operator_cost = 0.0025     # same scale as above

    Você notará que eles enfatizam "unidades arbitrárias" em uma "escala arbitrária"; tudo o que eles querem estabelecer é que ler N páginas aleatoriamente é quatro vezes mais intensivo em recursos ("caro") do que ler tantas páginas sequencialmente, ou que avaliar um predicado em relação a uma entrada de índice é metade do custo de fazer isso em uma tabela fileira. Quando o custo de toda a árvore do plano é somado, obtém-se um valor que só pode ser comparado ao custo de outra árvore; só pode ser mais alto ou mais baixo , não alto ou baixo.

    HT para jjanes por mencionar JIT em um comentário. O custo estimado da consulta no seu caso acaba excedendo o limite do gatilho JIT, que é padrão em 100.000 e, como jjanes apontou astutamente, "2/3 do tempo [de sua consulta] parece ser gasto em JIT", o que provavelmente é contraproducente. Você pode querer avaliar se ter o JIT ativado em seu ambiente é útil.

    • 6
  2. Best Answer

    O índice é bom, a consulta é rápida.

    Mas o índice pode ser melhor e a consulta mais rápida. A coluna de índice approvedé apenas frete morto com a condição approved = 'N'. Remova.

    CREATE INDEX idx_name ON task (situation, deadline)
WHERE  deadline IS NOT NULL
AND    situation <> ALL ('{4,5}'::integer[])
AND    approved = 'N';

    Importa para o tamanho do índice, mesmo que approvedseja varchar(1)(e provavelmente deveria ser boolean). Como deadlineé um tipo (alinhado) timestamp( with time zone), a coluna de índice adicionada approveddesperdiça pelo menos 8 bytes por tupla de índice - faz com que ela cresça em 1/3.

    Melhor ainda, se o exemplo com SELECT deadline, id ...for qualquer indicação, vale a pena anexar idcomo coluna "incluída" para permitir varreduras somente de índice :

    CREATE INDEX idx_name ON task (situation, deadline) INCLUDE (id)
WHERE  deadline IS NOT NULL
AND    situation <> ALL ('{4,5}'::integer[])
AND    approved = 'N';

    Requer Postgres 11 ou posterior. Traz o tamanho de volta ao que era antes.

    Ver:

    • 4

  3. Ambas as outras respostas são muito boas, deixe-me adicionar um detalhe:

    Ambas as suas condições não são comparações de igualdade simples, portanto, a segunda coluna no índice será usada apenas para filtrar linhas (antes que a tabela seja acessada). Portanto, a ordem das colunas de índice influenciará quantas entradas de índice devem ser lidas para encontrar a resposta.

    Para encontrar o melhor índice para a consulta, primeiro tente com um índice em (situation, deadline), depois elimine esse índice e tente com um em (deadline, situation), e veja qual deles toca menos blocos com EXPLAIN (ANALYZE, BUFFERS).

    • 3

relate perguntas