Re: Add n_tup_newpage_upd to pg_stat table views

On Fri, Jan 27, 2023 at 6:55 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> On 2023-01-27 18:23:39 -0500, Corey Huinker wrote:
> > This patch adds the n_tup_newpage_upd to all the table stat views.
> >
> > Just as we currently track HOT updates, it should be beneficial to track
> > updates where the new tuple cannot fit on the existing page and must go
> to
> > a different one.
>
> I like that idea.
>
>
> I wonder if it's quite detailed enough - we can be forced to do out-of-page
> updates because we actually are out of space, or because we reach the max
> number of line pointers we allow in a page. HOT pruning can't remove dead
> line
> pointers, so that doesn't necessarily help.
>

I must be missing something, I only see the check for running out of space,
not the check for exhausting line pointers. I agree dividing them would be
interesting.

> Similarly, it's a bit sad that we can't distinguish between the number of
> potential-HOT out-of-page updates and the other out-of-page updates. But
> that'd mean even more counters.
>

I wondered that too, but the combinations of "would have been HOT but not
no space" and "key update suggested not-HOT but it was id=id so today's
your lucky HOT" combinations started to get away from me.

I wondered if there was interest in knowing if the tuple had to get
TOASTed, and further wondered if we would be interested in the number of
updates that had to wait on a lock. Again, more counters.