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

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