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

[Mark Cave-Ayland]

> From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Rick
Schumeyer
> Sent: 01 December 2005 18:01
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] COPY too slow when geometry column is indexed
>
> I’m running postgresql 8.1.0 with postgis 1.0.4 on a FC3 system, 3Ghz, 1
GB memory.
> 
> I am using COPY to fill a table that contains one postgis geometry column.
> 
> With no geometry index, it takes about 45 seconds to COPY one file.
> 
> If I add a geometry index, this time degrades.  It keeps getting worse as
more
> records are added to the table.  It was up to over three minutes per file
on my 
> most recent test.  If I let it run long enough, the time degrades to 7 or
8 minutes.
> 
> The problem is that each file contains about 5 – 10 minutes of
data. Eventually,
> I want to add the data to the table in “real time”.  So the COPY needs to
take 
>less time than actually generating the data.
>
> Any suggestions for improvement?


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


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk  
http://www.infomapper.com
http://www.swtc.co.uk 

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.