Mercurial > gemma
changeset 4002:eb11ada33fa7
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 | bf86f9a08733 |
| children | 5d4edf268aec |
| files | schema/geoserver_views.sql |
| diffstat | 1 files changed, 9 insertions(+), 12 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Thu Jul 18 15:04:30 2019 +0200 +++ b/schema/geoserver_views.sql Thu Jul 18 17:33:18 2019 +0200 @@ -94,16 +94,6 @@ FROM waterway.distance_marks; CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS - WITH - fairway_availability_latest AS ( - SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical - FROM waterway.fairway_availability - ORDER BY bottleneck_id, date_info DESC), - sounding_result_latest AS ( - SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max - FROM waterway.sounding_results - GROUP BY bottleneck_id - ORDER BY bottleneck_id DESC) SELECT b.id, b.bottleneck_id, @@ -132,9 +122,16 @@ FROM waterway.bottlenecks b LEFT JOIN waterway.gauges_base_view g ON b.gauge_location = g.location AND b.gauge_validity = g.validity - LEFT JOIN fairway_availability_latest fal + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, date_info, critical + FROM waterway.fairway_availability + ORDER BY bottleneck_id, date_info DESC) AS fal ON b.id = fal.bottleneck_id - LEFT JOIN sounding_result_latest srl + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, max(date_info) AS date_max + FROM waterway.sounding_results + GROUP BY bottleneck_id + ORDER BY bottleneck_id DESC) AS srl ON b.bottleneck_id = srl.bottleneck_id WHERE b.validity @> current_timestamp;