Received: from tidal.ipinc.com (tidal.ipinc.com [199.245.188.85]) by hub.org (8.10.1/8.10.1) with ESMTP id e77JdYG07717 for ; Mon, 7 Aug 2000 15:39:35 -0400 (EDT) Received: from ipinc.com (jliang.ipinc.com [199.245.188.130]) by tidal.ipinc.com (8.9.3/8.9.3) with ESMTP id MAA62137 for ; Mon, 7 Aug 2000 12:39:24 -0700 (PDT) (envelope-from jliang@ipinc.com) Message-ID: <398F105F.107064E0@ipinc.com> Date: Mon, 07 Aug 2000 12:39:12 -0700 From: Jie Liang Organization: ipinc.com X-Mailer: Mozilla 4.7 [en] (X11; U; FreeBSD 3.4-RELEASE i386) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: foreign key take too much time to check Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/57 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