Mercurial > gemma
changeset 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 | 5890e62e6d52 |
| children | 0e180d651fc6 |
| files | schema/gemma.sql schema/updates/1409/01.add_fm_validity_indexes.sql schema/version.sql |
| diffstat | 3 files changed, 44 insertions(+), 1 deletions(-) [+] |
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,
--- /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; +$$;