[postgis-users] Understanding speed issues

[Chris Hermansen]

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: 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
Regards,

Chris Hermansen         mailto:chris.hermansen at timberline.ca
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