Nominatim geocoding slow performance on planet dataset

[ashley-mort]

We have the entire planet dataset loaded and queries are taking 2-5minutes. I have researched all the nominatim repos issues and comments but I think I still need help.

We are running nominatim-docker 4.2 with postgres 14.4. Any advice is greatly appreciated!

I ran an ANALYZE on the Nominatim DB with no improvement. I then did a REINDEX which improved the query time by maybe 20% (5min to 3min). All nominatim tables appear to be using an index.
The reindex was:
$ export PGOPTIONS="-P"
$ psql nominatim
nominatim=> REINDEX DATABASE nominatim;

The following postgres settings have been modified and tested without much change:

primary:
extendedConfiguration: |
jit = off
shared_buffers = 2GB
maintenance_work_mem = 10GB
autovacuum_work_mem = 2GB
work_mem = 50MB
effective_cache_size = 24GB
#synchronous_commit = off
#max_wal_size = 1GB
#checkpoint_timeout = 10min
#checkpoint_completion_target = 0.9
#fsync = off
#full_page_writes = off

Below is an EXPLAIN ANALYZE of the SQL query I saw from running:
curl -k "https://nominatim.cluster.local/?q=oakland"

nominatim=# EXPLAIN ANALYZE SELECT
nominatim-# place_id,
nominatim-# address_rank,
nominatim-# (SELECT COUNT(*)
nominatim(# FROM (SELECT unnest(ARRAY[211771]) AS id
nominatim(# INTERSECT
nominatim(# SELECT unnest(nameaddress_vector) AS name_id) AS s) AS exactmatch
nominatim-# FROM
nominatim-# search_name
nominatim-# WHERE
nominatim-# name_vector @> ARRAY[211771]
nominatim-# ORDER BY
nominatim-# (CASE
nominatim(# WHEN importance = 0 OR importance IS NULL THEN 0.75001 - (search_rank::float / 40)
nominatim(# ELSE importance
nominatim(# END) DESC,
nominatim-# exactmatch DESC
nominatim-# LIMIT 20;
QUERY PLAN

---

---

Limit (cost=10912961.95..10912962.00 rows=20 width=26) (actual time=102470.844..102484.958 rows=20 loops=1)
-> Sort (cost=10912961.95..10913009.44 rows=18998 width=26) (actual time=102470.841..102484.954 rows=20 loops=1)
Sort Key: (CASE WHEN ((search_name.importance = '0'::double precision) OR (search_name.importance IS NULL)) THEN ('0.75001'::double precision - ((search_name.search_ra
nk)::double precision / '40'::double precision)) ELSE search_name.importance END) DESC, ((SubPlan 1)) DESC
Sort Method: top-N heapsort Memory: 27kB
-> Gather (cost=1000.00..10912456.42 rows=18998 width=26) (actual time=16132.990..102484.792 rows=175 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on search_name (cost=0.00..10903572.25 rows=7916 width=615) (actual time=15250.238..102458.591 rows=58 loops=3)
Filter: (name_vector @> '{211771}'::integer[])
Rows Removed by Filter: 37995350
SubPlan 1
-> Aggregate (cost=0.30..0.31 rows=1 width=8) (actual time=0.035..0.035 rows=1 loops=175)
-> Subquery Scan on s (cost=0.00..0.29 rows=1 width=0) (actual time=0.034..0.034 rows=0 loops=175)
-> HashSetOp Intersect (cost=0.00..0.28 rows=1 width=8) (actual time=0.034..0.034 rows=0 loops=175)
-> Append (cost=0.00..0.26 rows=11 width=8) (actual time=0.002..0.026 rows=115 loops=175)
-> Subquery Scan on "SELECT 1" (cost=0.00..0.03 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=175)
-> ProjectSet (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=175)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=175)
-> Subquery Scan on "SELECT 2" (cost=0.00..0.17 rows=10 width=8) (actual time=0.001..0.016 rows=114 loops=175)
-> ProjectSet (cost=0.00..0.07 rows=10 width=4) (actual time=0.000..0.008 rows=114 loops=175)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=175)
Planning Time: 3.490 ms
Execution Time: 102485.069 ms
(23 rows)

Server disk performance is:
I have no name!@nominatim-postgresql-0:/tmp$ dd if=/dev/zero of=/tmp/test1.img bs=1G count=1 oflag=dsync
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB, 1.0 GiB) copied, 2.14096 s, 502 MB/s

I have no name!@nominatim-postgresql-0:/tmp$ time dd if=/tmp/test1.img of=/dev/null bs=8k
131072+0 records in
131072+0 records out
1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.284603 s, 3.8 GB/s

[lonvia]

Make sure you have parallel execution disabled (max_parallel_workers_per_gather = 0).

Other than that, the usual advice is to update to the latest version 4.5. You can do that without a reimport, just follow the migration hints in the guide Newer PostgreSQL versions are faster as well but you will need to reimport to get to those.

[ashley-mort]

There were a lot of missing indexes, found the cli troubleshooting command on a blog and it returned this:

root@nominatim-6d96b45d5f-4pbpq:/nominatim# /usr/local/bin/nominatim admin --check-database
2025-01-28 17:29:44: Using project directory: /nominatim
2025-01-28 17:29:44: Checking database
Checking database connection ... OK
Checking for placex table ... OK
Checking for placex content ... OK
Checking that tokenizer works ... OK
Checking for wikipedia/wikidata data ... WARNING

Wikipedia/Wikidata importance tables missing.
Quality of search results may be degraded. Reverse geocoding is unaffected.
See https://nominatim.org/release-docs/latest/admin/Import/#wikipediawikidata-rankings

Checking indexing status ... OK
Checking that database indexes are complete ... Failed
The following indexes are missing:
idx_place_addressline_address_place_id
idx_placex_rank_search
idx_placex_rank_address
idx_placex_parent_place_id
idx_placex_geometry_reverse_lookuppolygon
idx_search_name_nameaddress_vector
idx_search_name_name_vector
idx_search_name_centroid
idx_placex_housenumber
idx_osmline_parent_osm_id_with_hnr
idx_place_osm_unique

       Rerun the index creation with:   nominatim import --continue db-postprocess

Checking that all database indexes are valid ... OK
Checking TIGER external data table. ... not applicable

So ran the recommended commands: nominatim import --continue db-postprocess

time curl -k "https://nominatim.cluster.local/?q=oakland&format=geojson"
{"type":"FeatureCollection","licence":"Data © OpenStreetMap contributors, ODbL 1.0. [https://osm.org/copyright","features":{"type":"Feature","properties":{"place_id":326702553,"osm_type":"relation","osm_id":359761,"display_name":"Oakland County, Michigan, United States","place_rank":12,"category":"boundary","type":"administrative","importance":0.55001},"bbox":[-83.689438,42.4311835,-83.0833772,42.888647],"geometry":{"type":"Point","coordinates":[-83.3804439,42.6618842]}},{"type":"Feature","properties":{"place_id":328361599,"osm_type":"relation","osm_id":11564939,"display_name":"Town of Oakland, Jefferson County, Wisconsin, United States","place_rank":14,"category":"boundary","type":"administrative","importance":0.50001},"bbox":[-89.0130992,42.9349163,-88.894201,43.0235763],"geometry":{"type":"Point","coordinates":[-88.9556648,42.9766694]}},{"type":"Feature","properties":{"place_id":326645771,"osm_type":"relation","osm_id":133666,"display_name":"Oakland, Garrett County, Maryland, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-79.42436,39.3999619,-79.3777488,39.4436557],"geometry":{"type":"Point","coordinates":[-79.4062658,39.4099097]}},{"type":"Feature","properties":{"place_id":326657807,"osm_type":"relation","osm_id":170661,"display_name":"Oakland, Bergen County, New Jersey, 07436, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-74.272484,40.992303,-74.201773,41.06382],"geometry":{"type":"Point","coordinates":[-74.2430988,41.0220483]}},{"type":"Feature","properties":{"place_id":326648728,"osm_type":"relation","osm_id":139994,"display_name":"Oakland, Saint Louis County, Missouri, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-90.391542,38.567932,-90.377238,38.582854],"geometry":{"type":"Point","coordinates":[-90.3856733,38.5764418]}},{"type":"Feature","properties":{"place_id":326667757,"osm_type":"relation","osm_id":192183,"display_name":"Oakland, Sumter County, South Carolina, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-80.506032,33.979842,-80.488239,33.993698],"geometry":{"type":"Point","coordinates":[-80.49815981420217,33.986850000000004]}},{"type":"Feature","properties":{"place_id":326644206,"osm_type":"relation","osm_id":130720,"display_name":"Oakland, Warren County, Kentucky, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-86.265486,37.031572,-86.239984,37.061733],"geometry":{"type":"Point","coordinates":[-86.2483177,37.0419887]}},{"type":"Feature","properties":{"place_id":326663716,"osm_type":"relation","osm_id":183859,"display_name":"Oakland, Marshall County, Oklahoma, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-96.8022183,34.0944844,-96.7847703,34.1061159],"geometry":{"type":"Point","coordinates":[-96.7938923,34.1000972]}},{"type":"Feature","properties":{"place_id":326666522,"osm_type":"relation","osm_id":188566,"display_name":"Oakland, Susquehanna County, Pennsylvania, 18847, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-75.6188551,41.9443743,-75.5992085,41.9582315],"geometry":{"type":"Point","coordinates":[-75.6049049,41.9500807]}},{"type":"Feature","properties":{"place_id":326634894,"osm_type":"relation","osm_id":109887,"display_name":"Oakland, Yalobusha County, Mississippi, 38948, United States","place_rank":16,"category":"boundary","type":"administrative","importance":0.4500099999999999},"bbox":[-89.926335,34.047495,-89.894432,34.078469],"geometry":{"type":"Point","coordinates":[-89.9164781,34.0556679]}}]}
real 0m0.120s
user 0m0.031s
sys 0m0.003s

That seems to have fixed it.