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

Tom Lane <tgl@sss.pgh.pa.us> writes:

> 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

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