Re: Expanding HOT updates for expression and partial indexes

Hello,

Attached is v43, eight patches on master (871dee0ccbe). It does what I
said I would in my last mail. The executor treats an indexed value
that is the same but stored differently as unchanged, for every access
method; the nbtree callback is gone; amcomparedatums is never asked
about a column the index can return; and the detoasting this costs is
measured below. 0009 and 0010 from v41 are still out of the set.

0005 amcomparedatums is not asked about a column the index can return
(index_can_return()), neither when the relcache decides which
columns are COMPARABLE nor when the executor compares an index's
datums. So the callback only answers for entries no index-only
scan can see, and an AM that later learns to return a column
loses the shortcut rather than giving a wrong answer.
dummy_index_am gets an amcanreturn driven by a reloption, and the
update its test makes HOT is not HOT with that turned on.

0006 Replaces "nbtree: implement amcomparedatums". When two values of
an indexed column differ as stored and one of them is compressed
or out of line, they are compared with datum_image_eq(), and if
they are the same the column counts as unchanged. That is done
once for each changed column, before any narrowing. The
narrowing then takes a column an index stores as changed exactly
when it is still in the changed set, so its values aren't
compared or fetched a second time; only the datums of expressions
are compared again, the same way. Columns of candidate keys and
of the replica identity are still compared as stored, because
heap_update() takes its lock strength and what it logs for
decoding from them; the cross-check in heap_update() in
assert-enabled builds allows for exactly that. hot_updates gets
four write-back cases: under SP-GiST, under a partial btree index
and under an expression that returns the value as is, all now
HOT, and under a unique index, which stays non-HOT.

Smaller things: heap_attr_equals()'s comment still named the function
0002 renamed, and a test had a redundant pg_stat_force_next_flush().

The cost of detoasting. Release builds on a c7i.8xlarge,
shared_buffers 8GB, fillfactor 50, with the data directory on tmpfs,
fsync and full_page_writes off, and a CHECKPOINT before each run. On
disk, checkpoints decided the result: the versions that make the
write-back cases HOT write less WAL, so their checkpoints come at
different points of the later workloads, which moved one of them by
40%. One UPDATE over every row of a 100k-row table (50k for hash,
500k for the controls), with 3000-byte values that compress, or 2500-
and 3200-byte values stored uncompressed out of line (STORAGE
EXTERNAL). Median of 12 runs (3 interleaved rounds of 5, the first
run of each dropped), in ms:

master v43 v43/master
value changes, same length
compressed, btree 1423 1498 +5.3%
out of line, btree 3428 3461 +0.9%
out of line, hash 1318 1491 +13.1%
value changes, different length
compressed, btree 6213 6250 +0.6%
same value written back
compressed, btree 3023 231 -92.4%
out of line, btree 2184 1245 -43.0%
compressed, SP-GiST 681 236 -65.3%
out of line, hash 730 660 -9.5%
controls
unindexed column changes 370 352 -4.9%
short indexed value changes 2113 2117 +0.2%

So your concern is right, and now we know what it costs. Nothing is
fetched when the lengths differ, or when an unchanged value keeps its
toast pointer. When a toasted value changes but keeps its length, the
old value is detoasted to find that it changed; the new one is still
in memory at that point. That costs 5% when the value is compressed,
and about 1% out of line under btree, where inserting a 2.5 kB key
costs much more. Out of line under hash is the worst case, at +13%,
and most of that isn't detoasting. hashcomparedatums() hashes both
3 kB values, and the insertion hashes the new one again, so perf shows
three times master's time in hash_bytes(); that is about two thirds of
the difference, and fetching the old value is the rest. v42 hashed the
same way. Without the changed set passed down, the narrowing compared
and fetched once more, and this case was +18%.

If the hashing matters, the executor could skip the callback for a
column whose type's equality implies image equality (btree's
equalimage): two values with different images then have the same hash
code only by collision. Numeric and nondeterministic collations,
where the callback earns its keep, would still be asked.

The write-back side is what ORMs and logical replication do with every
UPDATE: no version before v43 made the SP-GiST case HOT, and under hash
v42 made the write-back HOT but slower than master, because hashing
both values cost more than the index insertion saved; comparing images
first makes it 9.5% faster. The unindexed-column control came out
faster than master in every round, here and in an earlier run on disk.
I can't explain that and wouldn't count on it.

I haven't touched simple_heap_update(); I might try to deform both
tuples once, maybe it will be in the next version.

best.

-greg