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

Ries van Twisk postgis at rvt.dds.nl
Fri Dec 2 17:00:00 PST 2005


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
>>    
>>




More information about the postgis-users mailing list