[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    RE: [postgis-users] Insert performance issues
From:       "Charlton Purvis" <cpurvis () asg ! sc ! edu>
Date:       2003-10-28 19:13:24
Message-ID: AE6FF3A533861C41A6571F1D7DDF6F6171A975 () asgnet2 ! asg ! sc ! edu
[Download RAW message or body]

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@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@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@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]