Skip to content

Instantly share code, notes, and snippets.

@breunigs

breunigs/statistics_from_vm.txt

Last active March 25, 2023 17:48
Show Gist options
  • Save breunigs/d169e1450a9d645afcf5a02822a78650 to your computer and use it in GitHub Desktop.
Save breunigs/d169e1450a9d645afcf5a02822a78650 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
statistics_from_vm.txt
==> automod_1679733336.log <==
Time (mean ± σ): 273.021 s ± 36.847 s [User: 0.340 s, System: 0.084 s]
Range (min … max): 253.506 s … 328.270 s 4 runs
Warning: Statistical outliers were detected. Consider re-running this benchmark on a quiet PC without any interferences from other programs. It might help to use the '--warmup' or '--prepare' options.
==> automod_1679737696.log <==
Time (mean ± σ): 254.550 s ± 0.897 s [User: 0.338 s, System: 0.071 s]
Range (min … max): 253.881 s … 255.796 s 4 runs
==> automod_1679750914.log <==
Time (mean ± σ): 277.748 s ± 35.383 s [User: 0.349 s, System: 0.076 s]
Range (min … max): 254.582 s … 329.616 s 4 runs
==> automod_1679758074.log <==
Time (mean ± σ): 255.282 s ± 3.615 s [User: 0.352 s, System: 0.065 s]
Range (min … max): 251.441 s … 259.163 s 4 runs
==> master_1679731167.log <==
Time (mean ± σ): 333.336 s ± 2.321 s [User: 0.382 s, System: 0.066 s]
Range (min … max): 331.444 s … 336.377 s 4 runs
==> master_1679738721.log <==
Time (mean ± σ): 335.734 s ± 1.785 s [User: 0.332 s, System: 0.087 s]
Range (min … max): 333.203 s … 337.226 s 4 runs
==> master_1679752031.log <==
Time (mean ± σ): 332.760 s ± 14.579 s [User: 0.333 s, System: 0.071 s]
Range (min … max): 311.064 s … 342.367 s 4 runs
==> master_1679756725.log <==
Time (mean ± σ): 335.778 s ± 2.903 s [User: 0.346 s, System: 0.080 s]
Range (min … max): 333.222 s … 339.856 s 4 runs
Raw
sum_of_4_runs_per_sql_from_notebook.txt
SQL file PR master
create_custom_types 0.3s 0.2s
country_name 0.2s 0.2s
country_osm_grid 5.0s 4.7s
admin_level_type_mapping 0.2s 0.2s
create_custom_columns 0.2s 0.2s
set_tables_unlogged 11.4s 11.2s
create_helper_functions 1.3s 1.1s
set_country_codes 7.5s 6.8s
set_polygon_types 0.5s 0.4s
set_place_ranks 37.5s 32.0s
merge_linked_nodes/merge_nodes_linked… 1.8s 1.2s
set_names_from_tags 47.2s 87.2s
set_linestring_names_from_relations 0.5s 0.5s
delete_unusable_entries 1.8s 1.7s
follow_wikipedia_redirects 0.6s 0.5s
set_geometry_centers 29.4s 31.6s
create_geometry_indexes 7.1s 6.0s
cluster_geometries 18.8s 15.7s
create_parent_polygons 0.6s 0.7s
set_points_parent_ids 1.6s 2.1s
set_polygons_parent_ids 2.6s 3.6s
set_linestrings_parent_ids 17.3s 20.1s
set_housenumbers_parent_ids 75.4s 74.2s
merge_corresponding_linestrings 24.1s 23.8s
set_street_attributes_by_street_relati… 0.3s 0.4s
set_street_names_by_relation_attributes 0.3s 0.3s
normalize_street_names 45.0s 53.0s
set_street_ids_by_street_name 236.7s 298.8s
set_street_attributes_by_nearest_street 11.1s 11.2s
sanitize_housenumbers 1.3s 1.9s
functions 0.2s 0.3s
create_points_view 2.8s 3.5s
create_polygons_view 9.3s 9.0s
create_linestrings_view 30.1s 27.1s
create_housenumbers_view 27.9s 28.1s
create_merged_linestrings_view 48.8s 41.8s
create_geonames_view 0.3s 0.2s
Raw
sum_of_4_runs_per_sql_from_vm.txt
SQL file automod_1679733336.log automod_1679737696.log automod_1679750914.log automod_1679758074.log master_1679731167.log master_1679738721.log master_1679752031.log master_1679756725.log
create_custom_types 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s
country_name 0.3s 0.2s 0.2s 0.3s 0.3s 0.2s 0.2s 0.3s
country_osm_grid 5.7s 5.7s 5.8s 6.0s 5.7s 5.8s 5.8s 5.9s
admin_level_type_mapping 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s
create_custom_columns 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 39.7s 0.2s
set_tables_unlogged 11.5s 11.3s 11.8s 11.7s 11.2s 11.5s 11.9s 11.2s
create_helper_functions 1.1s 1.0s 1.1s 1.1s 1.1s 1.1s 1.1s 1.0s
set_country_codes 8.4s 8.1s 8.6s 8.3s 8.3s 8.3s 8.5s 8.2s
set_polygon_types 0.4s 0.4s 0.4s 0.4s 0.4s 0.4s 0.4s 0.4s
set_place_ranks 40.1s 40.1s 40.8s 40.7s 40.1s 40.0s 40.3s 40.6s
merge_linked_nodes/merge_nodes_linked_by_relation 1.4s 1.4s 1.4s 1.4s 1.4s 1.3s 1.4s 1.4s
set_names_from_tags 45.8s 45.7s 46.7s 47.9s 92.4s 92.9s 94.4s 92.4s
set_linestring_names_from_relations 0.5s 0.5s 0.5s 0.6s 0.5s 0.5s 0.5s 0.5s
delete_unusable_entries 2.2s 2.3s 2.5s 2.5s 2.4s 2.4s 2.1s 2.2s
follow_wikipedia_redirects 0.8s 0.8s 0.8s 0.9s 0.8s 0.8s 0.8s 0.8s
set_geometry_centers 33.1s 33.3s 34.5s 33.7s 33.6s 32.8s 34.2s 33.1s
create_geometry_indexes 7.3s 7.2s 7.2s 7.2s 7.1s 7.2s 7.3s 7.2s
cluster_geometries 19.9s 20.4s 20.3s 20.4s 20.2s 19.9s 20.2s 20.1s
create_parent_polygons 0.8s 1.0s 0.8s 0.9s 1.0s 0.8s 1.0s 0.8s
set_points_parent_ids 1.8s 1.9s 2.1s 2.0s 2.2s 2.0s 2.1s 2.2s
set_polygons_parent_ids 2.5s 2.5s 2.6s 2.6s 3.1s 2.6s 2.6s 2.8s
set_linestrings_parent_ids 18.7s 18.4s 18.9s 18.6s 19.4s 18.9s 18.9s 19.2s
set_housenumbers_parent_ids 84.9s 83.9s 88.7s 84.4s 84.9s 85.2s 86.0s 84.5s
merge_corresponding_linestrings 28.5s 28.5s 28.7s 28.9s 28.6s 28.8s 28.5s 38.7s
set_street_attributes_by_street_relation_members 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s
set_street_names_by_relation_attributes 0.3s 0.3s 0.3s 0.3s 0.3s 0.3s 0.3s 0.3s
normalize_street_names 57.9s 55.7s 56.2s 56.6s 56.6s 56.0s 55.7s 55.0s
set_street_ids_by_street_name 281.0s 211.9s 282.8s 218.0s 485.6s 489.4s 432.3s 487.3s
set_street_attributes_by_nearest_street 12.3s 12.4s 12.7s 12.8s 12.1s 11.8s 12.5s 12.5s
sanitize_housenumbers 2.3s 2.4s 2.3s 2.4s 2.2s 2.2s 2.2s 2.3s
functions 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s
create_points_view 3.2s 2.6s 2.6s 2.9s 2.8s 2.7s 3.7s 2.7s
create_polygons_view 7.9s 6.9s 7.4s 8.1s 8.9s 10.5s 8.7s 8.8s
create_linestrings_view 25.3s 22.4s 22.9s 24.4s 27.8s 28.9s 27.5s 28.0s
create_merged_linestrings_view 41.9s 39.8s 40.8s 41.3s 44.4s 45.9s 44.9s 45.5s
create_housenumbers_view 26.4s 35.0s 34.7s 31.0s 21.1s 22.4s 26.4s 21.1s
create_geonames_view 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s 0.2s
Raw
verifying_the_vm_is_clean.console
root@aard:/tmp/OSMNames# git diff
diff --git a/.env b/.env
index 1dce3a2..80fd83b 100644
--- a/.env
+++ b/.env
@@ -7,10 +7,11 @@ PGPASSWORD=osm
TZ=Europe/Zurich
DATA_DIR=/osmnames/data/
-PBF_FILE_URL=http://planet.osm.org/pbf/planet-latest.osm.pbf
+PBF_FILE=osm_data_source.8.9236,53.1336,10.8897,53.9682.osm.pbf
+#PBF_FILE_URL=http://planet.osm.org/pbf/planet-latest.osm.pbf
# Trying it with a smaller extract might be useful
#PBF_FILE_URL=https://download.openstreetmap.fr/extracts/europe/luxembourg.osm.pbf
# Skips additional information from Wikipedia
-#SKIP_WIKIPEDIA=True
\ No newline at end of file
+SKIP_WIKIPEDIA=True
root@aard:/tmp/OSMNames# git diff master...
diff --git a/osmnames/prepare_data/set_names/set_names_from_tags.sql b/osmnames/prepare_data/set_names/set_names_from_tags.sql
index bbc112b..c2f9340 100644
--- a/osmnames/prepare_data/set_names/set_names_from_tags.sql
+++ b/osmnames/prepare_data/set_names/set_names_from_tags.sql
@@ -34,6 +34,6 @@ END;
$$ LANGUAGE plpgsql IMMUTABLE;
-UPDATE osm_linestring SET (name, alternative_names) = (SELECT * FROM get_name_and_alternative_names(name, all_tags)); --&
-UPDATE osm_polygon SET (name, alternative_names) = (SELECT * FROM get_name_and_alternative_names(name, all_tags)); --&
-UPDATE osm_point SET (name, alternative_names) = (SELECT * FROM get_name_and_alternative_names(name, all_tags)); --&
+UPDATE osm_linestring SET (name, alternative_names) = (SELECT * FROM get_name_and_alternative_names(name, all_tags)) WHERE auto_modulo(id); --&
+UPDATE osm_polygon SET (name, alternative_names) = (SELECT * FROM get_name_and_alternative_names(name, all_tags)) WHERE auto_modulo(id); --&
+UPDATE osm_point SET (name, alternative_names) = (SELECT * FROM get_name_and_alternative_names(name, all_tags)) WHERE auto_modulo(id); --&
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment