{"action":"created","issue":{"url":"https://api.github.com/repos/twain47/Nominatim/issues/578","repository_url":"https://api.github.com/repos/twain47/Nominatim","labels_url":"https://api.github.com/repos/twain47/Nominatim/issues/578/labels{/name}","comments_url":"https://api.github.com/repos/twain47/Nominatim/issues/578/comments","events_url":"https://api.github.com/repos/twain47/Nominatim/issues/578/events","html_url":"https://github.com/twain47/Nominatim/issues/578","id":189322931,"number":578,"title":"Nominatim geocoding slow queries optimization","user":{"login":"aaphadke","id":6111886,"avatar_url":"https://avatars.githubusercontent.com/u/6111886?v=3","gravatar_id":"","url":"https://api.github.com/users/aaphadke","html_url":"https://github.com/aaphadke","followers_url":"https://api.github.com/users/aaphadke/followers","following_url":"https://api.github.com/users/aaphadke/following{/other_user}","gists_url":"https://api.github.com/users/aaphadke/gists{/gist_id}","starred_url":"https://api.github.com/users/aaphadke/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aaphadke/subscriptions","organizations_url":"https://api.github.com/users/aaphadke/orgs","repos_url":"https://api.github.com/users/aaphadke/repos","events_url":"https://api.github.com/users/aaphadke/events{/privacy}","received_events_url":"https://api.github.com/users/aaphadke/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2016-11-15T07:54:02Z","updated_at":"2016-11-16T18:47:03Z","closed_at":null,"body":"Hi ,\r\n\r\nWe are using Nominatim with following configs for geocoding (getting lat and lon)\r\nNominatim Version 2.4.0\r\n16 core CPUS,     122GB RAM    320GB SSD HD\r\n\r\nAnd our postgresql.conf looks like following\r\n\r\nshared_buffers = 1GB            # min 128kB\r\nmaintenance_work_mem = 10GB        # min 1MB\r\nwork_mem = 50MB                # min 64kB\r\nmaintenance_work_mem = 10GB        # min 1MB\r\neffective_cache_size = 24GB\r\nsynchronous_commit = off        # synchronization level;\r\ncheckpoint_segments = 100        # in logfile segments, min 1, 16MB each\r\ncheckpoint_timeout = 10min        # range 30s-1h\r\ncheckpoint_completion_target = 0.9    # checkpoint target duration, 0.0 - 1.0\r\n\r\nWe are seeing following queries taking time (almost always> 5 seconds) in postgres logs for geocode\r\nhttp://nominatim.local.com/nominatim/search.php?format=json&limit=1&q=322+West+Street%2CCarlisle%2CMassachusetts%2C01741%2CUnited+States\r\n\r\nLOG:  duration: 19915.484 ms  statement: select place_id, (select count(*) from (select unnest(ARRAY[4523464,5730972,36867388]) INTERSECT select unnest(nameaddress_vector))s) as exactmatch from search_name where name_vector @> ARRAY[2832006] and array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[15678054,348921,362979,36867389] and country_code = 'us' order by (case when importance = 0 OR importance IS NULL then 0.75-(search_rank::float/40) else importance end) DESC, exactmatch DESC limit 2\r\n\r\nhttp://nominatim.local.com/nominatim/search.php?format=json&limit=1&q=106+Winchester+Street+%232%2CBrookline%2CMassachusetts%2C02446%2CUnited+States\r\n\r\nLOG:  duration: 13518.676 ms  statement: select place_id, (select count(*) from (select unnest(ARRAY[6122619,5730972,18878827]) INTERSECT select unnest(nameaddress_vector))s) as exactmatch from search_name where name_vector @> ARRAY[2294177] and array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[1997025,53652,362979,18878828] and country_code = 'us' order by (case when importance = 0 OR importance IS NULL then 0.75-(search_rank::float/40) else importance end) DESC, exactmatch DESC limit 2\r\n\r\nCan someone suggest what are we missing here? Anyways to improve like creating indexes or changing postgres configs?\r\n\r\nThanks."},"comment":{"url":"https://api.github.com/repos/twain47/Nominatim/issues/comments/261034950","html_url":"https://github.com/twain47/Nominatim/issues/578#issuecomment-261034950","issue_url":"https://api.github.com/repos/twain47/Nominatim/issues/578","id":261034950,"user":{"login":"aaphadke","id":6111886,"avatar_url":"https://avatars.githubusercontent.com/u/6111886?v=3","gravatar_id":"","url":"https://api.github.com/users/aaphadke","html_url":"https://github.com/aaphadke","followers_url":"https://api.github.com/users/aaphadke/followers","following_url":"https://api.github.com/users/aaphadke/following{/other_user}","gists_url":"https://api.github.com/users/aaphadke/gists{/gist_id}","starred_url":"https://api.github.com/users/aaphadke/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aaphadke/subscriptions","organizations_url":"https://api.github.com/users/aaphadke/orgs","repos_url":"https://api.github.com/users/aaphadke/repos","events_url":"https://api.github.com/users/aaphadke/events{/privacy}","received_events_url":"https://api.github.com/users/aaphadke/received_events","type":"User","site_admin":false},"created_at":"2016-11-16T18:47:03Z","updated_at":"2016-11-16T18:47:03Z","body":"Thanks Ionvia for answers so far ..appreciate your help!\r\n\r\nBelow are the outputs of the commands you asked\r\n\r\n**nominatim=# \\d search_name**\r\n                Table \"public.search_name\"\r\n       Column       |          Type           | Modifiers\r\n--------------------+-------------------------+-----------\r\n place_id           | bigint                  |\r\n search_rank        | integer                 |\r\n address_rank       | integer                 |\r\n importance         | double precision        |\r\n country_code       | character varying(2)    |\r\n name_vector        | integer[]               |\r\n nameaddress_vector | integer[]               |\r\n centroid           | geometry(Geometry,4326) |\r\nIndexes:\r\n    \"idx_search_name_centroid\" gist (centroid)\r\n    \"idx_search_name_name_vector\" gin (name_vector) WITH (fastupdate=off)\r\n    \"idx_search_name_nameaddress_vector\" gin (nameaddress_vector) WITH (fastupdate=off)\r\n    \"idx_search_name_place_id\" btree (place_id)\r\n\r\n**nominatim=# \\d placex**\r\n                       Table \"public.placex\"\r\n         Column          |            Type             | Modifiers\r\n-------------------------+-----------------------------+-----------\r\n place_id                | bigint                      | not null\r\n partition               | integer                     |\r\n osm_type                | character(1)                | not null\r\n osm_id                  | bigint                      | not null\r\n class                   | text                        | not null\r\n type                    | text                        | not null\r\n name                    | hstore                      |\r\n admin_level             | integer                     |\r\n housenumber             | text                        |\r\n street                  | text                        |\r\n addr_place              | text                        |\r\n isin                    | text                        |\r\n postcode                | text                        |\r\n country_code            | character varying(2)        |\r\n extratags               | hstore                      |\r\n geometry                | geometry(Geometry,4326)     | not null\r\n parent_place_id         | bigint                      |\r\n linked_place_id         | bigint                      |\r\n rank_address            | integer                     |\r\n rank_search             | integer                     |\r\n importance              | double precision            |\r\n indexed_status          | integer                     |\r\n indexed_date            | timestamp without time zone |\r\n wikipedia               | text                        |\r\n geometry_sector         | integer                     |\r\n calculated_country_code | character varying(2)        |\r\n centroid                | geometry(Geometry,4326)     |\r\nIndexes:\r\n    \"idx_place_id\" UNIQUE, btree (place_id)\r\n    \"idx_placex_adminname\" btree (make_standard_name(name -> 'name'::text), rank_search) WHERE osm_type = 'N'::bpchar AND rank_search < 26\r\n    \"idx_placex_geometry\" gist (geometry)\r\n    \"idx_placex_linked_place_id\" btree (linked_place_id)\r\n    \"idx_placex_osmid\" btree (osm_type, osm_id)\r\n    \"idx_placex_parent_place_id\" btree (parent_place_id) WHERE parent_place_id IS NOT NULL\r\n    \"idx_placex_pendingsector\" btree (rank_search, geometry_sector) WHERE indexed_status > 0\r\n    \"idx_placex_rank_address\" btree (rank_address)\r\n    \"idx_placex_rank_search\" btree (rank_search)\r\n    \"idx_placex_reverse_geometry\" gist (geometry) WHERE rank_search <> 28 AND (name IS NOT NULL OR housenumber IS NOT NULL) AND (class <> ALL (ARRAY['waterway'::text, 'railway'::text, 'tunnel'::text, 'bridge'::text]))\r\n    \"idx_placex_sector\" btree (geometry_sector, rank_address, osm_type, osm_id)\r\nTriggers:\r\n    placex_before_delete AFTER DELETE ON placex FOR EACH ROW EXECUTE PROCEDURE placex_delete()\r\n    placex_before_insert BEFORE INSERT ON placex FOR EACH ROW EXECUTE PROCEDURE placex_insert()\r\n    placex_before_update BEFORE UPDATE ON placex FOR EACH ROW EXECUTE PROCEDURE placex_update()\r\n\r\n**nominatim=# EXPLAIN** select place_id, (select count(*) from (select unnest(ARRAY[8053860,18280874,1813026,11573732]) INTERSECT select unnest(nameaddress_vector))s) as exactmatch from search_name where name_vector @> ARRAY[1038416] and array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[12924,13740561,29688,57845,19924055] and address_rank between 16 and 27 order by exists(select place_id from placex where parent_place_id = search_name.place_id and transliteration(housenumber) ~ E'\\m1988 1\\M' limit 1) desc, - abs(26 - address_rank) + 3 DESC, exactmatch DESC limit 20;\r\n                                                                                    QUERY PLAN\r\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Limit  (cost=136946.74..136946.75 rows=1 width=419)\r\n   ->  Sort  (cost=136946.74..136946.75 rows=1 width=419)\r\n         Sort Key: ((SubPlan 2)), (((- abs((26 - search_name.address_rank))) + 3)), ((SubPlan 1))\r\n         ->  Bitmap Heap Scan on search_name  (cost=392.56..136946.73 rows=1 width=419)\r\n               Recheck Cond: (name_vector @> '{1038416}'::integer[])\r\n               Filter: ((address_rank >= 16) AND (address_rank <= 27) AND (array_cat(nameaddress_vector, '{}'::integer[]) @> '{12924,13740561,29688,57845,19924055}'::integer[]))\r\n               ->  Bitmap Index Scan on idx_search_name_name_vector  (cost=0.00..392.56 rows=34742 width=0)\r\n                     Index Cond: (name_vector @> '{1038416}'::integer[])\r\n               SubPlan 1\r\n                 ->  Aggregate  (cost=3.53..3.54 rows=1 width=0)\r\n                       ->  Subquery Scan on s  (cost=0.00..3.52 rows=1 width=0)\r\n                             ->  HashSetOp Intersect  (cost=0.00..3.51 rows=1 width=0)\r\n                                   ->  Append  (cost=0.00..3.01 rows=200 width=0)\r\n                                         ->  Subquery Scan on \"*SELECT* 1\"  (cost=0.00..1.51 rows=100 width=0)\r\n                                               ->  Result  (cost=0.00..0.51 rows=100 width=0)\r\n                                         ->  Subquery Scan on \"*SELECT* 2\"  (cost=0.00..1.51 rows=100 width=0)\r\n                                               ->  Result  (cost=0.00..0.51 rows=100 width=0)\r\n               SubPlan 2\r\n                 ->  Limit  (cost=61.02..8182.89 rows=1 width=8)\r\n                       ->  Bitmap Heap Scan on placex  (cost=61.02..8182.89 rows=1 width=8)\r\n                             Recheck Cond: (parent_place_id = search_name.place_id)\r\n                             Filter: (transliteration(housenumber) ~ 'm1988 1M'::text)\r\n                             ->  Bitmap Index Scan on idx_placex_parent_place_id  (cost=0.00..61.02 rows=2045 width=0)\r\n                                   Index Cond: (parent_place_id = search_name.place_id)\r\n(24 rows)\r\n\r\n**nominatim=# SELECT * FROM word WHERE word_id IN (8053860,18280874,1813026,11573732, 1038416, 12924,13740561,29688,57845,19924055);**\r\n word_id  |   word_token    |      word      | class | type | country_code | search_name_count | operator\r\n----------+-----------------+----------------+-------+------+--------------+-------------------+----------\r\n    12924 | 02              |                |       |      |              |              3397 |\r\n    29688 | krakow          |                |       |      |              |               168 |\r\n    57845 | poland          |                |       |      |              |               357 |\r\n  1038416 |  2              | La Druga       |       |      |              |                 0 |\r\n  1813026 |  krakow         | Krąków         |       |      |              |                 0 |\r\n  8053860 |  02             | 02             |       |      |              |                 0 |\r\n 11573732 |  poland         | Poeland        |       |      |              |                 0 |\r\n 13740561 | szprotawa       |                |       |      |              |                 0 |\r\n 18280874 |  szprotawa      | Szprotawa      |       |      |              |                 0 |\r\n 19924055 |  united kingdom | United Kingdom |       |      |              |                 0 |\r\n(10 rows)"}}
{"actor":{"display_login":"aaphadke"}}