Mercurial > gemma

comparison schema/geoserver_views.sql @ 4002:eb11ada33fa7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid full sequential scans on some tables 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 Thu, 18 Jul 2019 17:33:18 +0200
parents db87f34805fb
children 3fcb95a07948
comparison
equal deleted inserted replaced
4001:bf86f9a08733 4002:eb11ada33fa7
92 catdis, 92 catdis,
93 position_code 93 position_code
94 FROM waterway.distance_marks; 94 FROM waterway.distance_marks;
95 95
96 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS 96 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
97 WITH
98 fairway_availability_latest AS (
99 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
100 FROM waterway.fairway_availability
101 ORDER BY bottleneck_id, date_info DESC),
102 sounding_result_latest AS (
103 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max
104 FROM waterway.sounding_results
105 GROUP BY bottleneck_id
106 ORDER BY bottleneck_id DESC)
107 SELECT 97 SELECT
108 b.id, 98 b.id,
109 b.bottleneck_id, 99 b.bottleneck_id,
110 b.objnam, 100 b.objnam,
111 b.nobjnm, 101 b.nobjnm,
130 g.forecast_accuracy_3d, 120 g.forecast_accuracy_3d,
131 g.forecast_accuracy_1d 121 g.forecast_accuracy_1d
132 FROM waterway.bottlenecks b 122 FROM waterway.bottlenecks b
133 LEFT JOIN waterway.gauges_base_view g 123 LEFT JOIN waterway.gauges_base_view g
134 ON b.gauge_location = g.location AND b.gauge_validity = g.validity 124 ON b.gauge_location = g.location AND b.gauge_validity = g.validity
135 LEFT JOIN fairway_availability_latest fal 125 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
126 bottleneck_id, date_info, critical
127 FROM waterway.fairway_availability
128 ORDER BY bottleneck_id, date_info DESC) AS fal
136 ON b.id = fal.bottleneck_id 129 ON b.id = fal.bottleneck_id
137 LEFT JOIN sounding_result_latest srl 130 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
131 bottleneck_id, max(date_info) AS date_max
132 FROM waterway.sounding_results
133 GROUP BY bottleneck_id
134 ORDER BY bottleneck_id DESC) AS srl
138 ON b.bottleneck_id = srl.bottleneck_id 135 ON b.bottleneck_id = srl.bottleneck_id
139 WHERE b.validity @> current_timestamp; 136 WHERE b.validity @> current_timestamp;
140 137
141 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS 138 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
142 SELECT 139 SELECT