Re: BUG #10591: setting newly added columns to null is slow

"eike@inter.net" <eike@inter.net> wrote:=0A=0A>- given demo_table with some=
 amount of data=0A>- add new_column (works instantely)=0A>- add a unique co=
ntraint on new_column=0A>- update demo_table set new_colum=3Dnull; (quite s=
low)=0A>-> takes a long time: seems to rewrite the whole table=0A=0AThis is=
 not a bug.=A0 It has nothing to do with having added the=0Acolumn.=A0 An U=
PDATE statement with no WHERE clause will delete and=0Are-add every row in =
the table, even if your update is setting a=0Acolumn to the value it alread=
y has.=0A=0A> I guess that adding a new column does not yet reserve space f=
or=0A> that new column on disk, but that the on-disk layout is only=0A> cha=
nged as soon as I do the update (aka making the column=0A> physically prese=
nt on disk)=0A=0ANo, the table can immediately be used, and the new column =
will be=0ANULL.=A0 The UPDATE was completely unnecessary.=A0 When in doubt,=
 use a=0AWHERE clause that ensures you are not updating unnecessarily, like=
:=0A=0AUPDATE demo_table=0A=A0 SET new_column =3D NULL=0A=A0 WHERE new_colu=
mn IS NOT NULL;=0A=0A> However from my uneducated guess (not tested) I beli=
eve that a=0A> vacuum full followed by the update would have been much fast=
er.=0A=0AIt would not have been.=A0 VACUUM FULL is not often a good idea;=
=0Aalthough you are now in a state where this table is heavily=0Abloated by=
 the unnecessary update of every row, so it might=0Aactually be worth it no=
w.=0A=0Ahttp://rhaas.blogspot.com/2014/03/vacuum-full-doesnt-mean-vacuum-bu=
t.html=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterprisedb.com=0AThe En=
terprise PostgreSQL Company