Re: foreign key take too much time to check

Try creating the tables without the constraint first,
then populate them, and then add the foreign key constaint
as the last step using ALTER TABLE/ADD CONSTRAINT.

Mark

Jie Liang wrote:
>
> Hi, there,
>
> I want add a constraint to my tables:
> I have 2 tables:
> 1. Table_A(id int 4 primary key, item text, ......),
> 2. Table_B(id int4, cid int4, constraint b_fk foreign key (id)
> refereneces Table_A(id)
> on delete cascade on update cascade);
> Table_A has ~900,000 rows,
> Table_B has ~1,200,000 rows.
> I tried serveral ways to generate the b_fk
> 1. alter table add constraint b_fk foreign key (id) refereneces
> Table_A(id)
> on delete cascade on update cascade,
> 2. pg_dump -d -t Table_B -f B dbname, then psql dbname < B
> even before that , I create an index for id of Table_B, and vacuum table
> Table_A and Table_B,
>
> insert (check) speed is very slow, ~100 rows per minute, for my table
> has ~1.2M rows will
> take more than 200hours, I wonder to compare 2 integers why takes so
> long, if no b_fk , the Table_B
> can be reload in 5 minutes...
>
> Is anybody know how reslove this problem? I 'll appreciate.
>
> --
> Jie LIANG
>
> Internet Products Inc.
>
> 10350 Science Center Drive
> Suite 100, San Diego, CA 92121
> Office:(858)320-4873
>
> jliang(at)ipinc(dot)com
> www.ipinc.com