# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1563472630 -7200
# Node ID 3dc2694557f17f8cd2aac39f6471e5f28b67012c
# Parent  5c7e797347a391568fb50ba4ebc723d10bf3a53f
Speed up time based filtering of gauge measurements and predictions

diff -r 5c7e797347a3 -r 3dc2694557f1 schema/gemma.sql
--- a/schema/gemma.sql	Thu Jul 18 19:49:56 2019 +0200
+++ b/schema/gemma.sql	Thu Jul 18 19:57:10 2019 +0200
@@ -479,6 +479,8 @@
         staging_done boolean NOT NULL DEFAULT false,
         UNIQUE (location, measure_date, staging_done)
     )
+    CREATE INDEX gauge_measurements_measure_date
+        ON waterway.gauge_measurements (measure_date)
 
     CREATE TABLE gauge_predictions (
         location isrs NOT NULL,
@@ -500,6 +502,8 @@
         source_organization varchar NOT NULL, -- "originator" from NtS response
         PRIMARY KEY (location, measure_date, date_issue)
     )
+    CREATE INDEX gauge_predictions_measure_date
+        ON waterway.gauge_predictions (measure_date)
 
     CREATE TABLE waterway_axis (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
diff -r 5c7e797347a3 -r 3dc2694557f1 schema/updates/1003/01.add-indexes.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1003/01.add-indexes.sql	Thu Jul 18 19:57:10 2019 +0200
@@ -0,0 +1,5 @@
+CREATE INDEX gauge_measurements_measure_date
+    ON waterway.gauge_measurements (measure_date);
+
+CREATE INDEX gauge_predictions_measure_date
+    ON waterway.gauge_predictions (measure_date)
diff -r 5c7e797347a3 -r 3dc2694557f1 schema/version.sql
--- a/schema/version.sql	Thu Jul 18 19:49:56 2019 +0200
+++ b/schema/version.sql	Thu Jul 18 19:57:10 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1002);
+INSERT INTO gemma_schema_version(version) VALUES (1003);