I am using:
- PostgreSQL 9.6
- pgRouting 2.6.2
- PostGIS 2.5
I have line geometry shapefile of count 700K. When I build the network using pgr_createTopology in the new DB, network created within 1 hrs.
But When I tried to build the network in actual DB which is a size of 20GB, it tooks to days build.
Before I ran the query pgr_createTopology in actual DB, I ran vacuum full & checked any temp file present for db in statistics tab.
This is actual settings taken from pgtune:
max_connections = 300 shared_buffers = 512MB effective_cache_size = 96GB maintenance_work_mem = 2047MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 4 work_mem = 74274kB min_wal_size = 2GB max_wal_size = 8GB
I increased the work_mem to 742740kB Still it took 12 hrs for building 100K records