| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | Greg Burd <greg(at)burd(dot)me> |
| Cc: | pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, 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-20 23:50:59 |
| Message-ID: | CA+TgmoY8fLnBnPA7iTDTuew7GQgWynoMbEqLm=kz3qU3owP4Pg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
On Tue, Nov 17, 2026 at 2:38 PM Greg Burd <greg(at)burd(dot)me> wrote:
> 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.
I'm not convinced this is a survivable defect. It's certainly a very
serious one. +87% on to_tsvector is really bad, because that's
actually a thing that people want to do.
One answer might be to make this configurable. Suppose you have a
table with a jsonb column called person, and you're going to create an
expression index on that column. Of course, if someone is searching
the table, we can figure out whether the index is useful. But if
someone is updating the table, we need to decide whether it's worth
reeevaluating the expression to see whether we potentially do a HOT
update. That's not an easy decision. If the expression index is on
person->{name}, there is a really good chance that the recheck will
avoid the need for a HOT update. If it's on person->{location}, the
chances of being able to avoid a HOT update are quite poor. If there's
no configuration parameter, how are we supposed to know which case
we've got?
It's previously been proposed that this should be controllable by a
GUC or a table-level reloption, but a GUC doesn't seem nearly granular
enough to me and a table-level reloption is still not great. I think
perhaps we should consider a per-index property. Then we can ask
whether any of the columns in an index that has opted into the feature
have changed, and because the user had to set that property on the
index, they can also do so only in cases where they know that the
recheck has a good chance of working out.
Alternatively, since Jeff expressed the view upthread that this
shouldn't require tuning, we could have infrastructure that studies an
expression and makes an educated guess. It could know that to_tsvector
is expensive and that the result is likely to change if anything in
the input does, and that json subscripting is pulling out part of an
object the rest of which may be unaltered. As compared with an index
property, this is better in that it requires no user configuration and
worse in that it doesn't provide the user with an opportunity to tell
us what they expect to happen. We'd guess that person->{name} and
person->{location} deserve the same treatment, but we'd know that
to_tsvector(...) should get a different one. And the list of operators
that should be considered for this optimization would probably be
quite short.
If we can't agree on or can't get to a satisfying implementation of
one of those two things, I submit that this feature is dead. IMHO,
there's no way that the cost of reevaluating the predicate for every
single expression index is ever going to be small enough that we don't
care about it. We either agree to let the user pick, we design a
mechanism to have the system pick, or we throw out the feature.
Another question is whether evaluation an index expression on the old
tuple is really safe. Currently, we never do that. This patch makes us
do that, but we do not know either that (a) doing so will produce the
same result as before or (b) that no error will occur. In the error
case, the old tuple just can't ever be updated until somebody fixes
something, which is not great. If there's no error but the result
differs, we end up with a mismatch between the indexed value and some
or all of the tuples in the HOT chain to which it points. That can
also happen today, but the first update that touches the indexed
column fixes it: the update will be non-HOT, so the old row and the
index entries pointing to it will eventually go away. Here, that sort
of corruption can more easily survive across any number of updates.
I'm not sure how much this really matters: I'm a bit fearful that
there's some submerged hazard here that is really bad, but as far as I
can think of at the moment is *mostly* just a differently-shaped
version of risks we already accept.
Yet another thought here is that this would be much less tricky if we
only tried to cover SET person['location'] = 'whatever' instead of SET
person = 'the-whole-thing'. That would benefit fewer workloads, maybe
a lot fewer, but we could probably find a way to structurally match
the subscripting ref to the index shape and never have to evaluate
anything for the old row at all.
--
Robert Haas
Databricks
| From | Date | Subject | |
|---|---|---|---|
| Next Message | jian he | 2026-11-21 00:00:00 | Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions |
| Previous Message | Masahiko Sawada | 2026-11-20 23:40:58 | Re: Postmaster PANIC on a standby with slot synchronization enabled |