Skip to content

Instantly share code, notes, and snippets.

@pnorman

pnorman/carto_1k_x4.log

Last active December 24, 2015 13:49
Show Gist options
  • Save pnorman/6808167 to your computer and use it in GitHub Desktop.
Save pnorman/6808167 to your computer and use it in GitHub Desktop.
Download ZIP
Analysis of slow postgresql queries from 1k meta-tiles, repeated 4x
Raw
carto_1k_x4.log
Restricted to queries>.25s
Standard indexes plus index for non-building polygons.
SELECT ST_AsBinary("way") AS geom,"landuse","natural","waterway" FROM (select way,"natural",waterway,landuse,name from planet_osm_polygon where (waterway in ('dock','mill_pond','riverbank','canal') or landuse in ('reservoir','water','basin') or "natural" in ('lake','water','land','glacier','mud')) and building is null order by z_order,way_area desc ) as water_areas WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
7 16 21.9s 22.8s 24.9s 364.8
13 271 0.2s 0.8s 2.5s 216.8
14 129 0.3s 0.3s 0.7s 38.7
Total: 620.3
SELECT ST_AsBinary("way") AS geom,"aeroway","building" FROM (select way,aeroway, case when building in ('residential','house','garage','garages','detached','terrace','apartments') then 'INT-light'::text else building end as building from planet_osm_polygon where (building is not null and building not in ('no','station','supermarket','planned') and (railway is null or railway != 'station') and (amenity is null or amenity != 'place_of_worship')) or aeroway = 'terminal' order by z_order,way_area desc) as buildings WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 152 0.3s 1.4s 3.3s 212.8
14 88 0.3s 0.7s 1.9s 61.6
15 14 0.3s 0.4s 0.8s 5.6
16 1 0.7s 0.7s 0.7s 0.7
Total: 280.7
SELECT ST_AsBinary("way") AS geom FROM (select way,way_area,name,boundary from planet_osm_polygon where boundary='national_park' and building is null) as boundary WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
7 8 18.3s 18.5s 18.6s 148
13 68 0.3s 0.7s 1.5s 47.6
14 28 0.3s 0.3s 0.6s 8.4
Total: 204.0
SELECT ST_AsBinary("way") AS geom,"amenity","building","railway" FROM (select way,building,railway,amenity from planet_osm_polygon where railway='station' or building in ('station','supermarket') or amenity='place_of_worship' order by z_order,way_area desc) as buildings WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 204 0.3s 0.8s 1.6s 163.2
14 90 0.2s 0.4s 0.6s 36
Total: 199.2
SELECT ST_AsBinary("way") AS geom,"ele","military","name","natural","place" FROM (select way,aeroway,shop,access,amenity,leisure,landuse,man_made,"natural",place,tourism,NULL as ele,name,ref,military,waterway,historic,'no'::text as point from planet_osm_polygon where amenity is not null or shop in ('supermarket','bakery','clothes','fashion','convenience','doityourself','hairdresser','department_store', 'butcher','car','car_repair','bicycle') or leisure is not null or landuse is not null or tourism is not null or "natural" is not null or man_made in ('lighthouse','windmill') or place='island' or military='danger_area' or historic in ('memorial','archaeological_site') ) as text WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 204 0.3s 0.8s 1.6s 163.2
Total: 163.2
SELECT ST_AsBinary("way") AS geom,"landuse","leisure" FROM (select way,landuse,leisure from planet_osm_polygon where (landuse = 'military' or leisure='nature_reserve') and building is null ) as landuse_overlay WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 136 0.3s 0.8s 1.6s 108.8
14 65 0.3s 0.3s 0.6s 19.5
Total: 128.3
SELECT ST_AsBinary("way") AS geom,"aerialway","railway" FROM (select way,name,railway,aerialway,disused from planet_osm_polygon where railway in ('station','halt','tram_stop') or aerialway='station' ) as stations WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 136 0.3s 0.8s 1.5s 108.8
Total: 108.8
SELECT ST_AsBinary("way") AS geom,"feature","religion" FROM (select way, religion, coalesce (aeroway, amenity, landuse, leisure, military, "natural", power, tourism, highway) as feature from ( select way, ('aeroway_' || (case when aeroway in ('apron', 'aerodrome') then aeroway else null end)) as aeroway, ('amenity_' || (case when amenity in ('parking', 'university', 'college', 'school', 'hospital', 'kindergarten', 'grave_yard') then amenity else null end)) as amenity, ('landuse_' || (case when landuse in ('quarry', 'vineyard', 'orchard', 'cemetery', 'grave_yard', 'residential', 'garages', 'field', 'meadow', 'grass', 'allotments', 'forest', 'farmyard', 'farm', 'farmland', 'recreation_ground', 'conservation', 'village_green', 'retail', 'industrial', 'railway', 'commercial', 'brownfield', 'landfill', 'greenfield', 'construction') then landuse else null end)) as landuse, ('leisure_' || (case when leisure in ('swimming_pool', 'playground', 'park', 'recreation_ground', 'common', 'garden', 'golf_course') then leisure else null end)) as leisure, ('military_' || (case when military in ('barracks', 'danger_area') then military else null end)) as military, ('natural_' || (case when "natural" in ('field','beach','desert','heath','mud','grassland','wood','sand','scrub') then "natural" else null end)) as "natural", ('power_' || (case when power in ('station','sub_station','generator') then power else null end)) as power, ('tourism_' || (case when tourism in ('attraction', 'camp_site', 'caravan_site', 'picnic_site', 'zoo') then tourism else null end)) as tourism, ('highway_' || (case when highway in ('services', 'rest_area') then highway else null end)) as 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') 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 landcover ) as features WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 69 0.3s 1.0s 2.2s 69
14 38 0.3s 0.4s 1.2s 15.2
Total: 84.2
SELECT ST_AsBinary("way") AS geom,"tourism" FROM (select way,amenity,shop,tourism,highway,man_made,access,religion,waterway,lock,historic,leisure from planet_osm_polygon where amenity is not null or shop is not null or tourism in ('alpine_hut','camp_site','caravan_site','guest_house','hostel','hotel','motel','museum','viewpoint','bed_and_breakfast','information','chalet') or highway in ('bus_stop','traffic_signals') or man_made in ('mast','water_tower') or historic in ('memorial','archaeological_site') or leisure='playground' ) as points WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.3s 0.8s 1.6s 54.4
14 36 0.3s 0.4s 0.7s 14.4
Total: 68.8
SELECT ST_AsBinary("way") AS geom,"bicycle","bridge","construction","feature","foot","horse","service","tracktype","tunnel" FROM (select way,tracktype, coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' ||(case when railway='preserved' and service in ('spur','siding','yard') then 'INT-preserved-ssy'::text when railway in ('spur','siding') or (railway='rail' and service in ('spur','siding','yard')) then 'spur-siding-yard' when railway in ('light_rail','narrow_gauge','funicular','rail','subway','tram','spur','siding','monorail','platform','preserved','disused','abandoned','construction','miniature','turntable') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature, horse,bicycle,foot,construction, case when tunnel in ('yes','true','1') then 'yes'::text else 'no'::text end as tunnel, case when bridge in ('yes','true','1','viaduct') then 'yes'::text else 'no'::text end as bridge, case when service in ('parking_aisle','drive-through','driveway') then 'INT-minor'::text else 'INT-normal'::text end as service from planet_osm_line where highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','narrow_gauge','funicular','rail','subway','tram','spur','siding','monorail','platform','preserved','disused','abandoned','construction','miniature','turntable') order by z_order) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 92 0.2s 0.7s 1.4s
14 11 0.2s 0.3s 0.4s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway in ('residential','unclassified','pedestrian','service','footway','living_street','track','path','platform','services') then highway else null end)), ('railway_' || (case when railway in ('platform') then railway else null end)), (('aeroway_' || case when aeroway in ('runway','taxiway','helipad') then aeroway else null end))) as feature from planet_osm_polygon where highway in ('residential','unclassified','pedestrian','service','footway','living_street','track','path','platform','services') or railway in ('platform') or aeroway in ('runway','taxiway','helipad') order by z_order,way_area desc) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.3s 0.8s 1.6s
14 31 0.3s 0.4s 0.7s
SELECT ST_AsBinary("way") AS geom,"natural" FROM (select way,"natural" from planet_osm_polygon where "natural" in ('marsh','wetland') and building is null order by z_order,way_area desc ) as water_areas WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.2s 0.8s 1.8s
14 32 0.2s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom FROM (select way,man_made from planet_osm_polygon where man_made in ('pier','breakwater','groyne')) as piers WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.3s 0.8s 1.7s
14 30 0.3s 0.3s 0.7s
SELECT ST_AsBinary("way") AS geom,"name","way_area" FROM (select way,name,way_area from planet_osm_polygon where "natural"='glacier' and building is null order by way_area desc ) as glaciers WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.2s 0.8s 1.6s
14 31 0.2s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"leisure" FROM (select way,leisure, case when leisure='pitch' then 2 when leisure='track' then 1 else 0 end as prio from planet_osm_polygon where leisure in ('sports_centre','stadium','pitch','track') order by z_order,prio,way_area desc ) as sports_grounds WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 65 0.3s 0.8s 1.7s
14 29 0.2s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"tourism" FROM (select way,name,tourism from planet_osm_polygon where tourism='theme_park') as theme_park WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.3s 0.7s 1.5s
14 29 0.3s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"natural" FROM (select * from planet_osm_polygon where aeroway in ('airport','aerodrome','helipad') or barrier in ('bollard','gate','lift_gate','block') or highway in ('mini_roundabout','gate') or man_made in ('lighthouse','power_wind','windmill','mast') or (power='generator' and ("generator:source"='wind' or power_source='wind')) or "natural" in ('peak','volcano','spring','tree') or railway='level_crossing' ) as symbols WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 68 0.3s 0.8s 1.6s
SELECT ST_AsBinary("way") AS geom,"ele","military","name","natural","place","tourism" FROM (select way,aeroway,shop,access,amenity,leisure,landuse,man_made,"natural",place,tourism,NULL as ele,name,ref,military,waterway,historic,'no'::text as point from planet_osm_polygon where amenity is not null or shop in ('supermarket','bakery','clothes','fashion','convenience','doityourself','hairdresser','department_store', 'butcher','car','car_repair','bicycle') or leisure is not null or landuse is not null or tourism is not null or "natural" is not null or man_made in ('lighthouse','windmill') or place='island' or military='danger_area' or historic in ('memorial','archaeological_site') ) as text WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
14 144 0.3s 0.4s 0.7s
SELECT ST_AsBinary("way") AS geom FROM (select way from planet_osm_line where route='ferry' ) as routes WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
7 4 9.5s 9.8s 10.3s
13 33 0.3s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"highway","tunnel" FROM (select way,highway, case when tunnel in ('yes','true','1') then 'yes'::text else 'no'::text end as tunnel, case when service in ('parking_aisle','drive-through','driveway') then 'INT-minor'::text else 'INT-normal'::text end as service from planet_osm_line where highway in ('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','secondary_link','tertiary','tertiary_link','residential','unclassified','road','service','pedestrian','raceway','living_street') order by z_order) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 87 0.3s 0.5s 0.9s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('spur', 'siding') or (railway='rail' and service in ('spur','siding','yard')) then 'INT-spur-siding-yard' when railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature,horse,bicycle,foot,tracktype from planet_osm_line where (highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction')) and bridge in ('yes','true','1','viaduct') and layer = '5' order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 113 0.2s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('spur', 'siding') or (railway='rail' and service in ('spur','siding','yard')) then 'INT-spur-siding-yard' when railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature,horse,bicycle,foot,tracktype from planet_osm_line where (highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction')) and bridge in ('yes','true','1','viaduct') and layer = '4' order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 109 0.2s 0.4s 0.7s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('spur', 'siding') or (railway='rail' and service in ('spur','siding','yard')) then 'INT-spur-siding-yard' when railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature,horse,bicycle,foot,tracktype from planet_osm_line where (highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction')) and bridge in ('yes','true','1','viaduct') and layer = '2' order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time tot time
---------------------------------------------------
13 111 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('spur', 'siding') or (railway='rail' and service in ('spur','siding','yard')) then 'INT-spur-siding-yard' when railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature,horse,bicycle,foot,tracktype from planet_osm_line where (highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction')) and bridge in ('yes','true','1','viaduct') and layer = '3' order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 107 0.2s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('spur', 'siding') or (railway='rail' and service in ('spur','siding','yard')) then 'INT-spur-siding-yard' when railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature,horse,bicycle,foot,tracktype from planet_osm_line where (highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction')) and bridge in ('yes','true','1','viaduct') and layer = '1' order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 106 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('spur', 'siding') or (railway='rail' and service in ('spur','siding','yard')) then 'INT-spur-siding-yard' when railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction') then railway else null end)), ('aeroway_' || (case when aeroway in ('runway', 'taxiway') then aeroway else null end))) as feature,horse,bicycle,foot,tracktype from planet_osm_line where (highway is not null or aeroway in ('runway','taxiway') or railway in ('light_rail','subway','narrow_gauge','rail','spur','siding','disused','abandoned','construction')) and bridge in ('yes','true','1','viaduct') and (layer is null or layer = '0') order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 106 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"highway" FROM (select way,highway from planet_osm_line where highway in ('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','secondary_link','tertiary','tertiary_link','residential','unclassified') and tunnel in ('yes','true','1') order by z_order) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 69 0.3s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"natural","railway" FROM (select * from planet_osm_polygon where aeroway in ('airport','aerodrome','helipad') or barrier in ('bollard','gate','lift_gate','block') or highway in ('mini_roundabout','gate') or man_made in ('lighthouse','power_wind','windmill','mast') or (power='generator' and ("generator:source"='wind' or power_source='wind')) or "natural" in ('peak','volcano','spring','tree') or railway='level_crossing' ) as symbols WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 72 0.3s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"feature","tunnel" FROM (select way,coalesce(('highway_' || (case when highway is not null then highway else null end)), ('railway_' || (case when railway in ('rail','tram','light_rail','funicular','narrow_gauge') then railway else null end))) as feature, case when tunnel in ('yes','true','1') then 'yes'::text else tunnel end as tunnel from planet_osm_roads where highway is not null or (railway is not null and railway!='preserved' and (service is null or service not in ('spur','siding','yard'))) order by z_order ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
7 4 4.9s 5.4s 6.8s
SELECT ST_AsBinary("way") AS geom,"aerialway","name","railway" FROM (select way,name,railway,aerialway,disused from planet_osm_polygon where railway in ('station','halt','tram_stop') or aerialway='station' ) as stations WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 63 0.2s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"name","place" FROM (select way,place,name from planet_osm_point where place in ('city','metropolis','town','large_town','small_town') and (capital is null or capital != 'yes') ) as placenames WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
7 4 3.8s 3.8s 4.0s
13 8 0.3s 0.4s 0.4s
SELECT ST_AsBinary("way") AS geom,"name" FROM (select way,place,name,ref from planet_osm_point where place in ('city','metropolis','town') and capital='yes' ) as placenames WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
7 4 3.6s 3.6s 3.6s
13 5 0.3s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"waterway" FROM (select way,waterway from planet_osm_line where waterway in ('stream','drain','ditch') and (tunnel is null or tunnel != 'yes') ) as water_lines WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 40 0.3s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"highway","name" FROM (select way,highway,name from planet_osm_line where waterway IS NULL and leisure IS NULL and landuse IS NULL and name is not null ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 36 0.3s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"disused","tunnel","waterway" FROM (select way,waterway,disused,lock,name, case when tunnel in ('yes','true','1') then 'yes'::text else tunnel end as tunnel from planet_osm_line where waterway in ('weir','river','canal','derelict_canal','stream','drain','ditch','wadi') and (bridge is null or bridge not in ('yes','true','1','aqueduct')) order by z_order ) as water_lines WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 40 0.3s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"bridge","highway","length","ref" FROM (select way,coalesce(highway,aeroway) as highway,ref,char_length(ref) as length, case when bridge in ('yes','true','1') then 'yes'::text else 'no'::text end as bridge from planet_osm_line where (highway is not null or aeroway is not null) and ref is not null and char_length(ref) between 1 and 8 ) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 38 0.3s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"name","place" FROM (select way,place,name,ref from planet_osm_point where place in ('country','state') ) as placenames WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
7 4 3.6s 3.7s 3.7s
SELECT ST_AsBinary("way") AS geom,"name","way_area" FROM (select way,way_area,name from planet_osm_polygon where name is not null and (waterway is null or waterway != 'riverbank') and place is null order by way_area desc ) as text WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 36 0.3s 0.4s 0.7s
SELECT ST_AsBinary("way") AS geom,"disused","lock","name","tunnel","waterway" FROM (select way,waterway,disused,lock,name, case when tunnel in ('yes','true','1') then 'yes'::text else tunnel end as tunnel from planet_osm_line where waterway in ('weir','river','canal','derelict_canal','stream','drain','ditch','wadi') order by z_order ) as water_lines WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 38 0.3s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"bridge","railway" FROM (select way,railway,bridge from planet_osm_line where railway='tram' and (tunnel is null or tunnel not in ('yes','true','1'))) as trams WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 38 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom FROM (select way from planet_osm_line where highway='bus_guideway' and (tunnel is null or tunnel not in ('yes','true','1'))) as guideways WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 38 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom FROM (select way,name from planet_osm_line where waterway='dam') as dam WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 35 0.2s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"man_made" FROM (select way,man_made from planet_osm_line where man_made in ('pier','breakwater','groyne')) as piers WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 35 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"bicycle","foot","highway","horse" FROM (select way,highway,horse,foot,bicycle from planet_osm_line where highway in ('bridleway','footway','cycleway','path') and tunnel in ('yes','true','1') order by z_order) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 34 0.2s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"natural" FROM (select way,"natural",man_made from planet_osm_line where "natural" = 'cliff' or man_made = 'embankment') as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 33 0.3s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"aerialway" FROM (select way,aerialway from planet_osm_line where aerialway is not null) as aerialways WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 32 0.3s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom FROM (select way from planet_osm_polygon where "historic"='castle_walls') as castle_walls WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 31 0.3s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"barrier" FROM (select way,barrier from planet_osm_polygon where barrier is not null) as barriers WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 30 0.2s 0.3s 0.6s
SELECT ST_AsBinary("way") AS geom,"int_tc_type" FROM (select distinct on (p.way) p.way as way,l.highway as int_tc_type from planet_osm_point p join planet_osm_line l on ST_DWithin(p.way,l.way,0.1) join (values ('tertiary',1), ('unclassified',2), ('residential',3), ('living_street',4), ('service',5) ) as v (highway,prio) on v.highway=l.highway where p.highway='turning_circle' order by p.way,v.prio ) as turning_circle WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
15 18 0.3s 0.5s 0.9s
SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way,coalesce(('highway_' || (case when highway in ('residential','unclassified','pedestrian','service','footway','track','path','platform') then highway else null end)), ('railway_' || (case when railway in ('platform') then railway else null end))) as feature from planet_osm_polygon where highway in ('residential','unclassified','pedestrian','service','footway','track','path','platform') or railway in ('platform') order by z_order,way_area desc) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 28 0.3s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom FROM (select way from planet_osm_polygon where leisure ='marina') as marinas WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 28 0.3s 0.3s 0.7s
SELECT ST_AsBinary("way") AS geom,"admin_level" FROM (select way,admin_level from planet_osm_roads where "boundary"='administrative' and admin_level in ('0','1','2','3','4') ) as admin WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
7 4 2.0s 2.1s 2.3s
SELECT ST_AsBinary("way") AS geom,"ele","military","name","natural","place" FROM (select way,amenity,shop,access,leisure,landuse,man_made,"natural",place,tourism,ele,name,ref,military,aeroway,waterway,historic,'yes'::text as point from planet_osm_point where amenity is not null or shop in ('supermarket','bakery','clothes','fashion','convenience','doityourself','hairdresser','department_store','butcher','car','car_repair','bicycle','florist') or leisure is not null or landuse is not null or tourism is not null or "natural" is not null or man_made in ('lighthouse','windmill') or place='island' or military='danger_area' or aeroway='gate' or waterway='lock' or historic in ('memorial','archaeological_site') ) as text WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 22 0.3s 0.3s 0.4s
SELECT ST_AsBinary("way") AS geom,"aerialway","railway" FROM (select way,name,railway,aerialway,disused from planet_osm_point where railway in ('station','halt','tram_stop','subway_entrance') or aerialway='station' ) as stations WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 8 0.4s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"name","place" FROM (select way,place,name from planet_osm_point where place in ('suburb','village','large_village','hamlet','locality','isolated_dwelling','farm') ) as placenames WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 8 0.3s 0.4s 0.4s
SELECT ST_AsBinary("way") AS geom,"natural" FROM (select * from planet_osm_point where aeroway in ('airport','aerodrome','helipad') or barrier in ('bollard','gate','lift_gate','block') or highway in ('mini_roundabout','gate') or man_made in ('lighthouse','power_wind','windmill','mast') or (power='generator' and ("generator:source"='wind' or power_source='wind')) or "natural" in ('peak','volcano','spring','tree','cave_entrance') or railway='level_crossing' ) as symbols WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 4 0.4s 0.4s 0.6s
SELECT ST_AsBinary("way") AS geom,"tourism" FROM (select way,amenity,shop,tourism,highway,man_made,access,religion,waterway,lock,historic,leisure from planet_osm_point where amenity is not null or shop is not null or tourism in ('alpine_hut','camp_site','caravan_site','guest_house','hostel','hotel','motel','museum','viewpoint','bed_and_breakfast','information','chalet') or highway in ('bus_stop','traffic_signals','ford') or man_made in ('mast','water_tower') or historic in ('memorial','archaeological_site') or waterway='lock' or lock='yes' or leisure in ('playground','slipway') ) as points WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 4 0.4s 0.4s 0.5s
SELECT ST_AsBinary("way") AS geom,"name","ref" FROM (select way,ref,name from planet_osm_point where highway='motorway_junction' ) as junctions WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
13 4 0.3s 0.3s 0.3s
SELECT ST_AsBinary("way") AS geom,"highway","service","tunnel" FROM (select way,highway, case when tunnel in ('yes','true','1') then 'yes'::text else 'no'::text end as tunnel, case when service in ('parking_aisle','drive-through','driveway') then 'INT-minor'::text else 'INT-normal'::text end as service from planet_osm_line where highway in ('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','secondary_link','tertiary','tertiary_link','residential','unclassified','road','service','pedestrian','raceway','living_street') order by z_order) as roads WHERE "way" && <bbox>
* probable originating layers: none found
zoom count min time avg time max time
-----------------------------------------
14 4 0.3s 0.3s 0.4s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment