Thread

1. Re: Optimizing huge inserts/copy's

   Webb Sprague <wsprague100@yahoo.com> — 2000-08-30T18:57:17Z

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

2. Re: Optimizing huge inserts/copy's

   Jie Liang <jliang@ipinc.com> — 2000-08-30T19:20:05Z

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

3. Re: Optimizing huge inserts/copy's

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-30T20:06:46Z

   On Wed, 30 Aug 2000, Jie Liang wrote:
   
   > 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 ;
   
   Yeah, the alter table has to check that the constraint is valid.  There
   might be a faster way than the current "scan through table calling
   trigger function" mechanism, although doing most of them starts pulling
   logic for the obeying constraint into multiple places.
   
   
   

4. Re: Optimizing huge inserts/copy's

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-30T20:22:49Z

   Webb Sprague <wsprague100@yahoo.com> writes:
   > 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.
   
   Do you have a lot of equal index keys in the data you're inserting?
   I've recently been swatting some performance problems in the btree
   index code for the case of large numbers of equal keys.
   
   			regards, tom lane