NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

When I run the specified query for landcover layer from the default.style (z = 8), It takes about half a minute to get the result set, and there are more than 100K records in the result set.

Is it supposed to be this slow? here is the query:

SELECT ST_AsBinary("way") AS geom,"landuse","military","natural" FROM (select way,aeroway,amenity,landuse,leisure,man_made,military,"natural",power,tourism,name,highway,
case when religion in ('christian','jewish') then religion else 'INT-generic'::text end as religion
from planet_osm_polygon where landuse is not null or leisure is not null or aeroway in ('apron','aerodrome')
or amenity in 'parking','university','college','school','hospital','kindergarten','grave_yard','prison') or military in ('barracks','danger_area') or "natural" in ('field','beach','desert','heath','mud','grassland','wood','sand','scrub') or power in ('station','sub_station','generator') or tourism in ('attraction','camp_site','caravan_site','picnic_site','zoo') or highway in ('services','rest_area')  order by z_order,way_area desc) as leisure WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)

and here is the explain analyze of the same query

Subquery Scan on leisure  (cost=660012.22..666569.32 rows=119220 width=288) (actual time=795.469..963.086 rows=109992 loops=1)  
  ->  Sort  (cost=660012.22..660310.27 rows=119220 width=383) (actual time=795.434..838.050 rows=109992 loops=1)  
        Sort Key: planet_osm_polygon.z_order, planet_osm_polygon.way_area  
        Sort Method: quicksort  Memory: 66602kB  
          ->  Bitmap Heap Scan on planet_osm_polygon  (cost=6604.80..649960.03 rows=119220 width=383) (actual time=54.657..271.586 rows=109992 loops=1)  
              Recheck Cond: ((way && '0103000020E6100000010000000500000078000000809656C09A378EE7625B444078000000809656C064C8550C19AB464088FFFFFF7F0155C064C8550C19AB464088FFFFFF7F0155C09A378EE7625B444078000000809656C09A378EE7625B4440'::geometry) AND ((landuse IS NOT NULL) OR (leisure IS NOT NULL) OR (aeroway IS NOT NULL) OR (amenity IS NOT NULL) OR (military IS NOT NULL) OR ("natural" IS NOT NULL) OR (power IS NOT NULL) OR (tourism IS NOT NULL) OR (highway IS NOT NULL)))  
              Filter: ((landuse IS NOT NULL) OR (leisure IS NOT NULL) OR (aeroway = ANY ('{apron,aerodrome}'::text[])) OR (amenity = ANY ('{parking,university,college,school,hospital,kindergarten,grave_yard,prison}'::text[])) OR (military = ANY ('{barracks,danger_area}'::text[])) OR ("natural" = ANY ('{field,beach,desert,heath,mud,grassland,wood,sand,scrub}'::text[])) OR (power = ANY ('{station,sub_station,generator}'::text[])) OR (tourism = ANY ('{attraction,camp_site,caravan_site,picnic_site,zoo}'::text[])) OR (highway = ANY ('{services,rest_area}'::text[])))  
              Rows Removed by Filter: 52924  
              ->  Bitmap Index Scan on planet_osm_polygon_leisure  (cost=0.00..6574.99 rows=177410 width=0) (actual time=36.457..36.457 rows=162916 loops=1)  
                    Index Cond: (way && '0103000020E6100000010000000500000078000000809656C09A378EE7625B444078000000809656C064C8550C19AB464088FFFFFF7F0155C064C8550C19AB464088FFFFFF7F0155C09A378EE7625B444078000000809656C09A378EE7625B4440'::geometry)  
Total runtime: 986.102 ms

asked 11 Nov '15, 09:04

khamooshi's gravatar image

khamooshi
146111219
accept rate: 50%

edited 11 Nov '15, 09:31

scai's gravatar image

scai ♦
33.3k21309459


I don't know if it is "supposed" to be slow but I can confirm that it usually is. I suggest you do a

CREATE INDEX run_faster_dammit 
   ON planet_osm_polygon 
   USING gist(way) 
   WHERE landuse is not null 
      OR leisure is not null
      OR aeroway in ('apron','aerodrome') 
      OR amenity in ('parking','university','college','school','hospital','kindergarten','grave_yard','prison') 
      OR military in ('barracks','danger_area') 
      OR "natural" in ('field','beach','desert','heath','mud','grassland','wood','sand','scrub') 
      OR power in ('station','sub_station','generator') 
      OR tourism in ('attraction','camp_site','caravan_site','picnic_site','zoo')
      OR highway in ('services','rest_area');

which will speed things up significantly.

permanent link

answered 11 Nov '15, 09:13

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

1

Thank you for the answer and your helpful presentation :)(http://www.geofabrik.de/media/2012-09-08-osm2pgsql-performance.pdf)

It is better now, but still takes few seconds to complete. I think because there are 100K+ records in the result set.

would you send a list of all indexes you added to the database.

(11 Nov '15, 09:55) khamooshi
1

No, that won't help you because the indexes you need are different depending on the style you are using, and even different versions of the same style will have different queries and therefore need different indexes. Essentially, you use the log_min_duration setting in your postgresql config and then create indexes that mimic exactly the WHERE clause of the slowest queries.

(11 Nov '15, 10:48) Frederik Ramm ♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×440
×28
×3

question asked: 11 Nov '15, 09:04

question was seen: 2,780 times

last updated: 11 Nov '15, 10:48

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum