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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Abraham Sent: Wednesday, May 14, 2008 5:30 PM To: postgis-users@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 [EMAIL PROTECTED] _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
