2

I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries.

My problem is :

This database is queried a few times a day, but because postgres loads the data in its buffer on demand, my first query on a particular gps point is always slow, and I do only one query per GPS point, so it's always slow (like 60sec slow against 100ms when the data is buffered)

.

What could I do here ?

  • I'm using a A6 Azure instance (4 Cores, 28 GB memory, HDD).
  • Server is Ubuntu 14.04.4 LTS
  • Swapping is enabled
  • There is index on the geometry columns queried.
  • The shared buffer is 10GB
  • work mem is 256MB
  • maintenance work mem is 4GB
| improve this question | |
  • Set effective_cache_size=21GB, shared_buffers=14GB and maintenance_work_mem=1GB. Try also work_mem=512MB. To improve speed on 400GB of data, partitioning is the way to go. You need indexes that fit in RAM. – pietrop Oct 9 '16 at 9:51
  • What do the queries look like? When you say index on the geometry column, do you mean spatial index? (i.e. do you use postgis?) – dbilid Nov 8 '19 at 15:16
  • @dbilid yes I do. – remi bourgarel Nov 8 '19 at 15:56
0

depending on your usage, you could try to partition the data to reduce the scanned data (at 400gb even your indexes are large and heavy to read...) I think it's your best option as working with 400gb tables is heavy even on a very optimized queries... You could also pre-warm the cache with the index data , by issuing some of your queries on frequent gps points or by using something like pg_prewarm

| improve this answer | |
  • I don't have a lot of control on the data structure :( And I use postgre 9.3 so there is no prewarm. Do you thin prewarm would work here with a 400gb and a 10gb shared buffer ? – remi bourgarel Oct 7 '16 at 9:38
  • Partitions are almost transparent to the application - if there's a good clause to partition by it might work with a couple of triggers... 400gb on 10 gb buffers won't work- you might get part of the index inside... as for pg_prewarm , you can just run a couple of queries yourself if the points you get are not totally random. – cohenjo Oct 8 '16 at 18:49

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.