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

On 05/09/2021 17:21, Tom Lane wrote:
> "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
Hi,

Thank you both David and Tom for this precious information.

Yes, I was thinking that the update was made in-place because a boolean true or false take the same size :).
I did't expect the ctid was changed.

The main reason is effectivly
> (b) there is (no) room on the same page for the new copy of the row.

I now see that the default TABLE fillfactor is 100. No room for update in same page by default (our case).

The CTID is changing for each update (even if I UPDATE many time the same boolean column with same value false, false, false .. maybe a room for optimisation here )

And with some room (lower fillfactor) I see the HOT working well with the help from https://habr.com/en/company/postgrespro/blog/483768/ , Thanks Егор Рогов @erogov

Disabing index during update obviously put index out of sync with the new row location which require an reindex.

I can now better explain to my team why this update is slow ( without lower fillfactor).
Why it is quick with index disabled.
Why disabling index without reindexing it after enabling it is a very bad idea .. the ctid as changed (without HOT update) and index is out-of-date.

Many thanks

Philippe