From 860f3559a1967591899643058903ca01dec4b650 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 20 Sep 2022 18:44:37 +0200 Subject: [PATCH] split up large osmid index on placex This doesn't do anything in terms of lookup speeds but the resulting indexes are quite a bit smaller. --- lib-sql/tables.sql | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 2ad5243c..59b1ad5d 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -158,7 +158,9 @@ CREATE TABLE placex ( 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_osmid ON placex USING BTREE (osm_type, osm_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_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}}; -- 2.45.1