Re: Adding domain type with CHECK constraints slow on large table

Jerry Sievers <jerry@jerrysievers.com> writes:
> I believe what's happening here is that the server doesn't realize
> that the new column is going to have all nulls and that the check
> constraint allows nulls.  As such, the check evidently is being
> evaluated for each row of the table.

Yup, that's right.  There are some corner cases that make that harder to
optimize than it might look:

* volatile functions in the constraint might possibly deliver different
answers at different rows

* if table is in fact empty, we should not throw an error, nor indeed
evaluate the constraint even once (again, volatile functions...)

            regards, tom lane