# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1558697726 -7200
# Node ID 7f65790b6bd6c4c626e34cf5f0b9eacc4c72b1a0
# Parent  41349a9c8ce9164a68e85d7dd8c62524ae728ce7
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.

diff -r 41349a9c8ce9 -r 7f65790b6bd6 schema/geoserver_views.sql
--- 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