From 611b925368aa0a8716ed91fba41c2de8d4917fdf Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sat, 12 Aug 2023 19:14:13 +0200 Subject: [PATCH] use coalsce() instead of indexless postgis functions ST_Intersects is deemed too expensive by the query planer which leads to odd plans when index use is avoided. --- nominatim/db/sqlalchemy_types.py | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py index 9e1f9fce..7b959036 100644 --- a/nominatim/db/sqlalchemy_types.py +++ b/nominatim/db/sqlalchemy_types.py @@ -70,11 +70,12 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg] def ST_DWithin_no_index(self, other: SaColumn, distance: SaColumn) -> SaColumn: - return sa.func._ST_DWithin(self, other, distance, type_=sa.Boolean) + return sa.func.ST_DWithin(sa.func.coalesce(sa.null(), self), + other, distance, type_=sa.Boolean) - def ST_Intersects_no_index(self, other: SaColumn) -> SaColumn: - return sa.func._ST_Intersects(self, other, type_=sa.Boolean) + def ST_Intersects_no_index(self, other: SaColumn) -> 'sa.Operators': + return sa.func.coalesce(sa.null(), self).op('&&')(other) def ST_Distance(self, other: SaColumn) -> SaColumn: -- 2.39.5