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