From 9f64c34f1a579cca7aa144365e5519fce9e47391 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Tue, 25 Jan 2022 11:24:13 +0100
Subject: [PATCH] optimize indexes for interpolation lines

Do not index 'inactive' rows (with startnumber is null) where possible.
---
 lib-sql/functions/placex_triggers.sql |  7 +++----
 lib-sql/indices.sql                   | 11 ++++++++---
 lib-sql/tables.sql                    |  3 ++-
 3 files changed, 13 insertions(+), 8 deletions(-)

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 8ad8a336..5db76b7b 100644
--- 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
-       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;
@@ -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?)
-          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');
diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index fed34524..ad6753c6 100644
--- 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
-  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}};
@@ -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
-      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
-      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 %}
diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 0b35cad2..10713661 100644
--- 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}};
-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;
-- 
2.39.5