Mercurial > gemma
comparison schema/gemma.sql @ 4449:ce884af9f42f
Fix forecast accuracy values and improve performance
Forecast accuracy should be based only on the most recent predictions.
Generally consider measurements and predictions older than 14 days as
outdated and irrelevant. This also implies a performance gain, since it
saves a full scan of gauge_measurements and the extra index for this
purpose.
Adding an extra subquery level for calculation of the forecast accuracy per
row instead of the function call as a FROM item saves a (nested loop)
join, which is another performance gain.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 20 Sep 2019 13:22:45 +0200 |
parents | 5e38667f740c |
children | 7128741faeb9 0f2c3cb139cc |
comparison
equal
deleted
inserted
replaced
4448:ec207cee13bd | 4449:ce884af9f42f |
---|---|
490 UNIQUE (measure_date, location, staging_done) | 490 UNIQUE (measure_date, location, staging_done) |
491 ) | 491 ) |
492 CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge | 492 CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge |
493 AFTER INSERT OR UPDATE OF location ON gauge_measurements | 493 AFTER INSERT OR UPDATE OF location ON gauge_measurements |
494 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') | 494 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') |
495 -- For fast retrieval of newest measurement per location: | |
496 CREATE INDEX gauge_measurements_location_measure_date_desc | |
497 ON waterway.gauge_measurements (location, measure_date DESC) | |
498 | 495 |
499 CREATE TABLE gauge_predictions ( | 496 CREATE TABLE gauge_predictions ( |
500 location isrs NOT NULL, | 497 location isrs NOT NULL, |
501 measure_date timestamp with time zone NOT NULL, | 498 measure_date timestamp with time zone NOT NULL, |
502 country_code char(2) NOT NULL REFERENCES countries, | 499 country_code char(2) NOT NULL REFERENCES countries, |