On 05/09/2021 17:21, Tom Lane wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Friday, September 3, 2021, Philippe Doussot <philippe.doussot@up.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 )
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
