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
effective_cache_size=21GB
,shared_buffers=14GB
andmaintenance_work_mem=1GB
. Try alsowork_mem=512MB
. To improve speed on 400GB of data, partitioning is the way to go. You need indexes that fit in RAM.