From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 16 Feb 2020 13:10:22 +0000 (+0100)
Subject: add bbox whereclause to make postgis 3.0 happy
X-Git-Tag: v3.5.0~81
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/2a6e8ad68e80dcda3f747ca47fa149a23d62676c?ds=inline;hp=--cc

add bbox whereclause to make postgis 3.0 happy

Normally ST_Covers() should include a bbox index use,
so adding a bbox where clause is not really necessary.
However, the query planner messes up and uses a parallel
index search with a second index instead of exclusively
running on the geometry index, when the bbox part is
missing.
---

2a6e8ad68e80dcda3f747ca47fa149a23d62676c
diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 9803e815..d6ae8838 100644
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -579,7 +579,10 @@ BEGIN
     IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
        AND NEW.housenumber IS NULL THEN
       FOR location IN
+        -- The additional && condition works around the misguided query
+        -- planner of postgis 3.0.
         SELECT address from placex where ST_Covers(geometry, NEW.centroid)
+            and geometry && NEW.centroid
             and (address ? 'housenumber' or address ? 'street' or address ? 'place')
             and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
             limit 1