Re: Adding a foreign key constraint is extremely slow

On Sun, 23 Mar 2003 bsamwel(at)xs4all(dot)nl wrote:

> Hi guys,
>
> I'm having another performance problem as well. I have two tables called
> "wwwlog" (about 100 bytes per row, 1.2 million records) and table called
> "triples" (about 20 bytes per row, 0.9 million records). Triples contains
> an integer foreign key to wwwlog, but it was not marked as a foreign key
> at the point of table creation. Now, when I do:
>
> alter table triples add foreign key(id1) references wwwlog(id);
>
> PostgreSQL starts doing heavy work for at least one and a half hour, and I
> broke it off at that. It is not possible to "explain" a statement like
> this! Probably what it does is that it will check the foreign key
> constraint for every field in the table. This will make it completely

In fact it does exactly this. It could be done using
select * from fk where not exists (select * from pk where ...)
or another optimized method, but noone's gotten to changing it. I didn't
do it in the start becase I didn't want to duplicate the check logic if it
could be helped.

As a temporary workaround until something is done(assuming you know the
data is valid), set the constraints before loading then turn off triggers
on the tables (see pg_dump's data only output for an example), load the
data and turn them back on.