Re: When Update balloons memory - Mailing list pgsql-bugs

On Tue, Dec 14, 2021 at 11:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'd be inclined to do so if we can find a suitable place to put it.
> But wouldn't a field in IndexInfo serve?  Letting the field default
> to "not optimizable" would cover most cases.

I'll come up with a patch for that soon.

> Yeah, you could make an argument that just not trying to optimize when
> there are index expressions would be fine for this --- and we may have
> to fix it that way in v14, because I'm not sure whether adding a field
> in IndexInfo would be safe ABI-wise.  But ISTM that the overhead of
> index_unchanged_by_update is a bit more than I care to pay per row
> even when it's only considering plain index columns.  I'm generally
> allergic to useless per-row computations, especially when they're
> being added by an alleged performance improvement.

I am tempted to broach the idea of always giving the hint in the case
of a non-HOT update, actually. But that's probably too weird to
countenance when you take a broader, API-level view of things. (So
I'll skip the explanation of why I think that might be reasonable from
the point of view of the nbtree code.)

> Another thing we ought to check into is the extent to which this
> is duplicative of the setup calculations for HOT updates --- I seem
> to recall that there's already roughly-similar logic somewhere else.

That's handled fairly directly, on the heapam side. At the top of
heap_update(), with some relcache infrastructure. Unlike
heap_update(), index_unchanged_by_update() cares about which specific
indexes have "logically modified" attributes. We already know for sure
that the update can't have been a HOT UPDATE when
index_unchanged_by_update() is reached, of course.

> And, not to be too picky, but does this cope with the case where
> an indexed column is changed by a BEFORE trigger, not by the
> query proper?

No. It's much better to err in the direction of giving the hint,
rather than not giving the hint. In order for us to make the category
of error that seems like it might actually be a problem (not giving
the hint when we should), the BEFORE trigger would have to "undo" an
explicit change to an updated column.

We also want to give the hint when a partial index is subject to lots
of non-HOT updates, when successive updates make the predicate flip
between matching and not matching. That was shown to be particularly
valuable (with a workload that has such an index). So the fact that we
don't handle predicates is intentional, even though the justification
for that relies on an implementation deficiency in HOT, that might be
fixed some day.

-- 
Peter Geoghegan