Received: from tidal.ipinc.com (tidal.ipinc.com [199.245.188.85]) by hub.org (8.10.1/8.10.1) with ESMTP id e7U1fjC17229 for ; Tue, 29 Aug 2000 21:41:45 -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 SAA54488; Tue, 29 Aug 2000 18:41:37 -0700 (PDT) (envelope-from jliang@ipinc.com) Message-ID: <39AC65E6.851BBC64@ipinc.com> Date: Tue, 29 Aug 2000 18:39:50 -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: pgsql-sql@postgresql.org Subject: Re: Optimizing huge inserts/copy's References: <20000830000452.2110.qmail@web802.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/334 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? Webb Sprague wrote: > Hi all, > > Does anybody have any thoughts on optimizing a huge > insert, involving something like 3 million records all > at once? Should I drop my indices before doing the > copy, and then create them after? I keep a > tab-delimited file as a buffer, copy it, then do it > again about 400 times. Each separate buffer is a few > thousand records. > > We do this at night, so it's not the end of the world > if it takes 8 hours, but I would be very grateful for > some good ideas... > > Thanks > W > > __________________________________________________ > 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