We had problems for awhile with update speed; turned out to be a combination of settings along the lines of Regina's posting below, plus we had the wrong RAID configuration for this application.
Here are a few other tuning links you may find useful: http://www.varlena.com/GeneralBits/Tidbits/perf.html http://edoceo.com/liber/db-postgresql-performance http://www.powerpostgresql.com/PerfList/ http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm and of course Bruce Momjian's thoughts: http://momjian.us/main/writings/pgsql/hw_performance/index.html Paragon Corporation wrote: > 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: [email protected] > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Regards, Chris Hermansen mailto:[EMAIL PROTECTED] tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644 Timberline Natural Resource Group · http://www.timberline.ca 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
