Speeding up GeoServer response - splitting the main PostgreSQL table into multiple sub tables instead of using views would help?

Question

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.

Answer 1

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.