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

Re: alter table TBL add constraint TBL_FK foreign key ...

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Minghann Ho <Minghann(dot)Ho(at)mcs(dot)vuw(dot)ac(dot)nz>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: alter table TBL add constraint TBL_FK foreign key ...
Date: 2003-01-01 03:38:54
Message-ID: 20021231193351.U68640-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 1 Jan 2003, Minghann Ho wrote:

> I've experienced very slow performance to add foreign key constraints using
> ALTER TABLE ADD CONSTRAINT FOREIGN KEY ...
>
> After using COPY ... FROM to load the base tables, I started to build the
> referential integrity between tables.
> I have 3 tables: T1 (6 million records), T2 (1.5 million records) and T3 (0.8
> million records).
> One of the RI - foreign key (T1 -> T2) constraint took about 70 hrs to build.
> The other RI - foreign key (T1 -> T3) constraint took about 200 hrs and yet
> completed!! (compound foreign key)
>
> I tried to use small subset of the tables of T2 and T3 to do the testing.
> An estimation show that it need about 960 hrs to build the RI - foreign key
> constraints on table T1 -> T3 !!!

It's running the constraint check for each row in the foreign key table.
Rather than using a call to the function and a select for each row, it
could probably be done in a single select with a not exists subselect, but
that hasn't been done yet.  There's also been talk about allowing some
mechanism to allow the avoidance of the create time check, but I don't
think any concensus was reached.


In response to

pgsql-performance by date

Next:From: Ron JohnsonDate: 2003-01-01 08:14:34
Subject: Re: alter table TBL add constraint TBL_FK foreign key
Previous:From: Minghann HoDate: 2003-01-01 03:38:06
Subject: alter table TBL add constraint TBL_FK foreign key ... very slow

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