PostgreSQL query using BRIN index is slow until REINDEX/VACUUM

Question

PostgreSQL 15.2. I have a partitioned table like this:

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

Partitions of this table have a PK on event_id and a BRIN index on second_offset:

create index event_DATASET_ID_ix_second_offset on event_DATASET_ID using brin (second_offset);

Some of these have hundreds of millions of rows. second_offset is the time of the event and the row is inserted soon, so it closely (but not perfectly) follows the physical order of rows. Rows are never updated or deleted in these tables, only inserted and read.

I run queries like this (simplified):

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

They use the index, but are still slow. EXPLAIN ANALYZE shows:

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

... until I run reindex index behavior.event_365_ix_second_offset or, alternatively, vacuum behavior.event_366 (I tried this on two different partitions). Then the query becomes really fast! EXPLAIN ANALYZE then shows:

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

So my question is how to ensure the queries always run fast. Should I add a cron job that reindexes all of them nightly? Seems a bit hacky and something like PG should do automatically, doesn't it?

pg_stat_user_tables shows that autovacuum never runs on most of these partitions - I don'tk now why. So I don't know if I should instead try to force autovacuum to run somehow.

relid	schemaname	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	n_ins_since_vacuum	last_vacuum	last_autovacuum	last_analyze	last_autoanalyze	vacuum_count	autovacuum_count	analyze_count	autoanalyze_count
8224073	behavior	event_365	26	4773933120	18135903	9046114024	238696656	0	0	0	238696656	0	238696656	238696656					0	0	0	0

Answer 1

It should not be needed to re-index BRIN indexes unless deletes/updates happen on the table. Just make sure it's vacuumed and analyzed on regular basis. Note , vacuum is not only removing dead tuples, but also updates visibility map and freezes tuples. If you prefer autovacuuming, for insert-only tables it can be controlled by autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor parameters, e.g.

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

Autovacuum event table every 20k inserts . You may try setting it on individual partition instead as well.

Answer 2

You probably created the index before inserting some of the rows, so there were a lot of unsummarized page ranges (see the documentation). Running VACUUM or calling brin_summarize_new_values() summarizes these pages, and the index becomes efficient.