[postgis-users] COPY too slow when geometry column is indexed

[David Bitner]

I have not implemented it yet, but I am having the same issues with a
dataset that I add several thousand records to a day.  I have been
looking into partitioning my data by month or quarter akin to:
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
A bit more difficult to keep adding the partitions, but It shouldn't
be too difficult to just add a script on a cron that adds a new table
each quarter.

On 12/2/05, Ries van Twisk <postgis at rvt.dds.nl> wrote:
> I am not sure,
>
> maby one of the guru's can confirm...
> for loading large amouth of data it's best to turn indexing off to make
> it load fast. (http://www.postgresql.org/docs/8.1/interactive/populate.html)
> After that you basicly re-index the table. (in a normal situation for a
> one time DB population).
>
> Now since you are loading a lot of records at a time, I wonder if you
> can do this:
> Create a partial index per database population, so current indexes
> doesn't affect indexes of the same table that are
> already in your DB.
> Then after your 200.000 record population you create the index again,
> this should be much faster...
>
> Please Note:
> 1) never tested
> 2) Your data that needs the be populated need to fit into this
> structure, (spatial index etc....)
>
> just a though... maby somebody can comment er have a other idea based on
> this concept..
>
> Ries
>
>
> >I attached a plot of the COPY times, showing how things slow down as
> >more files are added.  Each file contains over 200,000 records.
> >
> >I added a second drive to my system, and moved the WAL to the new drive,
> >per a discussion on the pg-performance list.  It helped a little, but not
> >enough - see the attached plot.
> >
> >Here is the table structure:
> >
> >bed=# \d data
> >      Table "public.data"
> > Column |   Type   | Modifiers
> >--------+----------+-----------
> > rid    | integer  | not null
> > range  | real     | not null
> > x      | real     |
> > y      | real     |
> > z      | real     |
> > bs     | real     |
> > snr    | real     |
> > rvel   | real     |
> > cfar   | smallint |
> > geom   | geometry |
> >Indexes:
> >    "data_pkey" PRIMARY KEY, btree (rid, range)
> >    "data_rid_ix" btree (rid)
> >    "geom_index" gist (geom)
> >Check constraints:
> >    "enforce_dims_geom" CHECK (ndims(geom) = 2)
> >    "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text OR geom
> >IS NULL)
> >    "enforce_srid_geom" CHECK (srid(geom) = 2959)
> >Foreign-key constraints:
> >    "data_rid_fkey" FOREIGN KEY (rid) REFERENCES record(rid)
> >
> >
> >
> >
> >>-----Original Message-----
> >>From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> >>bounces at postgis.refractions.net] On Behalf Of strk at refractions.net
> >>Sent: Friday, December 02, 2005 5:04 AM
> >>To: 'PostGIS Users Discussion'
> >>Subject: Re: [postgis-users] COPY too slow when geometry column is indexed
> >>
> >>On Fri, Dec 02, 2005 at 09:28:17AM -0000, Mark Cave-Ayland wrote:
> >>
> >>
> >>>Hi Rick,
> >>>
> >>>In order to assist with something like this, we need more information
> >>>
> >>>
> >>such
> >>
> >>
> >>>as:
> >>>
> >>>- \dt output of the table you are inserting data into
> >>>- Actual timings for each progressive insert
> >>>- Number of records added during each insert
> >>>
> >>>
> >>Also note he was talking about COPY, not insert.
> >>Maybe transaction management does also influence
> >>that, I suggest to perform the tests also with
> >>small transactions.
> >>
> >>--strk;
> >>_______________________________________________
> >>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
> >>
> >>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>