Re: Add n_tup_newpage_upd to pg_stat table views

On Fri, Jan 27, 2023 at 6:44 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> I don't think that'd make it particularly easy to figure out how often
> out-of-space causes non-HOT updates to go out of page, and how often it causes
> potential HOT updates to go out of page. If you just have a single index,
> it's not too hard, but after that seems decidedly nontrival.
>
> But I might just be missing what you're suggesting.

It would be useless for that, of course. But it would be a good proxy
for what specific indexes force non-hot updates due to HOT safety
issues. This would work independently of the issue of what's going on
in the heap. That matters too, of course, but in practice the main
problem is likely the specific combination of indexes and updates.
(Maybe it would just be an issue with heap fill factor, at times, but
even then you'd still want to rule out basic HOT safety issues first.)

If you see one particular index that gets a far larger number of
non-hot updates that are reported as "logical changes to the indexed
columns", then dropping that index has the potential to make the HOT
update situation far better.

--
Peter Geoghegan