The following bug has been logged on the website:
Bug reference: 10591
Logged by: Eike Dierks
Email address: eike@inter.net
PostgreSQL version: 9.2.1
Operating system: OSX 10.9, centos on virtuozzo linux-2.6.18-028stab
Description:
- 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
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)
I fully understand that this is a sensible tradeoff and that this is the
intended behaviour.
However from my uneducated guess (not tested) I believe that a vacuum full
followed by the update would have been much faster.
I somehow believe that my first sequence of commands did make the new_column
virtual first and then physically for every row in turn, thereby taking a
long time to complete.
I'd like to suggest to add to the documentation (if it's not already in
there)
It should be stated that a vacuum full might speed up further access to a
newly introduced column.
But again I did not fully test that scenario, it's just my guess.