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.

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 9803e815e80fdedc024492ee4885aaf82bd01891..d6ae88384f462c41bf58e2d6052b70db62b4cfa8 100644 (file)
--- 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
     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)
         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
             and (address ? 'housenumber' or address ? 'street' or address ? 'place')
             and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
             limit 1