Re: Expanding HOT updates for expression and partial indexes

Hello,

Attached is v46, nine patches on master (952bab8b74d), so after the
attribute versioning commits. It's v45 rebased, plus the indexUnchanged
change I asked Peter about [1]. Nobody objected, so here it is as its
own patch, the new 0003, where it's easy to look at and easy to drop.
0004 to 0009 are v45's 0003 to 0008, renumbered; apart from the call
sites the new argument touches, their code is unchanged. The rebase had
one conflict: b58a9a97fe7 reverted json_check_mutability(), next to
which 0005 adds the jsonb support function, so 0005 now just appends it.

The cfbot's red mark on v45 from Sunday is MinGW failing to download
MSYS2 (HTTP 504) before the build started, so a cf-bot issue not the
patches.

0001 pageinspect's hot_updates gets the test I described: two btree
indexes over the same values, on full pages, three rounds of
"UPDATE ... SET a = a, c = c + 1". The index on d, which SET
doesn't name, gets the hint and stays small; the one on a, which
SET writes back with its own value, doesn't and splits. 0001
records master's answer (sizes differ) and 0003 flips it. At
20000 rows and eight rounds the index on a ended up at 2.26MB and
the one on d at 1.55MB on master; with 0003 both are 1.55MB. I
used pg_relation_size() rather than counting leaf pages with
bt_page_stats(): the two indexes get the same tuples in the same
order, so with the same hint their sizes match exactly, whatever
the block size. I ran it at BLCKSZ 1, 2, 4, 8, 16 and 32kB: f on
master every time, t with 0003 every time. The table is a
temporary one, so no other session's snapshot can keep its old
versions alive; it passes under installcheck too.

0003 ExecInsertIndexTuples() takes the hint for each index from
modified_idx_attrs, the indexed attributes whose values the row's
update changed, which every UPDATE caller already has since 0002.
It's passed in as a new argument, NULL for inserts. The rules
are the ones index_unchanged_by_update() applied to the columns
SET names: key columns count, INCLUDE columns and the predicate
don't, and an expression counts as changed when any column it
uses changed. The hint is now decided per row, and the per
statement cache in the IndexInfo (ii_CheckedUnchanged,
ii_IndexUnchanged) goes away. So does the updatedCols REPACK
(CONCURRENTLY) built naming every column, which nothing but the
hint read; its indexes now get the hint like any other update's.
In ExecUpdateAct() the set lives in the UpdateContext, in
per-tuple memory, until ExecUpdateEpilogue() has inserted the
entries.

Each later patch narrows the set, so the hint gets more accurate
along the way: an expression whose value stays the same, or a
value that's equal but stored differently, no longer takes it
away.

Peter, on per row vs per statement: _bt_delete_or_dedup_one_page() reads
the hint of the one tuple being inserted, to decide whether to try
_bt_bottomupdel_pass() and whether _bt_dedup_pass() may use its single
value strategy. Both then judge the page by what's on it, so rows of
one statement that disagree just get different treatment for the one
page each of them lands on. I didn't find anything in nbtree that
assumes the answer holds for a whole statement. Tell me if I missed
something, I'm happy to revisit this.

best.

-greg

[1] https://postgr.es/m/auOvC2XUJ-pNmayW@floki