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

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