From: | Philippe Doussot <philippe(dot)doussot(at)up(dot)coop> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: update non-indexed value is slow if some non-related index/fk are enabled |
Date: | 2021-09-06 09:32:55 |
Message-ID: | fd92be8d-39dd-1afa-8d12-7095e0ded80a@up.coop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | PALAYRET Jacques | 2021-09-06 12:50:55 | PostgreSQL : column value filtering in Logical Replication |
Previous Message | Philippe Doussot | 2021-09-06 09:29:09 | Re: update non-indexed value is slow if some non-related index/fk are enabled |