Mercurial > gemma

view 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 source

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)