# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1582911307 -3600
# Node ID de190de05f677178ffa58ac32d9113c8f84ba3fd
# Parent  5890e62e6d52eb8b1eca6fc9516f59df8437e633
Add index to speed up fairway marks imports a bit

The new indexes allow index based filtering of historic entries
using the @> operator in the main import SQL statement.

diff -r 5890e62e6d52 -r de190de05f67 schema/gemma.sql
--- a/schema/gemma.sql	Fri Feb 28 18:24:36 2020 +0100
+++ b/schema/gemma.sql	Fri Feb 28 18:35:07 2020 +0100
@@ -899,6 +899,8 @@
                 colour, colpat, condtn, bcnshp, catlam, 0
             ) AS fairway_marks_bcnlat_hydro)
         ))
+    CREATE INDEX fairway_marks_bcnlat_hydro_validity
+        ON fairway_marks_bcnlat_hydro USING GiST (validity)
 
     CREATE TABLE fairway_marks_bcnlat_ienc (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
@@ -913,6 +915,8 @@
                 colour, colpat, condtn, bcnshp, catlam, 0
             ) AS fairway_marks_bcnlat_ienc)
         ))
+    CREATE INDEX fairway_marks_bcnlat_ienc_validity
+        ON fairway_marks_bcnlat_ienc USING GiST (validity)
 
     CREATE TABLE fairway_marks_bcnlat_dirimps (
         fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc,
@@ -940,6 +944,8 @@
                 0, colour, colpat, conrad, marsys, boyshp, catcam
             ) AS fairway_marks_boycar)
         ))
+    CREATE INDEX fairway_marks_boycar_validity
+        ON fairway_marks_boycar USING GiST (validity)
 
     -- Additional attributes for IENC feature BOYLAT
     CREATE TABLE fairway_marks_boylat (
@@ -964,6 +970,8 @@
                 colour, colpat, conrad, marsys, boyshp, catlam, 0
             ) AS fairway_marks_boylat_hydro)
         ))
+    CREATE INDEX fairway_marks_boylat_hydro_validity
+        ON fairway_marks_boylat_hydro USING GiST (validity)
 
     CREATE TABLE fairway_marks_boylat_ienc (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
@@ -978,6 +986,8 @@
                 colour, colpat, conrad, marsys, boyshp, catlam, 0
             ) AS fairway_marks_boylat_ienc)
         ))
+    CREATE INDEX fairway_marks_boylat_ienc_validity
+        ON fairway_marks_boylat_ienc USING GiST (validity)
 
     -- Additional attributes for IENC feature BOYSAW
     CREATE TABLE fairway_marks_boysaw (
@@ -998,6 +1008,8 @@
                 0, colour, colpat, conrad, marsys, boyshp
             ) AS fairway_marks_boysaw)
         ))
+    CREATE INDEX fairway_marks_boysaw_validity
+        ON fairway_marks_boysaw USING GiST (validity)
 
     -- Additional attributes for IENC feature BOYSPP
     CREATE TABLE fairway_marks_boyspp (
@@ -1019,6 +1031,8 @@
                 0, colour, colpat, conrad, marsys, boyshp, catspm
             ) AS fairway_marks_boyspp)
         ))
+    CREATE INDEX fairway_marks_boyspp_validity
+        ON fairway_marks_boyspp USING GiST (validity)
 
     -- Additional attributes for IENC features DAYMAR/daymar
     CREATE TABLE fairway_marks_daymar (
@@ -1041,6 +1055,8 @@
                 colour, colpat, condtn, topshp, 0
             ) AS fairway_marks_daymar_hydro)
         ))
+    CREATE INDEX fairway_marks_daymar_hydro_validity
+        ON fairway_marks_daymar_hydro USING GiST (validity)
 
     CREATE TABLE fairway_marks_daymar_ienc (
         orient double precision,
@@ -1056,6 +1072,8 @@
                 colour, colpat, condtn, topshp, orient, 0
             ) AS fairway_marks_daymar_ienc)
         ))
+    CREATE INDEX fairway_marks_daymar_ienc_validity
+        ON fairway_marks_daymar_ienc USING GiST (validity)
 
     CREATE TABLE fairway_marks_daymar_dirimps (
         fm_daymar_id int REFERENCES fairway_marks_daymar_ienc,
@@ -1092,6 +1110,8 @@
                 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
             ) AS fairway_marks_lights)
         ))
+    CREATE INDEX fairway_marks_lights_validity
+        ON fairway_marks_lights USING GiST (validity)
 
     -- Additional attributes for IENC feature RTPBCN
     CREATE TABLE fairway_marks_rtpbcn (
@@ -1111,6 +1131,8 @@
                 0, condtn, siggrp, catrtb, radwal
             ) AS fairway_marks_rtpbcn)
         ))
+    CREATE INDEX fairway_marks_rtpbcn_validity
+        ON fairway_marks_rtpbcn USING GiST (validity)
 
     -- Additional attributes for IENC feature TOPMAR
     CREATE TABLE fairway_marks_topmar (
@@ -1130,6 +1152,8 @@
                 0, colour, colpat, condtn, topshp
             ) AS fairway_marks_topmar)
         ))
+    CREATE INDEX fairway_marks_topmar_validity
+        ON fairway_marks_topmar USING GiST (validity)
 
     -- Additional attributes for IENC feature NOTMRK
     CREATE TABLE fairway_marks_notmrk (
@@ -1158,6 +1182,8 @@
                 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
             ) AS fairway_marks_notmrk)
         ))
+    CREATE INDEX fairway_marks_notmrk_validity
+        ON fairway_marks_notmrk USING GiST (validity)
 
     CREATE TABLE fairway_marks_notmrk_dirimps (
         fm_notmrk_id int REFERENCES fairway_marks_notmrk,
diff -r 5890e62e6d52 -r de190de05f67 schema/updates/1409/01.add_fm_validity_indexes.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1409/01.add_fm_validity_indexes.sql	Fri Feb 28 18:35:07 2020 +0100
@@ -0,0 +1,17 @@
+DO LANGUAGE plpgsql
+$$
+DECLARE table_suffix varchar;
+BEGIN
+    FOREACH table_suffix IN ARRAY ARRAY[
+        'bcnlat_hydro', 'bcnlat_ienc', 'boycar',
+        'boylat_hydro', 'boylat_ienc', 'boysaw',
+        'boyspp', 'daymar_hydro', 'daymar_ienc',
+        'lights', 'rtpbcn', 'topmar', 'notmrk']
+    LOOP
+        EXECUTE format(
+            'CREATE INDEX fairway_marks_%1$s_validity '
+            'ON waterway.fairway_marks_%1$s USING GiST (validity)',
+            table_suffix);
+    END LOOP;
+END;
+$$;
diff -r 5890e62e6d52 -r de190de05f67 schema/version.sql
--- a/schema/version.sql	Fri Feb 28 18:24:36 2020 +0100
+++ b/schema/version.sql	Fri Feb 28 18:35:07 2020 +0100
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1408);
+INSERT INTO gemma_schema_version(version) VALUES (1409);