Skip site navigation (1) Skip section navigation (2)

Re: Adding a foreign key constraint is extremely slow

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: <bsamwel(at)xs4all(dot)nl>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Adding a foreign key constraint is extremely slow
Date: 2003-03-23 19:30:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-03-23 21:55:02
Subject: Re: Slow update of indexed column with many nulls
Previous:From: bsamwelDate: 2003-03-23 17:58:24
Subject: Adding a foreign key constraint is extremely slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group