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
146●11●12●19
accept rate: 50%
edited 11 Nov '15, 09:31

scai ♦
33.3k●21●309●459
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.
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.