0

I am sorry if the title might sound silly, but I will try to explain the problem and why that title seemed the right to me.

I have an OpenLayers application which display different WMS Image Layers and make query against the relative WFS services coming from GeoServer.

These layers are published from different PostgreSQL views. All the views are generated against a join with a main attribute table and a main geographic table through common feature ids.

The main attribute table is getting big (right now it is 2.5 millions rows), and the response of the application in serving the WMS and other operations like using cql_filter is getting slower.

I cannot create a tiled WMS as my data change frequently and data is served with a filter on dates, so that I would have to create tiles for each single day I have in the views.

Also, I took care of serving the layers with the same coordinate system as my data not to slow down the process due to reprojection.

I tried to create SQL Views in GeoServer to further decrease the size of each view in order to see if this would have improved performance, but I still have slow responses, and I guess (I might be wrong though), that this is due to the data eventually coming from the same big main table under the hood.

So, my idea was to split the main big attribute table into smaller tables, and create my views against these.

Would this be a valuable path or do you have better suggestions?

I can provide more information if needed.

  • Seems like MATERIALIZED VIEWS are the way to go. Just tested them and they seem to have solved the slowness issue! Will post an answer if indeed this will speed everything as I am just seeing right now. – umbe1987 Sep 10 at 8:36
3

There are a number of things that you can do to improve performance.

  1. Don't draw (or query) too many features, so set scale dependencies on your data where possible.

  2. Don't draw things twice (if you can help it) so use the tile cache - it supports TIME dimensions, so if your historical data doesn't change those tiles don't need to either. Also the tiling will be kept in your browser cache while you are panning and zooming the map which is an immediate win in speed terms.

  3. Optimise the way your data is stored and accessed, make sure that there is an index and that it is being used. Using GeoTools-Developer logging will show you the queries being made. You can then run explain analyse on them.

| improve this answer | |
  • Hi Ian, thanks for the great answer. Unfortunately 1 and 2 are out of choice as 1. these layers should be visible from all scales, and for 2. historical data might change now because we are in the phase where we are testing different algorithm to produce the data. Can you tell me a bit about the index? I think I might have indexes, but I am not sure how I should check. Anyway, I found out that MATERIALIZED VIEWS are a great way to improve performance, as they actually are more as db tables than db views and as such they retain less rows and are faster to query. – umbe1987 Sep 10 at 8:49
  • Regarding the index, if you know, are you talking about PostgreSQL indexes, PostGIS spatial indexes, or something related to GeoServer? (sorry but I have to investigate on this, I see there are indexes in my main attribute and geographic tables, but none in the views for instance, which i am about to add now) – umbe1987 Sep 10 at 8:56
  • this link and your answer really helped me: spin.atomicobject.com/2018/04/09/postgres-materialized-views – umbe1987 Sep 10 at 8:59
  • 2
    indexes are a postgresql/postigis issue - you should certainly have a spatial index and then an index on any attribute you query regularly – Ian Turton Sep 10 at 9:04
  • That clarifies my doubts. Thank you for your time and patience ;) – umbe1987 Sep 10 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.