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

[Paul Ramsey]

I could be wrong, but that graph looks fairly linear, and the r-tree  
picksplit algorithm is O(N) (better than the traditional r-tree O 
(N^2) algorithm though slightly less balanced).  COPY (as with  
everything in pgsql) is done in a transactional context, but the  
whole COPY is treated as one transaction, so you are incurring the  
bare minimum transaction overhead.

It is good you are talking with pg-performance on WAL, since I have  
little experience in write performance tuning.  In general terms,  
what is your system doing while this happens?  CPU at 100%?  IO?  How  
is it responding to load, where is there binding?

P

On 2-Dec-05, at 12:51 PM, Rick Schumeyer wrote:

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