use constant expressions to select partial indexes in reverse

When expressions are generated with SQLAlchemy, any constants are
replaced with bind parameters. The bind parameters become parameters of
prepared statements. The result is that the query planner tends to
oversee that the partial indexes can be used.

diff --git a/nominatim/api/reverse.py b/nominatim/api/reverse.py
index 00605d45b3841ad5742e25287bb2d956fd1f10ad..0163f935f6352273b2e2d643f39a0c6665b09c6c 100644 (file)
--- a/nominatim/api/reverse.py
+++ b/nominatim/api/reverse.py
@@ -17,6 +17,7 @@ import nominatim.api.results as nres
 from nominatim.api.logging import log
 from nominatim.api.types import AnyPoint, DataLayer, ReverseDetails, GeometryFormat, Bbox
 from nominatim.db.sqlalchemy_types import Geometry
 from nominatim.api.logging import log
 from nominatim.api.types import AnyPoint, DataLayer, ReverseDetails, GeometryFormat, Bbox
 from nominatim.db.sqlalchemy_types import Geometry

+import nominatim.db.sqlalchemy_functions as snfn

 
 # In SQLAlchemy expression which compare with NULL need to be expressed with
 # the equal sign.
 
 # In SQLAlchemy expression which compare with NULL need to be expressed with
 # the equal sign.


@@ -348,13 +349,8 @@ class ReverseGeocoder:
         # later only a minimum of results needs to be checked with ST_Contains.
         inner = sa.select(t, sa.literal(0.0).label('distance'))\
                   .where(t.c.rank_search.between(5, MAX_RANK_PARAM))\
         # later only a minimum of results needs to be checked with ST_Contains.
         inner = sa.select(t, sa.literal(0.0).label('distance'))\
                   .where(t.c.rank_search.between(5, MAX_RANK_PARAM))\

-                  .where(t.c.rank_address.between(5, 25))\
-                  .where(t.c.geometry.is_area())\

                   .where(t.c.geometry.intersects(WKT_PARAM))\
                   .where(t.c.geometry.intersects(WKT_PARAM))\

-                  .where(t.c.name != None)\
-                  .where(t.c.indexed_status == 0)\
-                  .where(t.c.linked_place_id == None)\
-                  .where(t.c.type != 'postcode')\
+                  .where(snfn.select_index_placex_geometry_reverse_lookuppolygon('placex'))\

                   .order_by(sa.desc(t.c.rank_search))\
                   .limit(50)\
                   .subquery('area')
                   .order_by(sa.desc(t.c.rank_search))\
                   .limit(50)\
                   .subquery('area')


@@ -373,14 +369,10 @@ class ReverseGeocoder:
             log().comment('Search for better matching place nodes inside the area')
             inner = sa.select(t,
                               t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
             log().comment('Search for better matching place nodes inside the area')
             inner = sa.select(t,
                               t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\

-                      .where(t.c.osm_type == 'N')\

                       .where(t.c.rank_search > address_row.rank_search)\
                       .where(t.c.rank_search <= MAX_RANK_PARAM)\
                       .where(t.c.rank_search > address_row.rank_search)\
                       .where(t.c.rank_search <= MAX_RANK_PARAM)\

-                      .where(t.c.rank_address.between(5, 25))\
-                      .where(t.c.name != None)\

                       .where(t.c.indexed_status == 0)\
                       .where(t.c.indexed_status == 0)\

-                      .where(t.c.linked_place_id == None)\
-                      .where(t.c.type != 'postcode')\
+                      .where(snfn.select_index_placex_geometry_reverse_lookupplacenode('placex'))\

                       .where(t.c.geometry
                                 .ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
                                 .intersects(WKT_PARAM))\
                       .where(t.c.geometry
                                 .ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
                                 .intersects(WKT_PARAM))\


@@ -476,15 +468,11 @@ class ReverseGeocoder:
 
             inner = sa.select(t,
                               t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
 
             inner = sa.select(t,
                               t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\

-                      .where(t.c.osm_type == 'N')\

                       .where(t.c.rank_search > 4)\
                       .where(t.c.rank_search <= MAX_RANK_PARAM)\
                       .where(t.c.rank_search > 4)\
                       .where(t.c.rank_search <= MAX_RANK_PARAM)\

-                      .where(t.c.rank_address.between(5, 25))\
-                      .where(t.c.name != None)\

                       .where(t.c.indexed_status == 0)\
                       .where(t.c.indexed_status == 0)\

-                      .where(t.c.linked_place_id == None)\
-                      .where(t.c.type != 'postcode')\

                       .where(t.c.country_code.in_(ccodes))\
                       .where(t.c.country_code.in_(ccodes))\

+                      .where(snfn.select_index_placex_geometry_reverse_lookupplacenode('placex'))\

                       .where(t.c.geometry
                                 .ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
                                 .intersects(WKT_PARAM))\
                       .where(t.c.geometry
                                 .ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
                                 .intersects(WKT_PARAM))\

diff --git a/nominatim/db/sqlalchemy_functions.py b/nominatim/db/sqlalchemy_functions.py
new file mode 100644 (file)
index 0000000..27eec79
--- /dev/null
+++ b/nominatim/db/sqlalchemy_functions.py
@@ -0,0 +1,34 @@
+# SPDX-License-Identifier: GPL-3.0-or-later
+#
+# This file is part of Nominatim. (https://nominatim.org)
+#
+# Copyright (C) 2023 by the Nominatim developer community.
+# For a full list of authors see the git log.
+"""
+Custom functions and expressions for SQLAlchemy.
+"""
+
+import sqlalchemy as sa
+
+def select_index_placex_geometry_reverse_lookuppolygon(table: str) -> 'sa.TextClause':
+    """ Create an expression with the necessary conditions over a placex
+        table that the index 'idx_placex_geometry_reverse_lookupPolygon'
+        can be used.
+    """
+    return sa.text(f"ST_GeometryType({table}.geometry) in ('ST_Polygon', 'ST_MultiPolygon')"
+                   f" AND {table}.rank_address between 4 and 25"
+                   f" AND {table}.type != 'postcode'"
+                   f" AND {table}.name is not null"
+                   f" AND {table}.indexed_status = 0"
+                   f" AND {table}.linked_place_id is null")
+
+def select_index_placex_geometry_reverse_lookupplacenode(table: str) -> 'sa.TextClause':
+    """ Create an expression with the necessary conditions over a placex
+        table that the index 'idx_placex_geometry_reverse_lookupPlaceNode'
+        can be used.
+    """
+    return sa.text(f"{table}.rank_address between 4 and 25"
+                   f" AND {table}.type != 'postcode'"
+                   f" AND {table}.name is not null"
+                   f" AND {table}.linked_place_id is null"
+                   f" AND {table}.osm_type = 'N'")