# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1563539414 -7200
# Node ID 9ab7e10563609e094f648579ed43d24c95140032
# Parent  009e9400bef031f0689e9cbc2c6bba0cf09535af
Optimize index setup a bit

Optimize column order in multi-column indexes created automatically
for constraints in order to match access patterns better. This
makes the extra indexes on measure_date obsolete.
Add a further index for fast retrieval of newest measurement
per location, which is currently needed in some VIEWs for GeoServer.

diff -r 009e9400bef0 -r 9ab7e1056360 schema/gemma.sql
--- a/schema/gemma.sql	Fri Jul 19 14:06:31 2019 +0200
+++ b/schema/gemma.sql	Fri Jul 19 14:30:14 2019 +0200
@@ -477,10 +477,11 @@
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL, -- "originator" from NtS response
         staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE (location, measure_date, staging_done)
+        UNIQUE (measure_date, location, staging_done)
     )
-    CREATE INDEX gauge_measurements_measure_date
-        ON waterway.gauge_measurements (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,
@@ -500,10 +501,8 @@
             CHECK (conf_interval @> CAST(water_level AS numeric)),
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL, -- "originator" from NtS response
-        PRIMARY KEY (location, measure_date, date_issue)
+        PRIMARY KEY (measure_date, location, 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 009e9400bef0 -r 9ab7e1056360 schema/updates/1004/01.optimize_index-setup.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1004/01.optimize_index-setup.sql	Fri Jul 19 14:30:14 2019 +0200
@@ -0,0 +1,16 @@
+ALTER TABLE waterway.gauge_measurements
+    DROP CONSTRAINT gauge_measurements_location_measure_date_staging_done_key;
+ALTER TABLE waterway.gauge_measurements
+    ADD CONSTRAINT gauge_measurements_measure_date_location_staging_done_key
+    UNIQUE (measure_date, location, staging_done);
+DROP INDEX waterway.gauge_measurements_measure_date;
+
+ALTER TABLE waterway.gauge_predictions
+    DROP CONSTRAINT gauge_predictions_pkey;
+ALTER TABLE waterway.gauge_predictions
+    ADD CONSTRAINT gauge_predictions_pkey
+    PRIMARY KEY (measure_date, location, date_issue);
+DROP INDEX waterway.gauge_predictions_measure_date;
+
+CREATE INDEX gauge_measurements_location_measure_date_desc
+    ON waterway.gauge_measurements (location, measure_date DESC)
diff -r 009e9400bef0 -r 9ab7e1056360 schema/version.sql
--- a/schema/version.sql	Fri Jul 19 14:06:31 2019 +0200
+++ b/schema/version.sql	Fri Jul 19 14:30:14 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1003);
+INSERT INTO gemma_schema_version(version) VALUES (1004);