From: | Philippe Doussot <philippe(dot)doussot(at)up(dot)coop> |
---|---|
To: | rob stone <floriparob(at)gmail(dot)com>, 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:29:09 |
Message-ID: | a0615923-0430-a31c-bef2-65580c30085e@up.coop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/09/2021 10:21, rob stone wrote:
>
> On Sun, 2021-09-05 at 11:21 -0400, 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
>>
>>
> If the DDL for that table had the column defined like this:-
>
> my_boolean BOOLEAN,
>
> instead of:-
>
> my_boolean BOOLEAN NOT NULL DEFAULT FALSE/TRUE, (whichever is
> convenient)
>
> then that column would contain either 'f' or 't' on insert instead of
> null.
>
> Then even if a fillfactor was not specified for that table, an update
> of that single column (which does not appear in an index) would merely
> swap the values.
> Surely that would write it back in place?
>
> Also, having boolean columns containing a null makes it difficult for
> the getter's of that table deciding if 'null' is true or false.
>
> Just an observation.
>
> Rob
>
Yes my columns are NOT NULL DEFAULT FALSE
but the update always change the ctid ( new row in page )
I whas hopping the same optimisation as you: Write in place.
For boolean it is maybe doable because the value is fixed in size but for variable length ..
I was also expecting no row rewrite if value don't change .. easy for boolean but not for bigger fields
Philippe
From | Date | Subject | |
---|---|---|---|
Next Message | Philippe Doussot | 2021-09-06 09:32:55 | Re: update non-indexed value is slow if some non-related index/fk are enabled |
Previous Message | rob stone | 2021-09-06 08:21:57 | Re: update non-indexed value is slow if some non-related index/fk are enabled |