pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Greg Burd <greg@burd.me>
To: pgsql-hackers@lists.postgresql.org
To: pg@bowt.ie
Cc: pgsql@j-davis.com
Subject: Re: Expanding HOT updates for expression and partial indexes
Date: Thu, 29 Oct 2026 20:46:03 +0000
Message-ID: <auOvC2XUJ-pNmayW@floki> (raw)
In-Reply-To: <y5Lpi3Xhb6mwYUiabFvbjjna67H6JbQtBWWqy27mqQ00v_QPqc7FEm1b4LD9eWyqey9tJoU1KcNK6eeBg8ddMbwgVQ_-qgI34JECCz9mDIY=@burd.me>
References: <7b8681a578b5fe77103948eeb7b5cdd80fabad5d.camel@j-davis.com>
	<4040d3a4-01c2-4f24-9025-57cfc19aea57@app.fastmail.com>
	<acQYVfzHoL4lVTUE@nathan>
	<6609db3402d0d2994144abc2081d1b790e644c0d.camel@j-davis.com>
	<-ugooulGn7M5X4DFIzWHSgYRngUmXChD_a-JvcMaklnDNcHMGnQhcM82SpYY76Je5ss4TOrsQB5sv2USLRSlDcpwDX-6CgCCbGKlJuA0SOs=@protonmail.com>
	<9XW-2xmCYWws1lnV5ELwZ67eaqyJNb9gJ6R2aUxntUuHetRia8NhzpSEl4gqtpKLtLwfqnBwnj5xOIs8N4Zd2x6x7gCyd5-HXocpMF4Fkj4=@burd.me>
	<CAEze2WiCn74vqoqfeuK_9mhwFABRf7tTNdMUuMzoU=VRcQKkEQ@mail.gmail.com>
	<XtuZX8xWYFYMwfJ0BFIwteRjtst6uj1MVjGxiXNb8PuUJwkAUhvdQqeE1wbZPiYvmZ9G92o6CORDsp1vb3S8ci-lublEhHrFTil4GIYQBvU=@burd.me>
	<atpXQaH1UBS3LY-6@floki>
	<y5Lpi3Xhb6mwYUiabFvbjjna67H6JbQtBWWqy27mqQ00v_QPqc7FEm1b4LD9eWyqey9tJoU1KcNK6eeBg8ddMbwgVQ_-qgI34JECCz9mDIY=@burd.me>

Hey Peter,

I'm adding you for one question about the indexUnchanged hint,
which this series could make more accurate.  I'd rather ask before
changing it.

Since 0002 the executor knows, per row, which indexed attributes an
UPDATE changed (modified_idx_attrs).  It compares values after BEFORE
ROW triggers and after EvalPlanQual.  index_unchanged_by_update()
(9dc718bdf2b) still works from ExecGetUpdatedCols(), the columns
named in SET, and caches its answer per statement in
ii_CheckedUnchanged.  The two can disagree either way:

 - A BEFORE ROW trigger that changes an indexed column the SET clause
   didn't name, as tsvector_update_trigger() does, leaves it out of
   updatedCols.  The hint then says "unchanged" for an index whose key
   changed.

 - An UPDATE that writes back the values a row already has (ORMs that
   send every column; logical replication apply, which marks every
   column the publisher sent) has those columns in updatedCols.  The
   hint then says "changed" for every index although nothing changed.
   When such an update can't be HOT because the page is full, that's
   the version churn bottom-up deletion is for.

The docs allow both false positives and false negatives, and you've
called it "just a hint used to trigger bottom-up index deletion" [1].
So neither is a bug.  The question is whether you'd want the better
answer now that the executor already has the set: the hint for index
i would be "no key column of i is in modified_idx_attrs", for the row
being inserted.  INCLUDE and predicate columns stay out as today, and
expression keys stay conservative, as Tepid's ExecSetIndexUnchanged
does.

What makes me ask first: the hint would become per row, so two rows
of one statement could get different answers.  _bt_findinsertloc()
says the hint "should not significantly affect our behavior over time
unless practically all inserts on to the leaf page get the hint",
which I read as tolerant of that.  If you see a reason it should stay
per statement, I'll leave it alone.  If not, I'd add it to 0002 in
v45.  The test would repeat an UPDATE that writes back an indexed
column's value on full pages, so it can't be HOT, and compare that
index's leaf page count (pageinspect's bt_page_stats) with and without
the change.

This also fits the split Jeff described earlier in this thread: "It's
wrong for the executor to say 'do a HOT update' but it's OK for the
executor to say 'this is the set of indexes that might have a new key
after the update'" [2].  The hint would come from that same set.

thoughts?

-greg

[1] https://postgr.es/m/CAH2-Wz=rugC=iZTDCKDo2gv-4u7Mkih=akqq07NVz-nai6=vKg@mail.gmail.com
[2] https://postgr.es/m/f59348afe291ef62b8596fc1615104c51c620bec.camel@j-davis.com








view thread (87+ messages)  latest in thread

Message-ID: <auOvC2XUJ-pNmayW@floki>
Permalink:  ../auOvC2XUJ-pNmayW@floki/
Also on:    postgresql.org/message-id/auOvC2XUJ-pNmayW@floki

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: greg@burd.me, pgsql-hackers@lists.postgresql.org, pg@bowt.ie, pgsql@j-davis.com
  Subject: Re: Expanding HOT updates for expression and partial indexes
  In-Reply-To: <auOvC2XUJ-pNmayW@floki>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox