Thread

1. Optimizing huge inserts/copy's

   Webb Sprague <wsprague100@yahoo.com> — 2000-08-30T00:04:52Z

   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/
   
   

2. Re: Optimizing huge inserts/copy's

   Jie Liang <jliang@ipinc.com> — 2000-08-30T01:39:50Z

   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
   
   
   
   
   

3. Re: Optimizing huge inserts/copy's

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-30T01:55:42Z

   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?
   
   
   

4. Re: Optimizing huge inserts/copy's

   Jie Liang <jliang@ipinc.com> — 2000-08-30T16:22:22Z

   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
   
   
   
   
   

5. Re: Optimizing huge inserts/copy's

   Zlatko Calusic <zlatko@iskon.hr> — 2000-08-30T22:13:18Z

   Jie Liang <jliang@ipinc.com> writes:
   
   > Hi, there,
   > 
   > I tried different  ways,  include vaccum table ,  ensure index works, it
   > still is as slow as ~100rows per minute.
   > 
   
   PGFSYNC=no in postmaster.init?
   
   Well, this might be Debian Linux specific, pardon me if it is. I have
   just begun playing with Postgres. Still learning, myself.
   
   IOW, disable fsync after every statement and your OS will do much
   better work clustering writes. That means more inserts/sec for you.
   
   In one of my tests I was able to insert at ~1000/sec rate. Then I made
   an experiment, enabled pgfsync _and_ indexes. The inserting speed
   dropped to 10/sec. Very interesting.
   
   Regards,
   -- 
   Zlatko