Início / dba / Perguntas / 340723
Accepted
Eduard Sukharev
Asked: 2024-07-04 22:18:05 +0800 CST

PostgreSQL usa varredura de índice lenta em vez de varredura de heap de bitmap + varredura de índice com ORDER BY e LIMIT específico

  • 772

Dada a seguinte tabela:

CREATE TABLE chat_message (
    id bigint DEFAULT nextval('public.chat_message_id_seq'::regclass) NOT NULL,
    "user" integer,
    type smallint,
    text text
);
ALTER TABLE ONLY chat_message ADD CONSTRAINT pk_chat_message PRIMARY KEY (id);
CREATE INDEX idx_chat_message_user_type ON chat_message USING btree ("user", type);
CREATE INDEX k_chat_message_user ON chat_message USING btree ("user");

onde type é 1ou NULL, então a consulta:

EXPLAIN ANALYZE
SELECT *
FROM "chat_message" AS t
WHERE true
  AND "type" = 1
  AND "user" = 1234567
ORDER BY "user", "type", "id" ASC
LIMIT 10 OFFSET 0;

fornece a seguinte saída:

                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=53644.94..53644.97 rows=10 width=127) (actual time=4.817..4.818 rows=6 loops=1)
   ->  Sort  (cost=53644.94..53681.60 rows=14663 width=127) (actual time=4.816..4.816 rows=6 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 26kB
         ->  Bitmap Heap Scan on chat_message t  (cost=362.86..53328.08 rows=14663 width=127) (actual time=1.975..2.181 rows=6 loops=1)
               Recheck Cond: (("user" = 1234567) AND (type = 1::smallint))
               Heap Blocks: exact=3
               ->  Bitmap Index Scan on idx_chat_message_user_type  (cost=0.00..359.19 rows=14663 width=0) (actual time=1.822..1.822 rows=6 loops=1)
                     Index Cond: (("user" = 1234567) AND (type = 1::smallint))
 Planning time: 0.348 ms
 Execution time: 5.028 ms

Mas uma vez que o valor LIMIT é reduzido abaixo de algum valor (para 9 na minha máquina local), o plano de consulta muda para isto:

                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..50193.33 rows=9 width=127) (actual time=23119.188..46005.965 rows=6 loops=1)
   ->  Index Scan using pk_chat_message on chat_message t  (cost=0.56..81775168.50 rows=14663 width=127) (actual time=23119.187..46005.962 rows=6 loops=1)
         Filter: ((type = 1::smallint) AND ("user" = 1234567))
         Rows Removed by Filter: 49452956
 Planning time: 14.840 ms
 Execution time: 46006.683 ms

o que é muito lento.

Há uma enorme distorção de dados para esse usuário exato: são 50.000 rows WHERE type is NULLe apenas 6 WHERE type = 1. Além disso, solicitando o mesmo LIMIT 9, mas WHERE type is NULLtem exatamente o mesmo plano de consulta, mas funciona rápido:

                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=153793.13..153793.15 rows=9 width=127) (actual time=886.897..886.898 rows=9 loops=1)
   ->  Sort  (cost=153793.13..153909.07 rows=46374 width=127) (actual time=886.894..886.894 rows=9 loops=1)
         Sort Key: gs_type, id
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Bitmap Heap Scan on chat_message t  (cost=1143.90..152826.25 rows=46374 width=127) (actual time=12.561..878.947 rows=49934 loops=1)
               Recheck Cond: (("user" = 1234567) AND (type IS NULL))
               Heap Blocks: exact=10903
               ->  Bitmap Index Scan on idx_chat_message_user_type  (cost=0.00..1132.31 rows=46374 width=0) (actual time=9.942..9.942 rows=49934 loops=1)
                     Index Cond: (("user" = 1234567) AND (type IS NULL))
 Planning time: 0.308 ms
 Execution time: 887.027 ms

No servidor de produção, exatamente os mesmos dados carregados em um servidor com especificações diferentes do meu laptop (mais memória RAM, enorme shared_buffers, max_memcarga de trabalho constante de outras tabelas diferentes) se comportam de maneira semelhante, apenas o valor do limite é diferente (é lento Index Scanaté 75, e depois rápido Bitmap Heap Scan+ Bitmap Index Scande 76 e mais).

