Does anyone know what will be the performance difference between publishing a spatial table directly vs. creating a view of the same table and publishing it in GeoServer?
Does the spatial index on a table still work on a view derived from it?
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
Views are just named queries stored into the database so they will use indexes on the underlying tables just as if you had run the query statement.
You can confirm that the view and the query are assigned the same execution plan by creating the view and then using the EXPLAIN command on the view and the query. The output should be identical.
In Oracle there is something called materialized view. This might be available in some next version of Postgre, but for now you can try a workaround.
If your data does not need to be updated frequently you can create a table that you fill in from results of a view query, and then refresh it periodically by scheduled job.