On Sun, 2021-09-05 at 11:21 -0400, Tom Lane wrote: > "David G. Johnston" <david.g.johnston@xxxxxxxxx> writes: > > On Friday, September 3, 2021, Philippe Doussot < > > philippe.doussot@xxxxxxx> > > 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
