[postgis-users] Understanding speed issues

[Burgholzer,Robert]

I am willing to speculate that this is an issue about your disk, not
Postgres (which does not actually do a delete, it simply marks as no
longer used rows that are updated).  

>From the looks of your query, it is an update on every single row in
your table.  Since this does not rely on any query logic or indexing, I
believe that performance of this query is wholly dependent upon your
disk write speed.  In addition to pure disk write speed, I would say
that perhaps the disk full issue may have more of an effect than you
think.  Even though the linux file system is going to be less prone to
fragmentation in general, this assumption fails when the disk becomes
very full.  Also, due to the physical nature of hard disks (multiple
physical disks make up a single disk) large files are likely to be
scattered over different physical entities, even if they are in
numerically contiguous blocks. 

See the following for a decent description:
http://geekblog.oneandoneis2.org/index.php/2006/08/17/why_doesn_t_linux_
need_defragmenting 

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Paragon Corporation
Sent: Thursday, May 15, 2008 2:55 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Understanding speed issues

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