主页 / dba / 问题 / 328732
Accepted
EM0
Asked: 2023-06-28 22:59:37 +0800 CST

使用 BRIN 索引的 PostgreSQL 查询很慢，直到 REINDEX/VACUUM

  • 772

PostgreSQL 15.2。我有一个这样的分区表：

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);

该表的分区有 PKevent_id和 BRIN 索引second_offset：

create index event_DATASET_ID_ix_second_offset on event_DATASET_ID using brin (second_offset);

其中一些有数亿行。second_offset是事件发生的时间，并且行很快被插入，因此它紧密（但不完美）遵循行的物理顺序。这些表中的行永远不会更新或删除，只会插入和读取。

我运行这样的查询（简化）：

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')

他们使用索引，但仍然很慢。解释分析显示：

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

...直到我运行reindex index behavior.event_365_ix_second_offset，或者，vacuum behavior.event_366（我在两个不同的分区上尝试过）。然后查询变得非常快！EXPLAIN ANALYZE 然后显示：

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

所以我的问题是如何确保查询始终快速运行。我应该添加一个 cron 作业来每晚重新索引它们吗？看起来有点老套，像 PG 这样的事情应该自动完成，不是吗？

pg_stat_user_tables显示 autovacuum 从未在大多数分区上运行 - 我现在不知道为什么。所以我不知道是否应该尝试强制 autovacuum 以某种方式运行。

雷利德 模式名 别名 序列扫描 seq_tup_read idx_扫描 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 最后的真空 最后自动真空 最后分析 最后自动分析 真空计数 自动真空计数 分析计数 自动分析计数
8224073 行为 事件_365 26 4773933120 18135903 9046114024 238696656 0 0 0 238696656 0 238696656 238696656 0 0 0 0
postgresql

2 个回答

  1. Best Answer

    除非表上发生删除/更新，否则不需要重新索引 BRIN 索引。只要确保定期对其进行吸尘和分析即可。注意，vacuum不仅删除死元组，还更新可见性图并冻结元组。如果您更喜欢自动清理，对于仅插入表，可以通过autovacuum_vacuum_insert_threshold和autovacuum_vacuum_insert_scale_factor参数控制，例如

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

    Autovacuum 事件表每 20k 插入一次。您也可以尝试将其设置在单独的分区上。

    • 1

  2. 您可能在插入某些行之前创建了索引，因此存在大量未汇总的页面范围（请参阅文档）。运行VACUUM或调用brin_summarize_new_values()会对这些页面进行汇总，索引变得高效。

    • 1

相关问题