pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
foreign key take too much time to check
2+ messages / 2 participants
[nested] [flat]

* foreign key take too much time to check
@ 2000-08-07 19:39 Jie Liang <jliang@ipinc.com>
  2000-08-07 19:57 ` Re: foreign key take too much time to check Mark Volpe <volpe.mark@epamail.epa.gov>
  0 siblings, 1 reply; 2+ messages in thread

From: Jie Liang @ 2000-08-07 19:39 UTC (permalink / raw)
  To: pgsql-sql

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@ipinc.com
www.ipinc.com






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: foreign key take too much time to check
  2000-08-07 19:39 foreign key take too much time to check Jie Liang <jliang@ipinc.com>
@ 2000-08-07 19:57 ` Mark Volpe <volpe.mark@epamail.epa.gov>
  0 siblings, 0 replies; 2+ messages in thread

From: Mark Volpe @ 2000-08-07 19:57 UTC (permalink / raw)
  To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql

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@ipinc.com
> www.ipinc.com



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-08-07 19:57 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-07 19:39 foreign key take too much time to check Jie Liang <jliang@ipinc.com>
2000-08-07 19:57 ` Mark Volpe <volpe.mark@epamail.epa.gov>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox