Re: Expanding HOT updates for expression and partial indexes

On Friday, March 27th, 2026 at 1:01 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Wed, 2026-03-25 at 12:16 -0500, Nathan Bossart wrote:
> > I just spoke to Greg off-list and wanted to share my current thoughts
> > on
> > the list as well. In short, while we feel that the patch is in
> > decent
> > shape and seems to be performance neutral (or maybe even positive in
> > some
> > cases), it obviously doesn't accomplish $subject,
>
> Agreed. I like the direction this is going, but if we can't accomplish
> $subject in 19, then let's move to the next cycle.

Wow, is it already mid-v20 cycle? Time flies when you're having fun.

> Regards,
> Jeff Davis

And we're back!

It has been a bit since the last patch that went out in March. Attached
is v39, eight patches on master (d733fcaf5ca), which now actually does what
the $subject says and the speed-ups are measured.

What's interesting is that a few patches in this series have had a different
life in a separate thread [1], I need them for both so I guess I'll just add
them to both and sort it out later. If that's not The Way(TM) to do this
then let me know what is. :)

Changes since v38:

0001 HOT test coverage (was v38-0001). Tests only, with master's expected
output, so each later patch's behavior change shows up as an
expected-output diff. Adds Tom's 2018 gist point->box crash case and
an EvalPlanQual isolation spec.

0002 Identify modified indexed attributes in the executor (was v38-0002),
with Nathan's and Jeff's review comments addressed.

0003 New: a column used only through index expressions or partial-index
predicates no longer blocks HOT if each such index's datums are
byte-identical and the row neither enters nor leaves its predicate.
Key and replica identity columns are never narrowed.

0004 New: a SupportRequestSubattr support request lets functions say which
parts of a value they read or change (jsonb, arrays, records, and
extension types). When the SET clause provably can't touch what an
index reads, 0003's evaluation is skipped. This is purely an
optimization; the HOT decision is the same.

0005 New: an optional index AM callback, amcomparedatums, "would the new
value give exactly the same index entries?". It lets columns that
indexes store directly be narrowed too. It's never used for
exclusion constraints, which are checked on values.

0006-0008 New: nbtree - datum_image_eq, GIN - same extracted keys, and hash
- same hash code, implementations for amcomparedatums.

Numbers (c7i.8xlarge, release builds, best of 5 runs, single UPDATE over
all rows):

jsonb, 2 expression indexes, unindexed key 3600ms -> 788ms 0 -> 78% HOT
partial index, row stays outside 900ms -> 490ms 0 -> 80%
btree on 3kB text, same value written back 58854ms -> 939ms 0 -> 79%
GIN int[], elements reordered 692ms -> 298ms 0 -> 80%
hash on numeric, display scale changed 1595ms -> 564ms 0 -> 79%
controls and pgbench within +/-3%

One known cost: when an *expensive* index expression's value does change
(to_tsvector, md5 of large text), 0003 evaluates it for both rows and the
index insert evaluates it again, so those UPDATEs are about 55% slower.
Cheap expressions (->>) show nothing. For v40 I plan to hand the computed
datums to ExecInsertIndexTuples(), as Matthias suggested earlier.

This started off as an exercise in allowing JSONB updates to go HOT so as
to reduce bloat. Now not only does that work, but we're bringing in XML,
ARRAY, and 3rd-party extensions...

DocumentDB[2] is the motivating extension, its RUM indexes key on the whole
BSON document with the indexed path in opclass options, so today none of
its updates is ever HOT. With this series and an amcomparedatums for its
RUM fork[3], an updateMany or updateOne of an unindexed field went from
0% to 100% HOT.

best.

-greg

[1] https://postgr.es/m/10eD4qtjEu2S7fepnPrBqxg73zuMbBCHN1MOEsbrwlPgOoGVzpzOchAF1GqaU8tKrIVCykGm7HVl6EUUonIheJNr9OQ2C5uajOLd1WLThlI%3D%40burd.me
[2] https://github.com/documentdb/documentdb
[3] https://github.com/documentdb/documentdb/pull/789