I created a view of a table containing line segments, where many lines may be part of the same project (and not necessarily adjacent) in PostgresSQL/PostGIS. The segments are joined to another table containing text attributes for that project.
I dissolved these lines in a 2nd view, so that all lines in a project would be one row, using st_union and group by (see below). Both the source table and the view are published as layers in GeoServer. There are many less rows in this view that the original table (700 from 17,000).
The problem is, the dissolved view layer loads only marginally faster (in GeoServer preview and in the webapp), and when I click on this new layer (to see a popup with attribute info) in my webapp using OpenLayers 3, the popup response is much slower than the original table layer - that is, it takes several seconds for the popup to appear when clicking on the dissolve view layer, while it appears quickly when clicking on the original view layer.
Is my dissolved view created correctly?
dissolved view:
CREATE OR REPLACE VIEW projects_dissolve AS
SELECT projects_geo.id,
st_union(projects_geo.geom) AS singlegeom
FROM projects_geo
LEFT JOIN projects ON projects_geo.id = projects.id
GROUP BY projects_geo.id,