Re: Add n_tup_newpage_upd to pg_stat table views

On Fri, Jan 27, 2023 at 3:55 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> 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.

It would be hard to apply that kind of information, I suspect. Maybe
it's still worth having, though.

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

ISTM that it would make more sense to do that at the index level
instead. It wouldn't be all that hard to teach ExecInsertIndexTuples()
to remember whether each index received the indexUnchanged hint used
by bottom-up deletion, which is approximately the same thing, but
works at the index level.

This is obviously more useful, because you have index-granularity
information that can guide users in how to index to maximize the
number of HOT updates. And, even if changing things around didn't lead
to the hoped-for improvement in the rate of HOT updates, it would at
least still allow the indexes on the table to use bottom-up deletion
more often, on average.

Admittedly this has some problems. The index_unchanged_by_update()
logic probably isn't as sophisticated as it ought to be because it's
driven by the statement-level extraUpdatedCols bitmap set, and not a
per-tuple test, like the HOT safety test in heap_update() is.
But...that should probably be fixed anyway.

> I think this might cause some trouble for existing monitoring setups after an
> upgrade. Suddenly the number of updates will appear way lower than
> before... And if we end up eventually distinguishing between different reasons
> for out-of-page updates, or hot/non-hot out-of-page that'll happen again.

Uh...no it won't? The new counter is totally independent of the existing
HOT counter, and the transactional all-updates counter. It's just that
there is an enum that encodes which of the two non-transactional "sub
counters" to use (either for HOT updates or new-page-migration
updates).

> I wish we'd included HOT updates in the total number of updates, and just kept
> HOT updates a separate counter that'd always be less than updates in total.

Uh...we did in fact do it that way to begin with?

> From that angle: Perhaps it'd be better to have counter for how many times a
> page is found to be full during an update?

Didn't Corey propose a patch to add just that? Do you mean something
more specific, like a tracker for when an UPDATE leaves a page full,
without needing to go to a new page itself?

If so, then that does require defining what that really means, because
it isn't trivial. Do you assume that all updates have a successor
version that is equal in size to that of the UPDATE that gets counted
by this hypothetical other counter of yours?

--
Peter Geoghegan