[postgis-users] Are views slow ing down my wms?

[Robert Buckley]

Thanks for all your replies and helpful comments. I will be able to do some good analysis next week on the recommendations posted,

yours,

rob


________________________________
 Von: Brent Wood <pcreso at yahoo.com>
An: PostGIS Users Discussion <postgis-users at postgis.refractions.net> 
CC: robertdbuckley at yahoo.com 
Gesendet: 6:19 Samstag, 14.Juli 2012
Betreff: Re: [postgis-users] Are views slow ing down my wms?
 

There are a couple of possibilities:

Postgis is slowing down for some reason, or Geoserver is.

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. 

Tomcat/Geoserver are perhaps more likely culprits in my experience.

You haven't said how much they are slowing down, or how much memory, cpu, etc are available or being used.

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. 

Brent Wood


--- On Sat, 7/14/12, Greg Williamson <gwilliamson39 at yahoo.com> wrote:


>From: Greg Williamson <gwilliamson39 at yahoo.com>
>Subject: Re: [postgis-users] Are views slow ing down my wms?
>To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>Date: Saturday, July 14, 2012, 1:43 PM
>
>
>Robert --
>
>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.
>
>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.
>
>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.
>
>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.
>
>Greg Williamson
>
>
>
>>________________________________
>> From: Robert Buckley <robertdbuckley at yahoo.com>
>>To: Post GIS Users Discussion <postgis-users at postgis.refractions.net> 
>>Sent: Wednesday, July 11, 2012 10:30 PM
>>Subject:
 [postgis-users] Are views slow ing down my wms?
>> 
>>
>>Hi,
>>
>>
>>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.
>>
>>
>>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?
>>
>>
>>Should I in hindsight have split the table into separate tables instead of creating views?
>>
>>
>>thanks for any help,
>>
>>
>>Robert
>>
>>
>>_______________________________________________
>>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
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120714/ebd363aa/attachment.html>