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

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Philippe Doussot <philippe(dot)doussot(at)up(dot)coop>
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-05 15:08:23
Message-ID: CAKFQuwZbx8v9Ect=FDk34FhJdCDw3ekQ=vDcKW==XaNTCiF5Cg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Friday, September 3, 2021, Philippe Doussot <philippe(dot)doussot(at)up(dot)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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-09-05 15:21:06 Re: update non-indexed value is slow if some non-related index/fk are enabled
Previous Message Richard Michael 2021-09-05 13:35:18 How to observe plan_cache_mode transition from custom to generic plan?