Mercurial > gemma

diff schema/gemma.sql @ 4449:ce884af9f42f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix forecast accuracy values and improve performance Forecast accuracy should be based only on the most recent predictions. Generally consider measurements and predictions older than 14 days as outdated and irrelevant. This also implies a performance gain, since it saves a full scan of gauge_measurements and the extra index for this purpose. Adding an extra subquery level for calculation of the forecast accuracy per row instead of the function call as a FROM item saves a (nested loop) join, which is another performance gain.
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Sep 2019 13:22:45 +0200
parents 5e38667f740c
children 7128741faeb9 0f2c3cb139cc
line wrap: on
line diff
--- a/schema/gemma.sql	Fri Sep 20 12:49:21 2019 +0200
+++ b/schema/gemma.sql	Fri Sep 20 13:22:45 2019 +0200
@@ -492,9 +492,6 @@
     CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
         AFTER INSERT OR UPDATE OF location ON gauge_measurements
         FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')
-    -- For fast retrieval of newest measurement per location:
-    CREATE INDEX gauge_measurements_location_measure_date_desc
-        ON waterway.gauge_measurements (location, measure_date DESC)
 
     CREATE TABLE gauge_predictions (
         location isrs NOT NULL,