optimize indexes for interpolation lines

Do not index 'inactive' rows (with startnumber is null) where possible.

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 8ad8a336f5fbfa7c6a0e262aa95df650747dbb43..5db76b7bce1c4eef89ba9d75857eada35fd16ba5 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -135,9 +135,8 @@ BEGIN
     FOR location IN
       SELECT q.parent_place_id
         FROM location_property_osmline q, planet_osm_ways x
     FOR location IN
       SELECT q.parent_place_id
         FROM location_property_osmline q, planet_osm_ways x

-       WHERE q.linegeo && bbox and x.id = q.osm_id
-             and poi_osm_id = any(x.nodes)
-       LIMIT 1
+       WHERE q.linegeo && bbox and startnumber is not null
+             and x.id = q.osm_id and poi_osm_id = any(x.nodes)

     LOOP
       {% if debug %}RAISE WARNING 'Get parent from interpolation: %', location.parent_place_id;{% endif %}
       RETURN location.parent_place_id;
     LOOP
       {% if debug %}RAISE WARNING 'Get parent from interpolation: %', location.parent_place_id;{% endif %}
       RETURN location.parent_place_id;


@@ -653,7 +652,7 @@ BEGIN
           -- roads may cause reparenting for >27 rank places
           update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
           -- reparenting also for OSM Interpolation Lines (and for Tiger?)
           -- roads may cause reparenting for >27 rank places
           update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
           -- reparenting also for OSM Interpolation Lines (and for Tiger?)

-          update location_property_osmline set indexed_status = 2 where indexed_status = 0 and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);
+          update location_property_osmline set indexed_status = 2 where indexed_status = 0 and startnumber is not null and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);

         ELSEIF NEW.rank_search >= 16 THEN
           -- up to rank 16, street-less addresses may need reparenting
           update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or address ? 'place');
         ELSEIF NEW.rank_search >= 16 THEN
           -- up to rank 16, street-less addresses may need reparenting
           update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or address ? 'place');

diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index fed3452402d1cbde20f7a2143f6cd234cb7eef06..ad6753c6a4b03766101ac556040929dae8cc6d79 100644 (file)
--- a/lib-sql/indices.sql
+++ b/lib-sql/indices.sql
@@ -28,7 +28,8 @@ CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPolygon
     AND name is not null AND indexed_status = 0 AND linked_place_id is null;
 
 CREATE INDEX IF NOT EXISTS idx_osmline_parent_place_id
     AND name is not null AND indexed_status = 0 AND linked_place_id is null;
 
 CREATE INDEX IF NOT EXISTS idx_osmline_parent_place_id

-  ON location_property_osmline USING BTREE (parent_place_id) {{db.tablespace.search_index}};
+  ON location_property_osmline USING BTREE (parent_place_id) {{db.tablespace.search_index}}
+  WHERE parent_place_id is not null;

 
 CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id
   ON location_property_osmline USING BTREE (osm_id) {{db.tablespace.search_index}};
 
 CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id
   ON location_property_osmline USING BTREE (osm_id) {{db.tablespace.search_index}};


@@ -66,8 +67,12 @@ CREATE INDEX IF NOT EXISTS idx_postcode_postcode
 
   {% if postgres.has_index_non_key_column %}
     CREATE INDEX IF NOT EXISTS idx_placex_housenumber
 
   {% if postgres.has_index_non_key_column %}
     CREATE INDEX IF NOT EXISTS idx_placex_housenumber

-      ON placex USING btree (parent_place_id) INCLUDE (housenumber) WHERE housenumber is not null;
+      ON placex USING btree (parent_place_id) {{db.tablespace.search_index}}
+      INCLUDE (housenumber)
+      WHERE housenumber is not null;

     CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id_with_hnr
     CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id_with_hnr

-      ON location_property_osmline USING btree(parent_place_id) INCLUDE (startnumber, endnumber);
+      ON location_property_osmline USING btree(parent_place_id) {{db.tablespace.search_index}}
+      INCLUDE (startnumber, endnumber)
+      WHERE startnumber is not null;

   {% endif %}
 {% endif %}
   {% endif %}
 {% endif %}

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 0b35cad2d27db9ebc812f1cdc62fd9de8a095c26..107136617c3a90439aa78127c84137c650f4220c 100644 (file)
--- a/lib-sql/tables.sql
+++ b/lib-sql/tables.sql
@@ -106,7 +106,8 @@ CREATE TABLE location_property_osmline (
   ){{db.tablespace.search_data}};
 CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {{db.tablespace.search_index}};
 CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {{db.tablespace.address_index}};
   ){{db.tablespace.search_data}};
 CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {{db.tablespace.search_index}};
 CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {{db.tablespace.address_index}};

-CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {{db.tablespace.search_index}};
+CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {{db.tablespace.search_index}}
+  WHERE startnumber is not null;

 GRANT SELECT ON location_property_osmline TO "{{config.DATABASE_WEBUSER}}";
 
 drop table IF EXISTS search_name;
 GRANT SELECT ON location_property_osmline TO "{{config.DATABASE_WEBUSER}}";
 
 drop table IF EXISTS search_name;