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 <pcr...@yahoo.com> An: PostGIS Users Discussion <postgis-users@postgis.refractions.net> CC: robertdbuck...@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 <gwilliamso...@yahoo.com> wrote: >From: Greg Williamson <gwilliamso...@yahoo.com> >Subject: Re: [postgis-users] Are views slow ing down my wms? >To: "PostGIS Users Discussion" <postgis-users@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 <robertdbuck...@yahoo.com> >>To: Post GIS Users Discussion <postgis-users@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@postgis.refractions.net >>http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >_______________________________________________ >postgis-users mailing list >postgis-users@postgis.refractions.net >http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
