Re: Expanding HOT updates for expression and partial indexes

Hey Peter,

I'm adding you for one question about the indexUnchanged hint,
which this series could make more accurate. I'd rather ask before
changing it.

Since 0002 the executor knows, per row, which indexed attributes an
UPDATE changed (modified_idx_attrs). It compares values after BEFORE
ROW triggers and after EvalPlanQual. index_unchanged_by_update()
(9dc718bdf2b) still works from ExecGetUpdatedCols(), the columns
named in SET, and caches its answer per statement in
ii_CheckedUnchanged. The two can disagree either way:

- A BEFORE ROW trigger that changes an indexed column the SET clause
didn't name, as tsvector_update_trigger() does, leaves it out of
updatedCols. The hint then says "unchanged" for an index whose key
changed.

- An UPDATE that writes back the values a row already has (ORMs that
send every column; logical replication apply, which marks every
column the publisher sent) has those columns in updatedCols. The
hint then says "changed" for every index although nothing changed.
When such an update can't be HOT because the page is full, that's
the version churn bottom-up deletion is for.

The docs allow both false positives and false negatives, and you've
called it "just a hint used to trigger bottom-up index deletion" [1].
So neither is a bug. The question is whether you'd want the better
answer now that the executor already has the set: the hint for index
i would be "no key column of i is in modified_idx_attrs", for the row
being inserted. INCLUDE and predicate columns stay out as today, and
expression keys stay conservative, as Tepid's ExecSetIndexUnchanged
does.

What makes me ask first: the hint would become per row, so two rows
of one statement could get different answers. _bt_findinsertloc()
says the hint "should not significantly affect our behavior over time
unless practically all inserts on to the leaf page get the hint",
which I read as tolerant of that. If you see a reason it should stay
per statement, I'll leave it alone. If not, I'd add it to 0002 in
v45. The test would repeat an UPDATE that writes back an indexed
column's value on full pages, so it can't be HOT, and compare that
index's leaf page count (pageinspect's bt_page_stats) with and without
the change.

This also fits the split Jeff described earlier in this thread: "It's
wrong for the executor to say 'do a HOT update' but it's OK for the
executor to say 'this is the set of indexes that might have a new key
after the update'" [2]. The hint would come from that same set.

thoughts?

-greg

[1] https://postgr.es/m/CAH2-Wz=rugC=iZTDCKDo2gv-4u7Mkih=akqq07NVz-nai6=vKg@mail.gmail.com
[2] https://postgr.es/m/f59348afe291ef62b8596fc1615104c51c620bec.camel@j-davis.com