Slow postcode request after world import

[sdrll]

Hi, after importing the world with the following postgres settings :

  postgresql:
    version: '14'
    parameters:
      jit: "off"
      max_parallel_workers_per_gather: "0"
      archive_mode: "on"
      tcp_keepalives_idle: "300"
      tcp_keepalives_interval: "60"
      shared_buffers: "2GB"
      maintenance_work_mem: "10GB"
      work_mem: "50MB"
      wal_level: replica 
      max_wal_size: "1GB"
      autovacuum_work_mem: "1GB"
      effective_cache_size: "24GB"
      synchronous_commit: "off"
      checkpoint_timeout: "10min"
      checkpoint_completion_target: '0.9'

I have very slow performance only for postcode query. After multiples request, i can only get 10 sec time response :

 EXPLAIN ANALYSE SELECT placex.place_id, placex.osm_type, placex.osm_id, placex.name, placex.class, placex.type, placex.address, placex.extratags, placex.housenumber, placex.postcode, placex.country_code, placex.wikipedia, placex.parent_place_id, placex.rank_address, placex.rank_search, placex.linked_place_id, placex.admin_level, ST_AsEWKB(placex.centroid) AS centroid, ST_AsEWKB(ST_Expand(placex.geometry, 0::INTEGER)) AS bbox, placex.importance
FROM placex
WHERE class = 'boundary'
AND type = 'postal_code'
AND osm_type = 'R' AND placex.postcode = 69300::VARCHAR;

give me :

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_placex_osmid_r on placex  (cost=0.43..8358363.29 rows=1 width=384) (actual time=6013.121..9883.806 rows=1 loops=1)
   Filter: ((class = 'boundary'::text) AND (type = 'postal_code'::text) AND (postcode = '69300'::text))
   Rows Removed by Filter: 1973533
 Planning Time: 0.428 ms
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.248 ms, Inlining 9.338 ms, Optimization 92.311 ms, Emission 54.017 ms, Total 156.914 ms
 Execution Time: 9885.185 ms
(9 rows)

My only guess was that a index was missing from placex but i think they are all here :

 nominatim=# \d placex
                             Table "public.placex"
     Column      |            Type             | Collation | Nullable | Default 
-----------------+-----------------------------+-----------+----------+---------
 place_id        | bigint                      |           | not null | 
 parent_place_id | bigint                      |           |          | 
 linked_place_id | bigint                      |           |          | 
 importance      | double precision            |           |          | 
 indexed_date    | timestamp without time zone |           |          | 
 geometry_sector | integer                     |           |          | 
 rank_address    | smallint                    |           |          | 
 rank_search     | smallint                    |           |          | 
 partition       | smallint                    |           |          | 
 indexed_status  | smallint                    |           |          | 
 osm_type        | character(1)                |           | not null | 
 osm_id          | bigint                      |           | not null | 
 class           | text                        |           | not null | 
 type            | text                        |           | not null | 
 admin_level     | smallint                    |           |          | 
 name            | hstore                      |           |          | 
 address         | hstore                      |           |          | 
 extratags       | hstore                      |           |          | 
 geometry        | geometry(Geometry,4326)     |           | not null | 
 wikipedia       | text                        |           |          | 
 token_info      | jsonb                       |           |          | 
 country_code    | character varying(2)        |           |          | 
 housenumber     | text                        |           |          | 
 postcode        | text                        |           |          | 
 centroid        | geometry(Geometry,4326)     |           |          | 
