From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 10 Oct 2021 12:17:03 +0000 (+0200)
Subject: use SP-GIST index for building index where available
X-Git-Tag: v4.0.0~20^2~1
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/3649487f5e2c70c5964a0af31bb846fe55904ab6?hp=4b007ae74039d26cd0b5cfd755beac9081b3528a

use SP-GIST index for building index where available

Point-in-polygon queries are much faster with a SP-GIST geometry
index, so use that for the index used to check if a housenumber
is inside a building.

Only available with Postgis 3. There is an automatic fallback to
GIST for Postgis 2.
---

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 9732c26c..5008091b 100644
--- a/lib-sql/tables.sql
+++ b/lib-sql/tables.sql
@@ -155,11 +155,11 @@ CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id)
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
 CREATE INDEX idx_placex_geometry_buildings ON placex
-  USING GIST (geometry) {{db.tablespace.search_index}}
+  USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.search_index}}
   WHERE address is not null and rank_search = 30
         and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon');
 CREATE INDEX idx_placex_geometry_placenode ON placex
-  USING GIST (geometry) {{db.tablespace.search_index}}
+  USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.search_index}}
   WHERE osm_type = 'N' and rank_search < 26
         and class = 'place' and type != 'postcode' and linked_place_id is null;
 CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {{db.tablespace.address_index}} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
diff --git a/nominatim/db/sql_preprocessor.py b/nominatim/db/sql_preprocessor.py
index 80b89c57..a1bf5b7f 100644
--- a/nominatim/db/sql_preprocessor.py
+++ b/nominatim/db/sql_preprocessor.py
@@ -46,8 +46,10 @@ def _setup_postgresql_features(conn):
         depend on the database version.
     """
     pg_version = conn.server_version_tuple()
+    postgis_version = conn.postgis_version_tuple()
     return {
-        'has_index_non_key_column': pg_version >= (11, 0, 0)
+        'has_index_non_key_column': pg_version >= (11, 0, 0),
+        'spgist_geom' : 'SPGIST' if postgis_version >= (3, 0) else 'GIST'
     }
 
 class SQLPreprocessor:
diff --git a/test/python/conftest.py b/test/python/conftest.py
index 2fc97726..74d36339 100644
--- a/test/python/conftest.py
+++ b/test/python/conftest.py
@@ -227,7 +227,7 @@ def osm2pgsql_options(temp_db):
                                  main_data='', main_index=''))
 
 @pytest.fixture
-def sql_preprocessor(temp_db_conn, tmp_path, table_factory):
+def sql_preprocessor(temp_db_conn, tmp_path, table_factory, temp_db_with_extensions):
     table_factory('country_name', 'partition INT', ((0, ), (1, ), (2, )))
     cfg = Configuration(None, SRC_DIR.resolve() / 'settings')
     cfg.set_libdirs(module='.', osm2pgsql='.', php=SRC_DIR / 'lib-php',