Mercurial > gemma
diff schema/geoserver_views.sql @ 3454:7f65790b6bd6
Avoid full sequential scan on measurements for every query
Up to PostgreSQL 11, a CTE is always executed to completion, regardless
of whether the result is actually needed for the calling query. A
sub-select is not 'fenced' that way. Makes some database requests of
GeoServer a lot faster.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 24 May 2019 13:35:26 +0200 |
parents | 030dc48382c9 |
children | ca395be62023 |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Fri May 24 13:34:14 2019 +0200 +++ b/schema/geoserver_views.sql Fri May 24 13:35:26 2019 +0200 @@ -1,9 +1,4 @@ CREATE OR REPLACE VIEW waterway.gauges_geoserver AS - WITH - waterlevel_latest AS ( - SELECT DISTINCT ON (location) location, water_level - FROM waterway.gauge_measurements - ORDER BY location, measure_date DESC) SELECT g.location, isrs_asText(g.location) AS isrs_code, @@ -27,7 +22,9 @@ FROM waterway.gauges_reference_water_levels GROUP BY location, validity) AS r USING (location, validity) - LEFT JOIN waterlevel_latest wl + LEFT JOIN (SELECT DISTINCT ON (location) location, water_level + FROM waterway.gauge_measurements + ORDER BY location, measure_date DESC) AS wl USING (location) LEFT JOIN (SELECT location, max(acc) FILTER (WHERE