[postgis-users] Understanding speed issues

[Paragon Corporation]

John,

Might be worthwhile to post to pg-general.

Regarding below -  for updating about 2 million or so records - it normally
takes about 10 minutes  on my box running Linux dual quad Xeon 2.2 GZ or
something like that.  Not sure what your specs are like and also depends on
type of disks you have.

Couple of thoughts you may want to check

1) Do you have an index on yearbuilt?  - could be its just doing a table
scan if you don't
2) Which version of PostgreSQL are you running?  As far as config changes -
I think there are quite a few and they vary from version to version and
honestly I can never remember what each does.  Couple that come to mind

A) wal_buffers - I think increasing this helps
B) fsynch - during bulk loads, you may want to consider turning this off.

These are detailed here
http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of John
Abraham
Sent: Wednesday, May 14, 2008 5:30 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Understanding speed issues

Hello postgis friends.  I'm doing a simple update query on a table of 138000
records.  The update query is update Parcels set pecastype=' ', pecassqft=0,
yearbuilt=1993;

It takes over 10000s.  Why might that be ? 

I've been googling a bit, and I understand that because of the transaction
nature of the postgresql system, each of these updates would be an "delete"
and an "insert".  That, I suppose, *might* explain the slowness.  But how to
get around it?  Are there database config setttings or options in the query
that might help?

PostGIS relevance:  It has a GIST index, but I'm not changing the geometry
at all.  But if it's a delete and an insert, maybe it has to keep rebuilding
the GIST index, which could be slow?

Thanks in advance for any help,

PS also my hard drive is almost full; I'm working on that problem but I
can't see how it could be too relevant.

--
John Abraham
jabraham at ucalgary.ca
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users