Início / dba / Perguntas / 328732
Accepted
EM0
Asked: 2023-06-28 22:59:37 +0800 CST

A consulta PostgreSQL usando o índice BRIN é lenta até REINDEX/VACUUM

  • 772

PostgreSQL 15.2. Eu tenho uma tabela particionada assim:

create table event
(
    dataset_id integer not null,
    event_id integer not null,
    second_offset integer not null,
    -- other columns
) partition by list (dataset_id);

As partições desta tabela têm um PK ativado event_ide um índice BRIN ativado second_offset:

create index event_DATASET_ID_ix_second_offset on event_DATASET_ID using brin (second_offset);

Alguns deles têm centenas de milhões de linhas. second_offseté a hora do evento e a linha é inserida logo, então segue de perto (mas não perfeitamente) a ordem física das linhas. As linhas nunca são atualizadas ou excluídas nessas tabelas, apenas inseridas e lidas.

Eu executo consultas como esta (simplificadas):

set enable_seqscan = off; 

select *
from event
where dataset_id = 365
    and second_offset <= timestamp_to_second_offset('2023-05-10') -- function that returns int
    and second_offset >= timestamp_to_second_offset('2023-05-09')

Eles usam o índice, mas ainda são lentos. EXPLICAR ANALISAR mostra:

Bitmap Heap Scan on event_365 event  (cost=453.13..2689210.37 rows=1322 width=54) (actual time=40651.983..40651.984 rows=0 loops=1)
  Recheck Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
  Rows Removed by Index Recheck: 238676609
  Filter: (dataset_id = 365)
  Heap Blocks: lossy=1762985
  ->  Bitmap Index Scan on event_365_ix_second_offset  (cost=0.00..452.80 rows=52893390 width=0) (actual time=73.633..73.634 rows=17629850 loops=1)
        Index Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
Planning Time: 0.673 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.672 ms, Inlining 4.802 ms, Optimization 19.712 ms, Emission 9.971 ms, Total 36.157 ms
Execution Time: 40653.748 ms

... até executar reindex index behavior.event_365_ix_second_offsetou, alternativamente, vacuum behavior.event_366(tentei isso em duas partições diferentes). Então a consulta se torna muito rápida! EXPLAIN ANALYZE então mostra:

Bitmap Heap Scan on event_365 event  (cost=596.29..5940945.52 rows=5967 width=54) (actual time=5.012..5.013 rows=0 loops=1)
  Recheck Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
  Filter: (dataset_id = 365)
  ->  Bitmap Index Scan on event_365_ix_second_offset  (cost=0.00..594.80 rows=238696656 width=0) (actual time=5.008..5.008 rows=0 loops=1)
        Index Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
Planning Time: 0.771 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.642 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 1.642 ms
Execution Time: 6.940 ms

Portanto, minha pergunta é como garantir que as consultas sejam sempre executadas rapidamente. Devo adicionar um cron job que reindexe todos eles todas as noites? Parece um pouco hacky e algo como PG deveria fazer automaticamente, não é?

pg_stat_user_tablesmostra que o autovacuum nunca é executado na maioria dessas partições - não sei por quê. Portanto, não sei se devo tentar forçar o autovacuum a funcionar de alguma forma.

relido nome do esquema nome real 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 n_ins_since_vacuum last_vacuum last_autovacuum last_analyze last_autoanalyze contagem_vácuo autovacuum_count analisar_contar autoanalyze_count
8224073 comportamento evento_365 26 4773933120 18135903 9046114024 238696656 0 0 0 238696656 0 238696656 238696656 0 0 0 0
postgresql

2 respostas

  1. Best Answer

    Não deve ser necessário reindexar índices BRIN, a menos que exclusões/atualizações ocorram na tabela. Apenas certifique-se de que seja aspirado e analisado regularmente. Observe que vacuumnão está apenas removendo tuplas mortas, mas também atualiza o mapa de visibilidade e congela as tuplas. Se você preferir o autovacuuming, para tabelas somente de inserção, ele pode ser controlado por autovacuum_vacuum_insert_thresholde autovacuum_vacuum_insert_scale_factorparâmetros, por exemplo

    alter table event set (autovacuum_vacuum_insert_threshold = 20000 , autovacuum_vacuum_insert_scale_factor =0 );

    Tabela de eventos de vácuo automático a cada 20 mil inserções. Você também pode tentar configurá-lo em uma partição individual.

    • 1

  2. Você provavelmente criou o índice antes de inserir algumas das linhas, portanto, havia muitos intervalos de páginas não resumidos (consulte a documentação ). Executar VACUUMou chamar brin_summarize_new_values()resume essas páginas e o índice se torna eficiente.

    • 1

relate perguntas