[postgis-users] Insert performance issues

[Paul Ramsey]

Are you running the query set in a transaction block?

On Monday, October 20, 2003, at 03:10 AM, Nicolas RIBOT wrote:

> Hi,
>
> We have a question concerning insert/update/delete queries on a postgis
> DB.
>
> We use postgis to store a polygon layer (120 000 polygons, some of them
> with more than 160 000 vertices).
>
> We use ArcGIS to modify these polygons.
> Once the edition session is done, we generate an SQL script to
> update/delete/insert polygons into postgis.
> Some times, it takes more than 20 minutes to run the script (1 Mb of 
> SQL
> queries)
> We wondered why performance is so bad.
>
> The configuration:
> Postgresql 7.2, postgis 0.7.2
> Linux Mandrake
> Pentium III 1 Ghz
> 256 Mb ram
> This computer is dedicated to this database, on a 100 Mb LAN
>
> The geographic table is indexed for all the fields involved in the
> queries.
> the geographic column is indexed with a GIST index.
> A vacuum is ran once a week
>
> Should we run a vacuum more often (full, analyse?) ?
> Does it take a long time to maintain indices on the table for each
> insert or delete. ?
> Is it necessary to index all fields involved in the query ?
>
> Thanx for any hint.
>
> Nicolas Ribot
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632