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

[Rick Schumeyer]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: copy.png
Type: image/png
Size: 13925 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20051202/560b1069/attachment.png>