> Interestingly it takes unusually long for my toy database: > 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. >
>From what I can tell in your example, you ran the manual vacuum ( session 1) while you had an open transaction (session 2), so vacuum could not remove the dead tuples or update the visibility map. Once you committed session 2, autovacuum came in and did its job after the autovacuum_naptime passed (default 1 minute). Checkpoint does not update the visibility map, only vacuum does. IMO, I don't think we need this patch for vacuum, as simply making sure autovacuum runs more frequently on the table that is accessed via index-only scans often is a way to deal with this already, i.e lowering autovacuum_vacuum_scale_factor. Maybe others have a different opinion? 13 also introduced autovacuum_vacuum_scale_factor to deal with append only tables that only saw their first vacuum for wraparound prevention ( 200 million transactions by default ) and that made index-only scans slow because of an outdated visibility map as well as the wraparound vacuum being more disruptive. 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. -- Sami Imseih Amazon Web Services (AWS)
