Mercurial > gemma
changeset 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 | 41349a9c8ce9 |
| children | 188b55ef814f |
| files | schema/geoserver_views.sql |
| diffstat | 1 files changed, 3 insertions(+), 6 deletions(-) [+] |
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