[Mapbender-commits] r9501 - trunk/mapbender/resources/db

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2016-05-27 03:27:05 -0700 (Fri, 27 May 2016)
New Revision: 9501

Added:
   trunk/mapbender/resources/db/materialize_wfs_view.sql
Modified:
   trunk/mapbender/resources/db/materialize_dataset_view.sql
Log:
Enhance search with materialized views

Modified: trunk/mapbender/resources/db/materialize_dataset_view.sql
===================================================================
--- trunk/mapbender/resources/db/materialize_dataset_view.sql	2016-05-27 09:38:29 UTC (rev 9500)
+++ trunk/mapbender/resources/db/materialize_dataset_view.sql	2016-05-27 10:27:05 UTC (rev 9501)
@@ -48,5 +48,48 @@
   ON dataset_search_table
   USING btree
   (dataset_id);
+-- Index: idx_wst_dataset_md_inspire_cats
 
+-- DROP INDEX idx_wst_dataset_md_inspire_cats;
 
+CREATE INDEX idx_wst_dataset_md_inspire_cats
+  ON dataset_search_table
+  USING btree
+  (md_inspire_cats);
+
+-- Index: idx_wst_dataset_md_custom_cats
+
+-- DROP INDEX idx_wst_dataset_md_custom_cats;
+
+CREATE INDEX idx_wst_dataset_md_custom_cats
+  ON dataset_search_table
+  USING btree
+  (md_custom_cats);
+
+-- Index: idx_wst_dataset_timebegin
+
+-- DROP INDEX idx_wst_dataset_timebegin;
+
+CREATE INDEX idx_wst_dataset_timebegin
+  ON dataset_search_table
+  USING btree
+  (timebegin);
+
+-- Index: idx_wst_dataset_timeend
+
+-- DROP INDEX idx_wst_dataset_timeend;
+
+CREATE INDEX idx_wst_dataset_timeend
+  ON dataset_search_table
+  USING btree
+  (timeend);
+
+-- Index: idx_wst_dataset_department
+
+-- DROP INDEX idx_wst_dataset_department;
+
+CREATE INDEX idx_wst_dataset_timestamp
+  ON dataset_search_table
+  USING btree
+  (dataset_timestamp);
+

Added: trunk/mapbender/resources/db/materialize_wfs_view.sql
===================================================================
--- trunk/mapbender/resources/db/materialize_wfs_view.sql	                        (rev 0)
+++ trunk/mapbender/resources/db/materialize_wfs_view.sql	2016-05-27 10:27:05 UTC (rev 9501)
@@ -0,0 +1,57 @@
+
+DROP TABLE IF EXISTS wfs_search_table_tmp;
+select * into wfs_search_table_tmp from search_wfs_view;
+
+
+DROP TABLE IF EXISTS wfs_search_table;
+--TODO
+ALTER TABLE wfs_search_table_tmp RENAME TO  wfs_search_table;
+
+-- Index: gist_wst_the_geom
+
+-- DROP INDEX gist_wst_featuretype_the_geom;
+
+CREATE INDEX gist_wst_featuretype_the_geom
+  ON wfs_search_table
+  USING gist
+  (the_geom);
+
+-- Index: idx_wst_featuretype_department
+
+-- DROP INDEX idx_wst_featuretype_department;
+
+CREATE INDEX idx_wst_featuretype_department
+  ON wfs_search_table
+  USING btree
+  (department);
+
+-- Index: idx_wst_featuretype_id
+
+-- DROP INDEX idx_wst_featuretype_id;
+
+CREATE INDEX idx_wst_featuretype_id
+  ON wfs_search_table
+  USING btree
+  (featuretype_id);
+
+-- Index: idx_wst_featuretype_searchtext
+
+-- DROP INDEX idx_wst_featuretype_searchtext;
+
+CREATE INDEX idx_wst_featuretype_searchtext
+  ON wfs_search_table
+  USING btree
+  (searchtext);
+
+-- Index: idx_wst_wfs_timestamp
+
+-- DROP INDEX idx_wst_wfs_timestamp;
+
+CREATE INDEX idx_wst_wfs_timestamp
+  ON wfs_search_table
+  USING btree
+  (wfs_timestamp);
+--vacuum analyze;
+--VACUUM ANALYZE wfs_search_table;
+
+