From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Philippe Doussot <philippe(dot)doussot(at)up(dot)coop>, "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:21:06 |
Message-ID: | 2875218.1630855266@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Shubham Mittal | 2021-09-05 17:06:42 | Re: Query takes around 15 to 20 min over 20Lakh rows |
Previous Message | David G. Johnston | 2021-09-05 15:08:23 | Re: update non-indexed value is slow if some non-related index/fk are enabled |