Remove unused indexes

[ZeLonewolf]

There are a significant number of indexes in OpenMapTiles that appear to be unused. If these are truly unused, we could save on import and update performance by removing them. Each index makes inserting or updating data slower because the index has to be updated in addition to the table data. After running the integrity script (which performs import and update action), I found that 274 265 85 75 64 52 indices appear to be unused.

I further filtered out this list to:

• include only indexes created in the layer SQL (excludes indexes made my imposm that we don't have control over).
• remove geometry indexes from the transportation layer after a planet build
• remove most geometry indexes from the park layer after a planet build

The SQL query I used is below and a listing of the 75 apparently unused indices is listed below.

select indexrelname from pg_stat_all_indexes where schemaname = 'public' and idx_scan = 0;

ne_10m_ocean_gen_z5_idx
ne_110m_glaciated_areas_gen_z0_idx
ne_110m_glaciated_areas_gen_z1_idx
ne_110m_lakes_gen_z0_idx
ne_110m_lakes_gen_z1_idx
ne_110m_ocean_gen_z0_idx
ne_110m_ocean_gen_z1_idx
ne_110m_rivers_lake_centerlines_gen_z3_idx
ne_50m_ocean_gen_z2_idx
ne_50m_ocean_gen_z3_idx
ne_50m_ocean_gen_z4_idx
ne_50m_rivers_lake_centerlines_gen_z5_idx
ne_50m_urban_areas_gen_z5_idx
osm_aerodrome_label_point_type_partial_idx
osm_border_disp_linestring_gen_z2_idx
osm_border_disp_linestring_gen_z3_idx
osm_border_linestring_adm_geom_idx
osm_border_linestring_gen_z10_idx
osm_border_linestring_gen_z11_idx
osm_border_linestring_gen_z12_idx
osm_border_linestring_gen_z4_idx
osm_border_linestring_gen_z5_idx
osm_border_linestring_gen_z6_idx
osm_border_linestring_gen_z7_idx
osm_border_linestring_gen_z8_idx
osm_border_linestring_gen_z9_idx
osm_building_relation_building_idx
osm_building_relation_member_idx
osm_important_waterway_linestring_gen_z10_geometry_idx
osm_important_waterway_linestring_gen_z11_geometry_idx
osm_important_waterway_linestring_gen_z9_geometry_idx
osm_important_waterway_linestring_geometry_idx
osm_marine_point_rank_idx
osm_ocean_polygon_gen_z11_idx
osm_ocean_polygon_gen_z6_idx
osm_ocean_polygon_gen_z7_idx
osm_ocean_polygon_gen_z8_idx
osm_ocean_polygon_gen_z9_idx
osm_ocean_polygon_union_geom_idx
osm_park_polygon_gen_z4_polygon_geom_idx
osm_transportation_name_network_geometry_idx
osm_water_lakeline_geometry_idx
osm_water_lakeline_pk
osm_water_point_pk
osm_waterway_linestring_waterway_partial_idx
waterway_relation_gen_z6_geometry_idx
waterway_relation_gen_z7_geometry_idx
waterway_relation_gen_z8_geometry_idx
waterway_relation_geometry_idx

Addressed in #1520:

osm_city_point_rank_idx
osm_country_point_rank_idx
osm_state_point_rank_idx

[TomPohys]

Thank you, this is really interesting. Many of them are geometry indexes. Isn't there any slowdown during generating? Every layer_* functions have WHERE geometry && bbox statement like this

openmaptiles/layers/aerodrome_label/aerodrome_label.sql

Line 58 in 81d29ff

WHERE geometry && bbox

Thanks!

[ZeLonewolf]

Good question. I'm going to run a full z14 .mbtiles of a US state, then run the same query, and then intersect that list against this list and see if that reduces the number. The integrity script does generate a tileset so in theory it's executing the whole chain. Perhaps some of these geometry indexes are duplicated in certain cases. I'm not really sure why some of these appear unused either but it seems worth digging into.

[ZeLonewolf]

That reduced the count slightly, to 265 unused indexes.

[ZeLonewolf]

It appears that at least some of these indexes are actually auto-generated by imposm, so those should be filtered out so we're only looking at a list of indexes that we create.

[ZeLonewolf]

I've updated this list to only include indexes that are actually created by the layer SQL (excludes imposm auto-generated indexes)

[ZeLonewolf]

Results from incomplete planet run of the transportation layer(s):

openmaptiles=# select indexrelname, idx_scan from pg_stat_all_indexes where schemaname = 'public' and indexrelname like 'osm_transportation%';
                        indexrelname                         |  idx_scan  
-------------------------------------------------------------+------------
 osm_transportation_name_network_osm_id_idx                  | 1063961701
 osm_transportation_name_network_name_ref_idx                |          0
 osm_transportation_name_network_geometry_idx                |          0
 osm_transportation_merge_linestring_gen_z10_geometry_idx    |        346
 osm_transportation_merge_linestring_gen_z10_id_idx          |          0
 osm_transportation_merge_linestring_gen_z7_geometry_idx     |         17
 osm_transportation_merge_linestring_gen_z7_id_idx           |          0
 osm_transportation_merge_linestring_gen_z6_geometry_idx     |         12
 osm_transportation_merge_linestring_gen_z6_id_idx           |          0
 osm_transportation_merge_linestring_gen_z5_geometry_idx     |         12
 osm_transportation_merge_linestring_gen_z5_id_idx           |          0
 osm_transportation_merge_linestring_gen_z4_geometry_idx     |         11
 osm_transportation_merge_linestring_gen_z4_id_idx           |          0
 osm_transportation_merge_linestring_gen_z11_pkey            |          0
 osm_transportation_merge_linestring_gen_z11_geometry_idx    |       1359
 osm_transportation_merge_linestring_gen_z8_pkey             |          0
 osm_transportation_merge_linestring_gen_z8_geometry_idx     |         29
 osm_transportation_merge_linestring_gen_z9_geometry_idx     |         93
 osm_transportation_merge_linestring_gen_z9_id_idx           |          0
 osm_transportation_name_linestring_name_ref_idx             |          0
 osm_transportation_name_linestring_geometry_idx             |     113476
 osm_transportation_name_linestring_highway_partial_idx      |          4
 osm_transportation_name_linestring_gen1_name_ref_idx        |          0
 osm_transportation_name_linestring_gen1_geometry_idx        |       1798
 osm_transportation_name_linestring_gen1_highway_partial_idx |          4
 osm_transportation_name_linestring_gen2_name_ref_idx        |          0
 osm_transportation_name_linestring_gen2_geometry_idx        |         29
 osm_transportation_name_linestring_gen2_highway_partial_idx |          2
 osm_transportation_name_linestring_gen3_name_ref_idx        |          0
 osm_transportation_name_linestring_gen3_geometry_idx        |         16
 osm_transportation_name_linestring_gen3_highway_partial_idx |          2
 osm_transportation_name_linestring_gen4_name_ref_idx        |          0
 osm_transportation_name_linestring_gen4_geometry_idx        |         12
(33 rows)

[ZeLonewolf]

From overnight planet render of the place layer (aborted before completion):

                      indexrelname                       | idx_scan 
---------------------------------------------------------+----------
 spatial_ref_sys_pkey                                    |        8
 osm_island_point_pkey                                   |        0
 osm_island_point_geom                                   | 23823324
 osm_state_point_pkey                                    |        8
 osm_state_point_geom                                    | 24203798
 osm_state_point_rank_idx                                |        0
 osm_country_point_pkey                                  |      262
 osm_country_point_rank_idx                              |        0
 osm_country_point_geom                                  | 24201920
 osm_island_polygon_pkey                                 |        0
 osm_island_polygon_geom                                 | 24201920
 osm_city_point_pkey                                     |        8
 osm_city_point_geom                                     | 48403840
 osm_city_point_rank_idx                                 |        0
 osm_continent_point_pkey                                |        0
 osm_continent_point_geom                                |        0

[benedikt-brandtner-bikemap]

Hey, with the recent merges of the Update Performance pull requests the index usage has changed significantly so we should also run an update affecting all layers to ensure indexes are not used there.

[benedikt-brandtner-bikemap]

So am have imported austria from september 2022 based on the new master branch and am currently running updates.

will post the results of select indexrelname from pg_stat_all_indexes where schemaname = 'public' and idx_scan = 0; as soon as a few updates have finished!

[ZeLonewolf]

Just removed another handful of indexes from the list that aren't actually made in the SQL layer.

[benedikt-brandtner-bikemap]

Hey sorry was unfortunately occupied with other topics...

Have finally completed an import and update for the mentioned area and attached a json with indexes w/o any accesses.
Am also currently running a new import of worldwide data based upon the current master and will post the resulting json here as well.

empty-openmaptiles-dev-indexes.txt