Re: New criteria for autovacuum

On 03/04/2025 6:50 pm, Aleksander Alekseev wrote:
> Hi,
>
>> ... and it is claimed that autovacuum will never be triggered in order
>> to set hint bits, assuming we never modify the table again.
> Actually I waited a bit and got a better EXPLAIN:
>
> ```
> Index Only Scan using humidity_idx on humidity (cost=0.42..181.36
> rows=1970 width=4) (actual time=0.372..16.869 rows=2904.00 loops=1)
> Index Cond: ((ts >= '2022-01-01 00:00:00'::timestamp without time
> zone) AND (ts < '2022-05-02 00:00:00'::timestamp without time zone)
> AND (city = 'M
> oscow'::text))
> Heap Fetches: 0
> Index Searches: 1
> Buffers: shared hit=44
> Planning Time: 0.520 ms
> Execution Time: 17.980 ms
> (7 rows)
> ```
>
> This happens when CHECKPOINT starts:
>
> ```
> 2025-04-03 18:36:23.435 MSK [209952] LOG: checkpoint starting: time
> ```
>
> Interestingly it takes unusually long for my toy database:
>
> ```
> 2025-04-03 18:40:53.082 MSK [209952] LOG: checkpoint complete: wrote
> 3522 buffers (5.4%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0
> removed, 5 recycled; write=269.463 s, sync=0.029 s, total=269.647 s;
> sync files=32, longest=0.004 s, average=0.001 s; distance=68489 kB,
> estimate=68489 kB; lsn=0/F4F3870, redo lsn=0/F167DD0
> ```
>
> There is nothing in between these two lines.
>
> To my humble knowledge, CHECKOINT shouldn't set hint bits and should
> take that long. At this point I don't know what's going on.
>
> This is `master` branch, b82e7eddb023.
>
Checkpoint is not setting hint bits and not updating VM.
it just writes dirty pages to disk.

My patch includes simple test reproducing the problem. You can check
that VM is never updated in this case and explicit checkpoint doesn't
solve the issue.
Certainly manual vacuum will help. But user should somehow managed to
notice that index-only scan is not used or used and perform larger
number of heap fetches and understand that problem is with not updated
VM and vacuum is needed to fix it.

In your example both sessions are inserting data into the table. Vacuum
performed in one session doesn't take in account records created by
uncommitted transaction in another session.
So I do not think that plan in your case is improved because of
checkpoint. Most likely autovacuum was just triggered for this table and
updates VM.

What is needed to reproduce the problem?
1. Table with populated data
2. Presence of transaction with assigned XID which prevents vacuum from
marking pages of this table as all visible
3. Vacuum or autovacuum processed this table (to eliminate dead tuple
and reset number of inserted tuples since last vacuum).

After this 3 steps autovacuum will never be called for this table (at
least until forced vacuum caused by wraparound).
And IOS will not be used or be very inefficient fot this table.