Received: from tidal.ipinc.com (tidal.ipinc.com [199.245.188.85]) by hub.org (8.10.1/8.10.1) with ESMTP id e7UJMMC13840 for ; Wed, 30 Aug 2000 15:22:22 -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 MAA67570; Wed, 30 Aug 2000 12:21:55 -0700 (PDT) (envelope-from jliang@ipinc.com) Message-ID: <39AD5E65.E79C06F1@ipinc.com> Date: Wed, 30 Aug 2000 12:20:05 -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: Webb Sprague CC: Stephan Szabo , pgsql-sql@postgresql.org Subject: Re: Optimizing huge inserts/copy's References: <20000830185717.12822.qmail@web803.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/344 Hi, I knew that if no constarint, it populate very quick, my question is: when two tables have been reloaded, then I want to add a foreign key constraint to it, say: tableA has primary key column (id) tableB has a column (id) references it, so I say: ALTER TABLE tableB ADD CONSTRAINT distfk FOREIGN KEY (id) REFERENCES tableA(id) ON DELETE CASCADE ; It just seems takes forever. Thanks anyway. Webb Sprague wrote: > I am experimenting with this too. If I have any > indexes at all, the copy's get VERY SLOW as the table > gets big. Delete ALL your indexes, do your copy's, > and then create your indexes again. > > Good luck. > --- Jie Liang wrote: > > Hi, there, > > > > I tried different ways, include vaccum table , > > ensure index works, it > > still is as slow as ~100rows per minute. > > > > > > Stephan Szabo wrote: > > > > > On Tue, 29 Aug 2000, Jie Liang wrote: > > > > > > > Hi, there, > > > > > > > > 1. use copy ... from '.....'; > > > > 2. write a PL/pgSQL function and pass multiple > > records as an array. > > > > > > > > However, if your table have a foreign key > > constraint, it cannot be speed > > > > up, > > > > > > > > I have same question as you, my table invloving > > 9-13 million rows, I > > > > don't > > > > know how can I add a foreign key them also? > > > > > > I haven't tried it on really large tables, but > > does it turn out faster to > > > use ALTER TABLE ADD CONSTRAINT to add the foreign > > key constraint after the > > > data is loaded and the indexes are created? > > > > -- > > 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 > > > > > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ -- 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