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

"eike(at)inter(dot)net" <eike(at)inter(dot)net> wrote:

>- given demo_table with some amount of data
>- add new_column (works instantely)
>- add a unique contraint on new_column
>- update demo_table set new_colum=null; (quite slow)
>-> takes a long time: seems to rewrite the whole table

This is not a bug.  It has nothing to do with having added the
column.  An UPDATE statement with no WHERE clause will delete and
re-add every row in the table, even if your update is setting a
column to the value it already has.

> I guess that adding a new column does not yet reserve space for
> that new column on disk, but that the on-disk layout is only
> changed as soon as I do the update (aka making the column
> physically present on disk)

No, the table can immediately be used, and the new column will be
NULL.  The UPDATE was completely unnecessary.  When in doubt, use a
WHERE clause that ensures you are not updating unnecessarily, like:

UPDATE demo_table
  SET new_column = NULL
  WHERE new_column IS NOT NULL;

> However from my uneducated guess (not tested) I believe that a
> vacuum full followed by the update would have been much faster.

It would not have been.  VACUUM FULL is not often a good idea;
although you are now in a state where this table is heavily
bloated by the unnecessary update of every row, so it might
actually be worth it now.

http://rhaas.blogspot.com/2014/03/vacuum-full-doesnt-mean-vacuum-but.html

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company