Re: New criteria for autovacuum

> I think this enables us to update the VM
> during on-access pruning. This is something I plan to work on in 19.
> It seems like it would alleviate situations like this.

IMO, index-only scans hitting the heap have always caught users off guard,
especially because scan performance fluctuates between good and bad.
Reducing this performance volatility is a great improvement.

> > As far as extra metrics go for the scenario in which and index only scan must
> > visit a table, pg_stat_all_indexes and pg_stat_all_tables do have a
> > idx_tup_fetch
> > counter which increases anytime an index scan visits the table, i.e.
> > index-only scan
> > with heap fetches or a regular index scan. I think having a counter
> > specifically for
> > heap fetches due to index-only scans could be valuable.
>
> What do you imagine using the heap fetches during index-only scans counter for?

A user can only determine that they are using index-only scans through EXPLAIN,
which limits visibility. It would be beneficial to provide additional
metrics to
support autovacuum/vacuum tuning for workloads that rely on index-only scans
and require them to be as efficient as possible.

Currently, pg_stat_all_tables/indexes displays the number of index scans,
but it would also be helpful to show the number of index-only scans
specifically,
along with "heap fetches". This would help in identifying the "heap
fetches" issue
and confirming whether vacuum tuning had any impact to reduce "heap fetches".

The existing indx_tup_fetch combines heap fetches from normal and
index-only scans,
so it could be useful but not perfect for this task.

--
Sami Imseih
Amazon Web Services (AWS)