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

  • 1
    Have you used an index on the required columns (ids and geometry)? – root676 Sep 2 at 10:07
  • yes, tried with that – Rajprabhu Arulnathan Sep 2 at 12:30
  • pgr_createTopology would also create these indexes for you. However, that function does a non-trivial job, and your target dataset easily contains two orders of magnitude more records (that's a linear 100 hours build time...). I don't think there is much chance to get a significant improvement with this setup. – geozelot Sep 2 at 20:14
  • I am not looking significant improvement, I want to know the reason for network building is very slow with its origin DB size of 20GB with other data when comparing with new DB with the only network data – Rajprabhu Arulnathan Sep 3 at 11:28
  • routing network created within half an hour in new DB & existing source in the same server – Rajprabhu Arulnathan Sep 3 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.