"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:On Friday, September 3, 2021, Philippe Doussot <philippe.doussot@xxxxxxx> wrote:I don't understand why disabling all index from the table speed up the update because the boolean column is not indexedIndex 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
