From d3ca9dd3f78fce550d601a43cab97bbd7a1ef68a Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 22 Sep 2020 15:51:04 +0200 Subject: [PATCH] remove ST_Covers check when also testing for ST_Intersects Using both is slightly problematic because they have different ways to use the index. Newer versions of Postgis exhibit a query planner issue when both functions appear together. As ST_Intersects includes ST_Covers, simply remove the latter. --- sql/functions/place_triggers.sql | 12 ++++++------ sql/functions/placex_triggers.sql | 4 ++-- sql/functions/utils.sql | 4 ++-- 3 files changed, 10 insertions(+), 10 deletions(-) diff --git a/sql/functions/place_triggers.sql b/sql/functions/place_triggers.sql index b7b51150..eaba12be 100644 --- a/sql/functions/place_triggers.sql +++ b/sql/functions/place_triggers.sql @@ -162,14 +162,14 @@ BEGIN IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN -- re-index points that have moved in / out of the polygon, could be done as a single query but postgres gets the index usage wrong - update placex set indexed_status = 2 where indexed_status = 0 and - (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) - AND NOT (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry)) + update placex set indexed_status = 2 where indexed_status = 0 + AND ST_Intersects(NEW.geometry, placex.geometry) + AND NOT ST_Intersects(existinggeometry, placex.geometry) AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null); - update placex set indexed_status = 2 where indexed_status = 0 and - (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry)) - AND NOT (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) + update placex set indexed_status = 2 where indexed_status = 0 + AND ST_Intersects(existinggeometry, placex.geometry) + AND NOT ST_Intersects(NEW.geometry, placex.geometry) AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null); END IF; diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index d39699f7..76b1bf1a 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -451,9 +451,9 @@ BEGIN -- RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547) - update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) + update placex set indexed_status = 2 where ST_Intersects(NEW.geometry, placex.geometry) AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place')); - update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) + update placex set indexed_status = 2 where ST_Intersects(NEW.geometry, placex.geometry) AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place')); END IF; ELSE diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql index 6697ff97..0cd793ee 100644 --- a/sql/functions/utils.sql +++ b/sql/functions/utils.sql @@ -474,9 +474,9 @@ BEGIN IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP - update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) + update placex set indexed_status = 2 where ST_Intersects(geom, placex.geometry) AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place')); - update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) + update placex set indexed_status = 2 where ST_Intersects(geom, placex.geometry) AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place')); END LOOP; ELSE -- 2.39.5