<html><body><div style="color:#000; background-color:#fff; font-family:arial, helvetica, sans-serif;font-size:10pt"><div><span>Thanks for all your replies and helpful comments. I will be able to do some good analysis next week on the recommendations posted,</span></div><div><span><br></span></div><div><span>yours,</span></div><div><span><br></span></div><div><span>rob</span></div><div><br></div> <div style="font-family: arial, helvetica, sans-serif; font-size: 10pt; "> <div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt; "> <div dir="ltr"> <font size="2" face="Arial"> <hr size="1"> <b><span style="font-weight:bold;">Von:</span></b> Brent Wood <pcreso@yahoo.com><br> <b><span style="font-weight: bold;">An:</span></b> PostGIS Users Discussion <postgis-users@postgis.refractions.net> <br><b><span style="font-weight: bold;">CC:</span></b> robertdbuckley@yahoo.com <br> <b><span style="font-weight:
bold;">Gesendet:</span></b> 6:19 Samstag, 14.Juli 2012<br> <b><span style="font-weight: bold;">Betreff:</span></b> Re: [postgis-users] Are views slow ing down my wms?<br> </font> </div> <br><div id="yiv226701638"><table cellspacing="0" cellpadding="0" border="0"><tbody><tr><td valign="top" style="font:inherit;">There are a couple of possibilities:<br><br>Postgis is slowing down for some reason, or Geoserver is.<br><br>If you can run a query against the view you can check Postgis performance as Greg suggests. With only 10,000 or so rows in 3 tables it is not likely that Postgis will be slowing down much due to the views, or indexes. <br><br>Tomcat/Geoserver are perhaps more likely culprits in my experience.<br><br>You haven't said how much they are slowing down, or how much memory, cpu, etc are available or being used.<br><br>Try running top in a terminal while it is being used to see which processes are loading up the system, although this will be of
limited use with tomcat. <br><br>Brent Wood<br><br><br>--- On <b>Sat, 7/14/12, Greg Williamson <i><gwilliamson39@yahoo.com></i></b> wrote:<br><blockquote style="border-left:2px solid rgb(16, 16, 255);
margin-left:5px;padding-left:5px;"><br>From: Greg Williamson <gwilliamson39@yahoo.com><br>Subject: Re: [postgis-users] Are views slow ing down my wms?<br>To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>Date: Saturday, July 14, 2012, 1:43 PM<br><br><div class="yiv226701638plainMail">Robert --<br><br>It is possible that you are missing an index on something -- in a smaller table the query planner will ignore an index and do sequential reads because it is a faster strategy; missing an index won't effect a small table but as the # of rows grows the sequential scan becomes much more painful.<br><br>Might also be a limited amount of memory (work_mem setting) dedicated to searches -- the default works ok for small sorts but at the number of rows being considered grows so too does the memory required. Once postgres reaches the limit set (which is per search and one query may have more than one sort) it dumps the whole
search to disk, which
can be much slower than the all in RAM version.<br><br>You might try running the SQL in the view inside an "EXPLAIN ANALYZE " command and see if there are oddities -- wildly divergent row counts, sequential scans on large tables, etc.<br><br>You might also post the version of postgres (e.g. 9.1.x) as that may be useful information in seeing what is going on. Also, changes from default postgres settings and information about RAM might help shed some light.<br><br>Greg Williamson<br><br><br><br>>________________________________<br>> From: Robert Buckley <<a rel="nofollow" href="">robertdbuckley@yahoo.com</a>><br>>To: Post GIS Users Discussion <<a rel="nofollow" href="">postgis-users@postgis.refractions.net</a>> <br>>Sent: Wednesday, July 11, 2012 10:30 PM<br>>Subject:
[postgis-users] Are views slow ing down my wms?<br>> <br>><br>>Hi,<br>><br>><br>>I have created a database structure which has 3 main postgis tables (1 for lines,polygones, and points) which each contain between 5-10,000 features. These tables then feed views which split the geometries into understandable groups which are then served through geoserver in Tomcat6, linux-Ubuntu 10.04.<br>><br>><br>>I have noticed that the performance of the wms is getting considerably worse. Is this to do with the views or simply the amount of data being served?<br>><br>><br>>Should I in hindsight have split the table into separate tables instead of creating views?<br>><br>><br>>thanks for any help,<br>><br>><br>>Robert<br>><br>><br>>_______________________________________________<br>>postgis-users mailing list<br>><a rel="nofollow" href="">postgis-users@postgis.refractions.net</a><br>><a
rel="nofollow" target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>><br>_______________________________________________<br>postgis-users mailing list<br><a rel="nofollow" href="">postgis-users@postgis.refractions.net</a><br><a rel="nofollow" target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></tbody></table></div><br><br> </div> </div> </div></body></html>