Re: Expanding HOT updates for expression and partial indexes

Hello,

Attached is v47, the same nine patches on master (77bba5454f9). I
benchmarked v46 on a c7i.metal-24xl, which found a cost in the new 0003
that v47 fixes, and a trade it makes that I'd like feedback on. I also
made the tests independent of the block size.

0003 v46 decided the hint per row by walking each expression index's
expressions for every row it inserted. Master walked them once
per statement. v47 caches, per statement, the set of attributes
each index's keys are formed from (ii_KeyAttrs, in place of
master's ii_IndexUnchanged), so a row costs one bms_overlap() per
index. On an UPDATE that changes the key of one of eight indexes,
four of them expressions, over 500k rows (median of 25 runs, ms):

master 0002 0003 (v46) 0003 (v47)
8 indexes 5874 5858 5952 5874
+1.6% +0.3%

I checked that v46's and v47's hints are the same, index by index,
on a table with plain, INCLUDE, expression, partial, multicolumn
and whole-row indexes.

0002 A comment in heap_update() still described the key-column test
that moved to HeapUpdateDetermineLockmode(); the note about
detoasted values that end up the same now sits with that test.

0001, 0007 The tests no longer depend on BLCKSZ. I ran pageinspect,
amcheck and the main regression suite at 1, 2, 4, 16 and 32kB, on
0001 alone and on the whole series. The parts these patches add
now pass at every size; what still fails at a non-default size are
upstream's own tests, which fail on master too.

- amcheck's hot_bt_image sizes its values from the block size:
e is 32 bytes short of a quarter of a block, which puts the row
over the TOAST threshold on its own, so it goes out of line,
and still fits in a btree entry, which is never more than 8191
bytes; t is three eighths of a block, so it gets compressed.
With fillfactor 10 the new versions fit on their pages even
with 1kB blocks. Whether e is out of line is now checked with
pg_column_toast_chunk_id(), which pg_column_size() couldn't
tell apart from a long inline value.

- hot_updates' write-back cases in the main suite use values of
three eighths of a block. At 16 and 32kB the old 3000 bytes
stayed inline uncompressed, and the unique index case turned
HOT.

- pageinspect's indexUnchanged test has a row for every two bytes
of a block, so the indexes span the same number of leaf pages
at any size. Its same_size was already f on master and t with
0003 at every size I tried, and is still. The line pointers
its HOT chain test prints did not depend on the block size
either: three small rows and their new versions share block 0
even with 1kB blocks.

Nothing else changed.

The trade is an UPDATE that writes back the value of an indexed column,
ORM style, on full pages: 1M rows, fillfactor 100, eight rounds of
"UPDATE w SET a = a, c = c + 1" with no VACUUM in between, data on tmpfs
(median of 5):

updates index pages index-only scan
master (no hint) 20163 ms 13710 92 ms
0003 (hint) 31750 ms 9189 78 ms

With the hint the index stays a third smaller and scans 15% faster, but
the updates take 57% longer, here, where nothing waits on I/O and a page
split costs little. That isn't new to 0003. Master already pays it for
any index SET doesn't name: with a second index d over the same values,
which master gives the hint, the run takes 57.8 s on master and 67.4 s
with 0003, and d stays at 9189 pages in both. 0003 only stops naming
a column in SET from taking the hint away. If you'd rather the written
back case keep master's behavior, 0003 drops out cleanly.

Overall, master against v46 (0001-0009), CPU bound: tmpfs, fsync and
full_page_writes off, turbo off, VACUUM and CHECKPOINT before each run,
3 interleaved rounds, the first run of each set dropped, ms. None of
these has an expression index that gets new entries, so v47's change to
0003 doesn't reach them:

master v46
same value written back
btree, compressed value 4505 330 -92.7%
jsonb, expression index unchanged 1214 362 -70.2%
value changes
btree, compressed, same length 2088 2182 +4.5%
short indexed value 2956 2970 +0.5%
unindexed column 536 538 +0.4%

A second master build ran alongside as an A/A check; it was within 0.1%
of the first on each workload. The compressed case is the detoasting
v43 added to find that two values are different.

pgbench, which reviewers asked for, again master against v46: scale
100, 16 clients, fillfactor 100, 120 s per run, 6 runs each, ABBA
order, tps:

master v46
default 90367 90442 +0.1%
index on abalance 88137 87276 -1.0%

The default case is HOT on both (97%). With the index on abalance
nothing is HOT, and v46 pays the comparison 0002 adds. The spread
within each set was about 2%, so -1.0% is at the edge of what this run
can tell.

best.

-greg