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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Jerry Sievers <jerry(at)jerrysievers(dot)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

Understood.

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

The table is big, the check constraint is trivial and the col values
will be all null. This is a tempting hack-around case.

Think I'm going to hide the constraint by temporarily toggling to zero
the contypid field in pg_constraint, around the alter table add column
statement. I've tested this and it allows the alter to happen fast.

Thanks for the information.

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
-------------------------------------------------------------------------------
Jerry Sievers 732 365-2844 (work) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant