Mercurial > gemma
comparison schema/updates/1004/01.optimize_index-setup.sql @ 4016:9ab7e1056360
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 |
comparison
equal
deleted
inserted
replaced
| 4015:009e9400bef0 | 4016:9ab7e1056360 |
|---|---|
| 1 ALTER TABLE waterway.gauge_measurements | |
| 2 DROP CONSTRAINT gauge_measurements_location_measure_date_staging_done_key; | |
| 3 ALTER TABLE waterway.gauge_measurements | |
| 4 ADD CONSTRAINT gauge_measurements_measure_date_location_staging_done_key | |
| 5 UNIQUE (measure_date, location, staging_done); | |
| 6 DROP INDEX waterway.gauge_measurements_measure_date; | |
| 7 | |
| 8 ALTER TABLE waterway.gauge_predictions | |
| 9 DROP CONSTRAINT gauge_predictions_pkey; | |
| 10 ALTER TABLE waterway.gauge_predictions | |
| 11 ADD CONSTRAINT gauge_predictions_pkey | |
| 12 PRIMARY KEY (measure_date, location, date_issue); | |
| 13 DROP INDEX waterway.gauge_predictions_measure_date; | |
| 14 | |
| 15 CREATE INDEX gauge_measurements_location_measure_date_desc | |
| 16 ON waterway.gauge_measurements (location, measure_date DESC) |