From 32a74b77a223eceed163d7303665421caa2eee41 Mon Sep 17 00:00:00 2001 From: Itz-Agasta Date: Mon, 27 Jul 2026 16:46:20 +0530 Subject: [PATCH] Use a combined centroid/categories index for category search The plain ltree index on categories is not spatial, so a category search bounded by a viewbox or a search area had to combine it with a second index and AND two large bitmaps together. On a full planet that made the near search take 510ms and the bounded POI search 106ms, against 22ms and 0.7ms for the place_classtype tables they replace. Put the centroid in front of the categories in a combined GiST index instead. Measured on a full planet import, that brings the POI search to 1.2ms and the near search to 74ms. Two details on the definition. The centroid comes first because a GiST index is ineffective when its first column has few distinct values, which is exactly what the lossy signatures of the categories column are; it also builds faster and smaller (12:22 and 5125MB against 19:45 and 5376MB for the other order). The index is not partial because the predicate would otherwise make it unusable for queries on the centroid alone, which cost only 10 to 20% on the category queries but takes a plain centroid lookup from a sequential scan to an index scan. siglen=8 keeps the size manageable. The default for gist__ltree_ops is 28 bytes, which on a planet with full category coverage would put the index well above the 22GB it takes now. The index gets a new name, so the now unused idx_placex_categories is simply dropped from the earlier migration rather than replaced. Since that migration is already released, the new index needs a migration step of its own. --- lib-sql/indices.sql | 5 ++--- src/nominatim_db/tools/migration.py | 21 ++++++++++++++++----- src/nominatim_db/version.py | 2 +- 3 files changed, 19 insertions(+), 9 deletions(-) diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql index 8f462eaf..5a4c1201 100644 --- a/lib-sql/indices.sql +++ b/lib-sql/indices.sql @@ -104,7 +104,6 @@ CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id INCLUDE (startnumber, endnumber) {{db.tablespace.search_index}} WHERE startnumber is not null; --- - CREATE INDEX IF NOT EXISTS idx_placex_categories ON placex - USING GIST(categories gist__ltree_ops) {{db.tablespace.search_index}} - WHERE categories IS NOT NULL; + CREATE INDEX IF NOT EXISTS idx_placex_centroid_categories ON placex + USING GIST(centroid, categories gist__ltree_ops(siglen=8)) {{db.tablespace.search_index}}; {% endif %} diff --git a/src/nominatim_db/tools/migration.py b/src/nominatim_db/tools/migration.py index 511d2cc7..e6dc311a 100644 --- a/src/nominatim_db/tools/migration.py +++ b/src/nominatim_db/tools/migration.py @@ -586,11 +586,6 @@ def backfill_categories(conn: Connection, **_: Any) -> None: """) conn.execute("ALTER TABLE placex ENABLE TRIGGER ALL") - if table_exists(conn, 'search_name'): - conn.execute("""CREATE INDEX IF NOT EXISTS idx_placex_categories - ON placex USING GIST (categories gist__ltree_ops) - WHERE categories IS NOT NULL""") - with conn.cursor() as cur: cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_geometry_placenode_categories ON placex USING SPGIST (geometry) @@ -608,3 +603,19 @@ def backfill_categories(conn: Connection, **_: Any) -> None: AND indexed_status > 0""") conn.execute("ANALYZE placex") + + +@_migration(5, 3, 99, 3) +def add_centroid_categories_index(conn: Connection, **_: Any) -> None: + """ Add the combined centroid/categories index used by category search. + + The index is not partial, so that it can also serve queries on the + centroid alone. A partial index cannot be used for those. + """ + if not table_exists(conn, 'placex') or not table_has_column(conn, 'placex', 'categories'): + return + + if table_exists(conn, 'search_name'): + conn.execute("""CREATE INDEX IF NOT EXISTS idx_placex_centroid_categories + ON placex USING GIST (centroid, + categories gist__ltree_ops(siglen=8))""") diff --git a/src/nominatim_db/version.py b/src/nominatim_db/version.py index 2b839675..3a2c83ad 100644 --- a/src/nominatim_db/version.py +++ b/src/nominatim_db/version.py @@ -55,7 +55,7 @@ def parse_version(version: str) -> NominatimVersion: return NominatimVersion(*[int(x) for x in parts[:2] + parts[2].split('-')]) -NOMINATIM_VERSION = parse_version('5.3.99-2') +NOMINATIM_VERSION = parse_version('5.3.99-3') POSTGRESQL_REQUIRED_VERSION = (13, 0) POSTGIS_REQUIRED_VERSION = (3, 0) -- 2.47.3