Indexes:
    "idx_place_id" UNIQUE, btree (place_id)
    "idx_placex_geometry" gist (geometry)
    "idx_placex_geometry_address_area_candidates" gist (geometry) WHERE rank_address >= 1 AND rank_address <= 25 AND (st_geometrytype(geometry) = ANY (ARRAY['ST_Polygon'::text, 'ST_MultiPolygon'::text]))
    "idx_placex_geometry_buildings" spgist (geometry) WHERE address IS NOT NULL AND rank_search = 30 AND (st_geometrytype(geometry) = ANY (ARRAY['ST_Polygon'::text, 'ST_MultiPolygon'::text]))
    "idx_placex_geometry_lower_rank_ways" spgist (geometry) WHERE osm_type = 'W'::bpchar AND rank_search >= 26
    "idx_placex_geometry_placenode" spgist (geometry) WHERE osm_type = 'N'::bpchar AND rank_search < 26 AND class = 'place'::text AND type <> 'postcode'::text
    "idx_placex_geometry_reverse_lookupplacenode" gist (st_buffer(geometry, reverse_place_diameter(rank_search))) WHERE rank_address >= 4 AND rank_address <= 25 AND type <> 'postcode'::text AND name IS NOT NULL AND linked_place_id IS NULL AND osm_type = 'N'::bpchar
    "idx_placex_geometry_reverse_lookuppolygon" gist (geometry) WHERE (st_geometrytype(geometry) = ANY (ARRAY['ST_Polygon'::text, 'ST_MultiPolygon'::text])) AND rank_address >= 4 AND rank_address <= 25 AND type <> 'postcode'::text AND name IS NOT NULL AND indexed_status = 0 AND linked_place_id IS NULL
    "idx_placex_housenumber" btree (parent_place_id) INCLUDE (housenumber) WHERE housenumber IS NOT NULL
    "idx_placex_linked_place_id" btree (linked_place_id) WHERE linked_place_id IS NOT NULL
    "idx_placex_osmid_n" btree (osm_id) WHERE osm_type = 'N'::bpchar
    "idx_placex_osmid_r" btree (osm_id) WHERE osm_type = 'R'::bpchar
    "idx_placex_osmid_w" btree (osm_id) WHERE osm_type = 'W'::bpchar
    "idx_placex_parent_place_id" btree (parent_place_id) WHERE parent_place_id IS NOT NULL
    "idx_placex_rank_address" btree (rank_address)
    "idx_placex_rank_address_sector" btree (rank_address, geometry_sector) WHERE indexed_status > 0
    "idx_placex_rank_boundaries_sector" btree (rank_search, geometry_sector) WHERE class = 'boundary'::text AND type = 'administrative'::text AND indexed_status > 0
    "idx_placex_rank_search" btree (rank_search)
    "idx_placex_wikidata" btree ((extratags -> 'wikidata'::text)) WHERE extratags ? 'wikidata'::text AND class = 'place'::text AND osm_type = 'N'::bpchar AND rank_search < 26
Triggers:
    placex_before_delete AFTER DELETE ON placex FOR EACH ROW EXECUTE FUNCTION placex_delete()
    placex_before_insert BEFORE INSERT ON placex FOR EACH ROW EXECUTE FUNCTION placex_insert()
    placex_before_update BEFORE UPDATE ON placex FOR EACH ROW EXECUTE FUNCTION placex_update()

And from the hardware perpective, other query are faster and i am low on ram and cpu. I probably have not the best IO disk but I think I have another issue. Do you have any clue ? Thanks

[mtmail]

The query planner output will be helpful, thank you.

Which version of Nominatim did you install (output of „nominatim —version“)?

Which query did you run? Here I‘m interested if you added a country filter or if the search was structured or unstructured.

[sdrll]

I use NOMINATIM_VERSION=4.3.2. I do unstructured query for example : https://nominatim.openstreetmap.org/search.php?q=69300&format=jsonv2 . But i tried with structured query and I am getting the same issue. I don't add any other parameters.

[lonvia]

The query you are using has only been introduced in Nominatim 4.4 (in #3362), to be precise. Have you updated your Nominatim code after the import? Then you need to run nominatim admin --migrate. This will create the missing index.

[sdrll]

thanks you. that's was a mistmatch between the import and the api version. FYI, we are tring Nominatim to be used on qwant results page. I can keep you up to date if/when we release it in production

[lonvia]

@sdrll That's awesome. I would love to know when it gets into production. If you need help with tweaking your installation or need so additional features (or just want to support Nominatim development), please do get in touch.