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.g.johnston@gmail.com> writes:
On Friday, September 3, 2021, Philippe Doussot <
philippe.doussot@up.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