Re: To-Do item: skip table scan for adding column with provable check constraints

On Thu, May 19, 2016 at 7:33 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> I recently had to run something like:
>
> alter table pgbench_accounts add locked text check (locked != 'unlocked');
>
> And was surprised that it took several minutes to complete as it
> scanned the whole table.
>
> The new column is going to start out as NULL in every row, so there is
> no need to validate the check constraint by reading the table as it
> can be proven from first principles. Correct?

Right. If there were a DEFAULT on the new column that would of course
be different, and you can also do thinks like CHECK (a != b) here.
However, if the CHECK constraint does not reference any column other
than the newly-added one, and if the new column will have the same
value for every row either because there is no default or because the
default is a constant, then we can test the CHECK constraint just once
against the value that all new rows will have instead of testing it
once per row.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company