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@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 indexedIndex 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 laneIf 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
