diff schema/gemma.sql @ 4971:de190de05f67 fairway-marks-import

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.
author Tom Gottfried <tom@intevation.de>
date Fri, 28 Feb 2020 18:35:07 +0100
parents 3f704ebad0c5
children 4a816ecf70de
line wrap: on
line diff
--- 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,