Re: update non-indexed value is slow if some non-related index/fk are enabled

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Friday, September 3, 2021, Philippe Doussot <philippe(dot)doussot(at)up(dot)coop>
> wrote:
>> I don't understand why disabling all index from the table speed up the
>> update because the boolean column is not indexed

> Index entries point to physical records. You just deleted one physical
> record and added another. The indexes need to be updated with that
> information.

Yeah. The OP's mental model is apparently update-in-place, but that's
not how Postgres does things.

The index-update overhead is avoided if the update is "HOT", which
requires that (a) no indexed column changes and (b) there is room
on the same page for the new copy of the row. Ensuring (b) requires
running with a fairly low fill-factor, which bloats your table and
thereby creates its own costs. Still, that might be worth doing
depending on your particular circumstances.

regards, tom lane