Mercurial > gemma

changeset 4786:3b3cf2083730

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid type cast that prevents index usage Casting to timestamp instead of timestamptz prevented usage of an index on measure_date. To be sure, also pass the location code as isrs instead of type record.
author Tom Gottfried <tom@intevation.de>
date Wed, 23 Oct 2019 15:53:07 +0200
parents 1fef9d8e7eb2
children 3a8ec3c396e0
files pkg/controllers/gauges.go
diffstat 1 files changed, 3 insertions(+), 10 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/gauges.go	Wed Oct 23 15:23:52 2019 +0200
+++ b/pkg/controllers/gauges.go	Wed Oct 23 15:53:07 2019 +0200
@@ -60,16 +60,9 @@
     water_level
   FROM waterway.gauge_predictions
 ) AS gmp
-WHERE
-  location = (
-    $1::char(2),
-    $2::char(3),
-    $3::char(5),
-    $4::char(5),
-    $5::int
-  ) AND
-  measure_date BETWEEN
-    $6::timestamp - '72hours'::interval AND $6::timestamp
+WHERE location = ($1, $2, $3, $4, $5)::isrs
+  AND measure_date BETWEEN
+    $6::timestamptz - '72hours'::interval AND $6::timestamptz
 ORDER BY measure_date, date_issue
 `
 	selectWaterlevelsSQL = `