From 33ba6896a8961cc68c024de110cce23dcc2d4642 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Wed, 21 Sep 2022 14:25:07 +0200 Subject: [PATCH] further split up the big geometry index Adds partial indexes for all geometry queries used during import. A full index is not necessary anymore at that point. Still create the index afterwards for use in queries. Also adds documentation for all indexes on where they are used. --- lib-sql/functions/interpolation.sql | 4 ++- lib-sql/functions/placex_triggers.sql | 7 ++-- lib-sql/indices.sql | 22 ++++++++----- lib-sql/tables.sql | 47 ++++++++++++++++++++++----- 4 files changed, 60 insertions(+), 20 deletions(-) diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index 96a105ae..fb822033 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -52,7 +52,9 @@ BEGIN IF parent_place_id is null THEN FOR location IN SELECT place_id FROM placex - WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26 + WHERE ST_DWithin(geom, placex.geometry, 0.001) + and placex.rank_search = 26 + and placex.osm_type = 'W' -- needed for index selection ORDER BY CASE WHEN ST_GeometryType(geom) = 'ST_Line' THEN (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+ ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+ diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 6c23fd67..b32cf0f5 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -197,6 +197,7 @@ BEGIN SELECT place_id FROM placex WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox)) AND rank_address between 5 and 25 + AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') ORDER BY rank_address desc LOOP RETURN location.place_id; @@ -212,6 +213,7 @@ BEGIN SELECT place_id FROM placex WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox)) AND rank_address between 5 and 25 + AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') ORDER BY rank_address desc LOOP RETURN location.place_id; @@ -275,7 +277,9 @@ BEGIN -- If extratags has a place tag, look for linked nodes by their place type. -- Area and node still have to have the same name. - IF bnd.extratags ? 'place' and bnd_name is not null THEN + IF bnd.extratags ? 'place' and bnd.extratags->'place' != 'postcode' + and bnd_name is not null + THEN FOR linked_placex IN SELECT * FROM placex WHERE (position(lower(name->'name') in bnd_name) > 0 @@ -284,7 +288,6 @@ BEGIN AND placex.osm_type = 'N' AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id) AND placex.rank_search < 26 -- needed to select the right index - AND placex.type != 'postcode' AND ST_Covers(bnd.geometry, placex.geometry) LOOP {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %} diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql index 916d0117..b1396034 100644 --- a/lib-sql/indices.sql +++ b/lib-sql/indices.sql @@ -5,13 +5,6 @@ -- Copyright (C) 2022 by the Nominatim developer community. -- For a full list of authors see the git log. --- The following indicies are only useful during imoprt when all of placex is processed. - -{% if drop %} - DROP INDEX IF EXISTS idx_placex_rank_address_sector; - DROP INDEX IF EXISTS idx_placex_rank_boundaries_sector; -{% endif %} - -- Indices used only during search and update. -- These indices are created only after the indexing process is done. @@ -28,6 +21,9 @@ CREATE INDEX IF NOT EXISTS idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) {{db.tablespace.search_index}} WHERE parent_place_id IS NOT NULL; --- +CREATE INDEX IF NOT EXISTS idx_placex_geometry ON placex + USING GIST (geometry) {{db.tablespace.search_index}}; +--- CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPolygon ON placex USING gist (geometry) {{db.tablespace.search_index}} WHERE St_GeometryType(geometry) in ('ST_Polygon', 'ST_MultiPolygon') @@ -43,9 +39,17 @@ CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id --- CREATE INDEX IF NOT EXISTS idx_postcode_postcode ON location_postcode USING BTREE (postcode) {{db.tablespace.search_index}}; --- Indices only needed for updating. -{% if not drop %} +{% if drop %} +--- + DROP INDEX IF EXISTS idx_placex_geometry_address_area_candidates; + DROP INDEX IF EXISTS idx_placex_geometry_buildings; + DROP INDEX IF EXISTS idx_placex_geometry_lower_rank_ways; + DROP INDEX IF EXISTS idx_placex_wikidata; + DROP INDEX IF EXISTS idx_placex_rank_address_sector; + DROP INDEX IF EXISTS idx_placex_rank_boundaries_sector; +{% else %} +-- Indices only needed for updating. --- CREATE INDEX IF NOT EXISTS idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) {{db.tablespace.address_index}}; diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 0958938a..7ef74349 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -137,7 +137,9 @@ CREATE TABLE place_addressline ( ) {{db.tablespace.search_data}}; CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {{db.tablespace.search_index}}; -drop table if exists placex; +--------- PLACEX - storage for all indexed places ----------------- + +DROP TABLE IF EXISTS placex; CREATE TABLE placex ( place_id BIGINT NOT NULL, parent_place_id BIGINT, @@ -157,25 +159,54 @@ CREATE TABLE placex ( postcode TEXT, centroid GEOMETRY(Geometry, 4326) ) {{db.tablespace.search_data}}; + CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {{db.tablespace.search_index}}; -CREATE INDEX idx_placex_node_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'N'; -CREATE INDEX idx_placex_way_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'W'; -CREATE INDEX idx_placex_relation_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'R'; -CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {{db.tablespace.address_index}} WHERE linked_place_id IS NOT NULL; -CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}}; +{% for osm_type in ('N', 'W', 'R') %} +CREATE INDEX idx_placex_osmid_{{osm_type | lower}} ON placex + USING BTREE (osm_id) {{db.tablespace.search_index}} + WHERE osm_type = '{{osm_type}}'; +{% endfor %} + +-- Usage: - removing linkage status on update +-- - lookup linked places for /details +CREATE INDEX idx_placex_linked_place_id ON placex + USING BTREE (linked_place_id) {{db.tablespace.address_index}} + WHERE linked_place_id IS NOT NULL; + +-- Usage: - check that admin boundaries do not overtake each other rank-wise +-- - check that place node in a admin boundary with the same address level +-- - boundary is not completely contained in a place area +-- - parenting of large-area or unparentable features CREATE INDEX idx_placex_geometry_address_area_candidates ON placex USING gist (geometry) {{db.tablespace.address_index}} WHERE rank_address between 1 and 25 and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon'); + +-- Usage: - POI is within building with housenumber CREATE INDEX idx_placex_geometry_buildings ON placex USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}} WHERE address is not null and rank_search = 30 and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon'); + +-- Usage: - linking of similar named places to boundaries +-- - linking of place nodes with same type to boundaries +-- - lookupPolygon() CREATE INDEX idx_placex_geometry_placenode ON placex USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_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; + and class = 'place' and type != 'postcode'; + +-- Usage: - is node part of a way? +-- - find parent of interpolation spatially +CREATE INDEX idx_placex_geometry_lower_rank_ways ON placex + USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}} + WHERE osm_type = 'W' and rank_search >= 26; + +-- Usage: - linking place nodes by wikidata tag to boundaries +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; -- The following two indexes function as a todo list for indexing. -- 2.45.2