Обсуждение: Adding domain type with CHECK constraints slow on large table

EnterpriseDB 8.2.4

create domain foodomain text check (value in ('val1', 'val2'));

alter table bigtable add foodomain;

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.

I'm tempted to update pg_constraint around the alter table statement
to in the same transaction frob contypid to 0 and then back to the
domain pg_type.oid field value to (not yet tested), prevent the check
from being evaluated.

In our case, this hackery would save hours of downtime on a prod
system.

Comments?

PS: Sent this a few hours ago and never saw it.  Sorry if duplicate.

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

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

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