Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: update non-indexed value is slow if some non-related index/fk are enabled
Date
Msg-id CAKFQuwZbx8v9Ect=FDk34FhJdCDw3ekQ=vDcKW==XaNTCiF5Cg@mail.gmail.com
Whole thread Raw
In response to update non-indexed value is slow if some non-related index/fk are enabled  (Philippe Doussot <philippe.doussot@up.coop>)
Responses Re: update non-indexed value is slow if some non-related index/fk are enabled  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
On Friday, September 3, 2021, Philippe Doussot <philippe.doussot@up.coop> wrote:

One UPDATE of one boolean value which is non-indexed take some time ( for many tuples .. 3 Millions ).

But if we disable all indexes/fk the UPDATE is 10x faster and do 10x less shared hit .

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.

David J.

pgsql-general by date:

Previous
From: Richard Michael
Date:
Subject: How to observe plan_cache_mode transition from custom to generic plan?
Next
From: Tom Lane
Date:
Subject: Re: update non-indexed value is slow if some non-related index/fk are enabled