Mercurial > gemma

diff schema/updates/1409/01.add_fm_validity_indexes.sql @ 4971:de190de05f67 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
children
line wrap: on
line diff
--- /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;
+$$;