Algumas informações adicionais:

SELECT * FROM pg_stat_user_tables WHERE relname = 'chat_message';

relname     |seq_scan   |seq_tup_read   |idx_scan   |idx_tup_fetch  |n_tup_ins  |n_tup_upd  |n_tup_del  |n_tup_hot_upd  |n_live_tup |n_dead_tup |n_mod_since_analyze|last_vacuum|last_autovacuum|last_analyze   |last_autoanalyze   |vacuum_count   |autovacuum_count   |analyze_count  |autoanalyze_count  |
chat_message|0          |0              |11         |197,652,914    |0          |0          |0          |0              |0          |0          |0                  |           |               |               |                   |0              |0                  |0              |0                  |

SELECT * FROM pg_stats where tablename = 'chat_message';

schemaname  |tablename      |attname       |inherited|null_frac|avg_width|n_distinct|most_common_vals
public      |chat_message   |id            |false    |0        |8        |-1        |
public      |chat_message   |user          |false    |0        |4        |30145     |{redacted}
public      |chat_message   |text          |false    |0        |38       |45553     |{redacted}
public      |chat_message   |type          |false    |0.7656   |2        |1         |{1}

Minhas perguntas são:

  • Por que o mesmo Index Scanfica muito lento quando se trata dessas poucas linhas?
  • Por que Index Scansempre usa pk_chat_messageíndice, mesmo que haja mais adequado idx_chat_message_user_type, mesmo que ORDER BYa cláusula tenha todos os campos da WHEREcláusula ( ordem por influencia o uso do índice )?
  • Por que LIMIT Nafeta o plano de consulta porque ele Index Scanprefere Bitmap Index + Heap Scan?
  • O que pode ser feito para que esta consulta tenha um desempenho decente (menos de 1s) para esta user + typee outras?
postgresql

2 respostas

  1. Best Answer

    O PostgreSQL tem duas opções para processar a consulta:

    • ele pode usar um índice para a WHEREcláusula, classificar e retornar os primeiros resultados (esse é o seu plano rápido)

    • ele pode usar um índice para a ORDER BYcláusula e descartar linhas que não correspondam à WHEREcondição até encontrar linhas de resultados suficientes (esse é o seu plano lento)

    A decisão de qual plano é melhor é difícil, e o PostgreSQL às vezes pode errar. No seu caso lento, ele precisa varrer 4.9452.957 linhas até encontrar uma que atenda à WHEREcondição, mesmo que haja cerca de 14.663 (na verdade, 49.934) linhas que atendam à WHEREcondição. O problema é que o PostgreSQL não possui estatísticas que possam dizer que todas as linhas correspondentes têm um valor grande id, então ele precisa verificar muitas linhas até obter uma resposta.

    Naturalmente, a segunda estratégia (no seu caso, lenta) se torna mais atraente se você precisar apenas de algumas linhas de resultados, o que explica que o otimizador muda para esse plano quando você reduz o número de linhas no arquivo LIMIT.

    Observe que uma "Varredura de índice" é processada de maneira bem diferente de uma "Varredura de índice de bitmap". O primeiro retornará os resultados na ordem do índice, enquanto o último retornará as linhas na ordem da tabela, mas terá melhor desempenho se houver muitas linhas de resultados.

    Você tem duas maneiras de melhorar a situação:

    1. crie um índice que suporte a ORDER BYe a WHEREcondição:

      CREATE INDEX ON chat_message ("user", type, id);

    2. use um truque grosseiro para evitar que o PostgreSQL use o índice de chave primária:

      ... ORDER BY "user", type, id + 0
    • 2

  2. A solução mais rápida para dados distorcidos seria partial index. No seu caso, um índice como o abaixo será rápido e super compacto. Você pode ajustar e testar o índice alterando index columnou filtering column.

    crie o índice ix_partial_type em chat_message(<user_id/id>) onde type=1;

    • 1

relate perguntas