Mercurial > gemma

diff schema/updates/1004/01.optimize_index-setup.sql @ 4016:9ab7e1056360

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Tom Gottfried <tom@intevation.de>
date Fri, 19 Jul 2019 14:30:14 +0200
parents
children
line wrap: on
line diff
--- /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)