Início / dba / Perguntas / 229413
Accepted
Brown Bear
Asked: 2019-02-12 06:23:32 +0800 CST

índice de floração postgres

  • 772

eu li o doc sobre postgres bloom , mas não consigo reproduzir os mesmos resultados, por favor me ajude a entender o que eu perdi. meu servidor é:

SHOW server_version;
        server_version         
-------------------------------
 10.6 (Debian 10.6-1.pgdg90+1)

dev=# show random_page_cost;
 random_page_cost 
------------------
 4

primeiro crie a tabela com o mesmo comando dos documentos:

dev=# CREATE TABLE tbloom AS                                               
   SELECT
     (random() * 1000000)::int as i1,
     (random() * 1000000)::int as i2,
     (random() * 1000000)::int as i3,
     (random() * 1000000)::int as i4,
     (random() * 1000000)::int as i5,
     (random() * 1000000)::int as i6
   FROM
  generate_series(1,10000000);

em seguida eu crio o índice btree

dev=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX

e obter o próximo plano:

dev=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..127195.10 rows=1 width=24) (actual time=258.963..260.900 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=255.446..255.446 rows=0 loops=3)
         Filter: ((i2 = 898732) AND (i5 = 123451))
         Rows Removed by Filter: 3333333
 Planning time: 0.412 ms
 Execution time: 260.939 ms

Tempo de execução: 260,939 ms

e agora solte o índice btree e crie bloom:

dev=# DROP INDEX btreeidx;
DROP INDEX
dev=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX

obter novo plano:

dev=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..127195.10 rows=1 width=24) (actual time=260.278..261.989 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=256.224..256.224 rows=0 loops=3)
         Filter: ((i2 = 898732) AND (i5 = 123451))
         Rows Removed by Filter: 3333333
 Planning time: 0.165 ms
 Execution time: 262.053 ms

Tempo de execução: 262,053 ms nos documentos

Bloom é melhor que btree

mas não no meu teste. Tentei várias opções com Comprimento mas não encontrei bom resultado.

postgresql index

3 respostas

  1. Best Answer

    Quando o bloom foi introduzido na versão 9.6, a consulta paralela havia acabado de ser introduzida e estava desativada por padrão. Bloom parecia ser melhor do que uma varredura sequencial não paralela no exemplo dado. Mas quando você pode fazer uma varredura seq paralela, parece melhor do que usar um índice de bloom. Na verdade, não é melhor, como pode ser verificado desativando a consulta paralela set max_parallel_workers_per_gather TO 0e observando as velocidades de execuções reais, mas o planejador acha que a varredura seq paralela será melhor. Parece que talvez a parte de estimativa de custos do bloom possa dar algum trabalho.

    O código de exemplo não foi atualizado para quando a consulta paralela foi ativada por padrão, na v10, portanto, não funciona mais conforme anunciado.

    Observe que seu exemplo nunca alcançou nenhum uso de índice, portanto, você não pode tirar conclusões sobre qual índice é melhor para esse cenário.

    • 5

  2. Atualizar

    Isso parece ser um bug com estimativas.

    Bloom está usando internamente genericcostestimates. Isso é derrotado se o seqscan for paralelo.

    Tentativa antiga de resposta

    Você nem está usando o índice que está criando (bloom ou btree)

    ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=256.224..256.224 rows=0 loops=3)

    Isso mostra que você está examinando a tabela inteira com trabalhadores paralelos. Sua indexação é totalmente irrelevante, nenhum índice é usado (daí a diferença de <1%). Você fez ANALYZEa tabela depois de criar o índice? Se sim, tente

    set enable_seqscan = 0;

    E execute o EXPLAIN ANALYZE para a consulta novamente. Eu esperaria que o índice de flores acelerasse as coisas, reduzindo massivamente o tamanho da tabela que você precisa visitar.

    • 3

  3. Resposta deixada nos comentários por a-cavalo-sem-nome

    Parece depender do valor de random_page_cost. No meu laptop onde tenho um SSD, isso é definido como 1e, nesse caso, o índice de flor é usado. Em um servidor random_page_costmaior que 1, a varredura seq é usada:

    https://explain.depesz.com/s/6Ynx

    Se você abaixar para 1 (pelo menos no Postgres 11) o índice de bloom é mais eficiente para o otimizador e assim ele escolhe a varredura de índice:

    captura de tela

    No entanto, definir esse valor como 1 só faz sentido em SSDs, não é uma boa ideia para discos rígidos giratórios.

    Analisar a tabela não muda as coisas (testado no Postgres 11). Parece que o custo de floração poderia fazer alguns ajustes.

    • 2

relate perguntas