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

[Greg Williamson]

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
>
>
>