Mercurial > gemma
changeset 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 | 009e9400bef0 |
| children | 639bdb17c3f2 |
| files | schema/gemma.sql schema/updates/1004/01.optimize_index-setup.sql schema/version.sql |
| diffstat | 3 files changed, 22 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- 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,
--- /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)