0

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
7
  • Regarding vacuuming - you might want to set autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor parameters for tables that are never updated/deleted.
    – a1ex07
    Jun 28, 2023 at 16:01
  • What are all the columns from pg_stat_user_tables for one of these tables which is never vacuumed?
    – jjanes
    Jun 29, 2023 at 12:36
  • In my hands it works as expected. Maybe you just aren't waiting long enough for autovacuum to get the job done. Are all the autovac workers currently in use?
    – jjanes
    Jun 29, 2023 at 12:44
  • @jjanes I edited the whole pg_stat_user_tables row into the question. Some of these tables have existed for months - that should be enough time, I hope. :) How do I tell whether all autovac workers are in use?
    – EM0
    Jun 29, 2023 at 13:11
  • With those stats, it should definitely be autovacuuming the tables with the default settings. Maybe something is taking strong locks on the table frequently, which prevent the autovacuum from getting the job done. You can check what is currently getting vacuumed with pg_stat_progress_vacuum. And look in the log file to see if vacuums are getting cancelled or failing for other errors (or if the system is restarting frequently, which will also prevent autovacuum from finishing)
    – jjanes
    Jun 30, 2023 at 0:22

2 Answers 2

2

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.

9
  • Interesting! But why is autovacuum not running for most of my partitions already? Those settings at their default values of 1000 and 0.2 respectively. So when an initially-empty table partition gets its first 1000 rows autovacuum should run on it, shouldn't it? Then at 2200 (= 1000 + 0.2 * 1000) rows it should run again.
    – EM0
    Jun 29, 2023 at 8:52
  • postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM says "The table is also vacuumed if the number of tuples inserted since the last vacuum has exceeded the defined insert threshold" - so it sounds like inserts alone should trigger autovacuum, even without updates or deletes.
    – EM0
    Jun 29, 2023 at 9:28
  • I'd double check if these values are actually set and not overwritten ( both on instance level and table level) ... Also, for large tables , autovacuum_vacuum_insert_scale_factor =0 works much better than any percentage.
    – a1ex07
    Jun 29, 2023 at 16:44
  • Checked with select pg_options_to_table(reloptions) from pg_class where relname = 'event_365' and show autovacuum_vacuum_insert_scale_factor - they are not overridden. How is 0 "better"? Do you have any idea why autovacuum is not running now - seems like it should even with the current settings.
    – EM0
    Jun 30, 2023 at 13:30
  • 0 is better (for large tables) because it lets you specify number of dead tuples(or inserts) rather than percentage. If table has billion of row , default 0.2 will trigger autovacuuming after 200 000 000 rows inserted (practically, most likely anti-wraparound vacuuming would get launched instead of regular vacuuming in such case).
    – a1ex07
    Jul 4, 2023 at 20:49
1

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.

4
  • Thanks! Yes, the index was created before any rows were inserted. So, as usual the issue is noted in the PG docs. "if the index's autosummarize parameter is enabled, which it isn't by default, whenever autovacuum runs in that database, summarization will occur for all unsummarized page ranges that have been filled, regardless of whether the table itself is processed by autovacuum" Isn't the solution then to set autosummarize=on? But why is it not on by default? Seems like quite a gotcha!
    – EM0
    Jun 29, 2023 at 8:47
  • This is to balance the performance impact of data modifications against query speed. If you don't care about DML, but want your queries as fast as possible, you use autosummarize. Jun 29, 2023 at 11:28
  • I care about the performance of inserts more. Does that mean I should not turn on autosummarize and instead run brin_summarize_new_values in a cron job nightly? (Or autovacuum, if I can get that to work, per the other answer.)
    – EM0
    Jun 29, 2023 at 13:05
  • 1
    Yes, that sounds like a good idea. Jun 29, 2023 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.