[postgis-users] Insert performance issues

[Charlton Purvis]

Hi, Nicolas:

> Furthermore, but I may be wrong, I understood that the copy command must
> provide a value for each attribute in the row.

No.  Not necessary.

Give the following a shot.

create table foo (a int, b int);
insert into foo (a) values 1;
insert into foo (b) values 2;
copy foo to '/tmp/foo.copy';

Now look at the contents of /tmp/foo.copy, and you should see how a COPY file works.  Follow that pattern to provide new data for the table.

copy foo from '/tmp/foo.copy';

That will insert that same row back into foo.  COPY still checks constraints and runs triggers, so your sequences should be good to go (although I don't use them as part of our solution).

Charlton

> -----Original Message-----
> From: Nicolas RIBOT [mailto:nri at scot.cnes.fr]
> Sent: Tuesday, October 28, 2003 1:25 PM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] Insert performance issues
> 
> Hello Charlton,
> 
> The table is accessed by several clients and cannot be locked during
> copy.
> Furthermore, but I may be wrong, I understood that the copy command must
> provide a value for each attribute in the row.
> In our table, the identifier is generated by a sequence.
> I thought that only inserts could update sequence values correctly.
> 
> Nicolas
> 
> > -----Message d'origine-----
> > De : Charlton Purvis [mailto:cpurvis at asg.sc.edu]
> > Envoyé : lundi 27 octobre 2003 19:59
> > À : PostGIS Users Discussion
> > Objet : RE: [postgis-users] Insert performance issues
> >
> >
> > Sorry to have joined the thread so late.  Good chance that I've missed
> > the crux of the messages.
> >
> > But I read through the thread, and your initial questioned
> > seemed to be
> > centered around insert performance.  And I am intimate w/ that beast.
> >
> > I'd HIGHLY recommend that you use the pgsql COPY command
> > instead of lots
> > of inserts.  It reduced my insert time exponentially from
> > something like
> > 12 hours to 5 minutes for about 5 million records.  Kind of crazy, eh?
> >
> > That is, if you can lock the table you're updating w/o effecting your
> > production system.  Also, removing all indices on the table before
> > copying helps, too.  Then rebuild and analyze them once your table is
> > populated.
> >
> > Charlton
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users