Slow query for landcover layer

Question

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

Accepted Answer

2

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 Ramm ♦
82.5k●92●720●1273
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 ♦

Slow query for landcover layer

Follow this question

Related questions