| From: | Greg Burd <greg(at)burd(dot)me> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Peter Geoghegan <pg(at)bowt(dot)ie>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, Nathan Bossart <nathandbossart(at)gmail(dot)com> |
| Subject: | Re: Expanding HOT updates for expression and partial indexes |
| Date: | 2026-11-17 19:36:43 |
| Message-ID: | 179491897173.2595695.14244909545607578521@burd.me |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hello,
Attached is v48, rebased on master (7fc2f042332), with one patch more
that stops evaluating an index's expressions a second time for the new
row.
0010 - ExecUpdateModifiedIdxAttrs() forms the index's datums for the old
row and the new, and evaluates its predicate. This patch keeps what was
learned of the new row, per index, in the ResultRelInfo (in per-tuple
memory) and ExecInsertIndexTuples() uses it.
An expression whose value changes is evaluated twice per row, for the
old row and the new, where master evaluates it once. Without updating
the executor during processing the SET a to a' there's no knowing it is
unchanged without the creating old value. We could, and I've tried it,
collect the information in the executor but that makes this patch set
even larger and much more complex. I think that's better left to the
next change if/when this one lands.
This change halves, but doesn't remove, the cost Tom raised in 2018 for
indexes whose expression maps one to one [1]:
This materially moves the overhead, it doesn't erase it, but it does
improve the picture quite a bit.
master 0001-0009 0001-0010
to_tsvector() under GIN 4019 11028 7518
+174% +87%
md5() of 2kB 2732 3727 3229
+36% +18%
lower(email) 2566 3014 2846
+17% +11%
partial index, key changes 1726 1870 1842
+8% +7%
0002 - One thing about simple_heap_update() that matters for extensions,
which Andres pointed out call it for their own tables [2], its
signature and its HOT and lock decisions shouldn't change if at all
possible. I've not looked for extensions that might be impacted were it
to change, but it's a fair assumption that someone is depending on its
current shape.
The whole simple_heap_update() path and system catalogs is an eyesore
IMO, but for now that's just how it has to be.
simple_table_tuple_update() now skips the fetch for a relation without
indexes too. v47 fetched the old row for every relation so this is a
bit more efficient.
I do like this patch series quite a bit, the only way to cut down the
overhead even more is to re-introduce what Nathan and Jeff both asked me
to remove to simplify this patch set. They were not wrong, it is
simpler without that 1,500 additional lines of code. But, in order to
make progress on this front we need to accept this overhead, maybe a GUC
to opt-in or something, and then a follow-on bit of work to reduce it to
near-zero overhead. I'm game for that work if whomever ends up latching
onto this is interested in seeing it happen as a follow-on.
As it stands the series does what it advertises but the cost is there
and to be fair this email shows only that, not the benefit of enabling
HOT for these new use cases. As always, it's a trade-off.
best.
-greg
[1] https://postgr.es/m/25975.1541618754@sss.pgh.pa.us
[2] https://postgr.es/m/akciabcu3b2hchj7adxhu4kovfaozp2pcn2z7sdljfthxcyg4o@7e6sfyzipvyy
| Attachment | Content-Type | Size |
|---|---|---|
| v48-0001-Improve-test-coverage-for-heap-only-tuple-HOT-up.patch | text/x-patch | 70.3 KB |
| v48-0010-Reuse-an-UPDATE-s-computed-index-datums-when-ins.patch | text/x-patch | 21.3 KB |
| v48-0007-Treat-an-indexed-value-stored-differently-but-un.patch | text/x-patch | 25.8 KB |
| v48-0003-Give-the-indexUnchanged-hint-from-the-attributes.patch | text/x-patch | 23.2 KB |
| v48-0009-hash-implement-amcomparedatums.patch | text/x-patch | 14.3 KB |
| v48-0008-GIN-implement-amcomparedatums.patch | text/x-patch | 13.0 KB |
| v48-0004-Allow-HOT-updates-when-expression-and-partial-in.patch | text/x-patch | 42.8 KB |
| v48-0006-Add-amcomparedatums-index-AM-callback-for-HOT-up.patch | text/x-patch | 44.0 KB |
| v48-0005-Skip-evaluating-indexes-that-an-UPDATE-s-SET-cla.patch | text/x-patch | 99.7 KB |
| v48-0002-Identify-modified-indexed-attributes-in-the-exec.patch | text/x-patch | 55.5 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Greg Burd | 2026-11-17 19:53:31 | Re: Let an ordering index scan hand its ORDER BY value to the target list |
| Previous Message | Zsolt Parragi | 2026-11-17 19:36:42 | Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns |