API tools faq
paste
Advertisement
khamooshi

Performance problem when using the openstreetmap-carto style

khamooshi
Nov 10th, 2015
522
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.95 KB | None | 0 0
raw download clone embed print report
  1. 2015-11-10 15:26:28.752 UTCSTATEMENT:  SELECT ST_AsBinary("way") AS geom,"landuse","natural","waterway" FROM (SELECT way,"natural",waterway,landuse,name
  2.               FROM planet_osm_polygon
  3.               WHERE (waterway IN ('dock','mill_pond','riverbank','canal')
  4.                  OR landuse IN ('reservoir','water','basin')
  5.                  OR "natural" IN ('lake','water','land','glacier','mud','bay'))
  6.                  AND building IS NULL
  7.               ORDER BY z_order,way_area DESC
  8.               ) AS water_areas WHERE "way" && ST_SetSRID('BOX3D(-136.4062500000068 39.90973623433025,-111.0937499999932 56.55948248355774)'::box3d, 4326)
  9. 2015-11-10 15:26:33.839 UTCLOG:  duration: 1885.918 ms  statement:
  10.     EXPLAIN analyze SELECT ST_AsBinary("way") AS geom,"landuse","natural","waterway" FROM (SELECT way,"natural",waterway,landuse,name
  11.               FROM planet_osm_polygon
  12.               WHERE (waterway IN ('dock','mill_pond','riverbank','canal')
  13.                  OR landuse IN ('reservoir','water','basin')
  14.                  OR "natural" IN ('lake','water','land','glacier','mud','bay'))
  15.                  AND building IS NULL
  16.               ORDER BY z_order,way_area DESC
  17.               ) AS water_areas WHERE "way" && ST_SetSRID('BOX3D(-136.4062500000068 39.90973623433025,-111.0937499999932 56.55948248355774)'::box3d, 4326)
  18. 2015-11-10 15:27:27.316 UTCLOG:  duration: 265.801 ms  statement:
  19.     EXPLAIN analyze SELECT ST_AsBinary("way") AS geom,"landuse","natural","waterway" FROM (SELECT way,"natural",waterway,landuse,name
  20.               FROM planet_osm_polygon
  21.               WHERE (waterway IN ('dock','mill_pond','riverbank','canal')
  22.                  OR landuse IN ('reservoir','water','basin')
  23.                  OR "natural" IN ('lake','water','land','glacier','mud','bay'))
  24.                  AND building IS NULL
  25.               ORDER BY z_order,way_area DESC
  26.               ) AS water_areas WHERE "way" && ST_SetSRID('BOX3D(-91.40624999954113 -1.406108835319754,-66.09374999952755 23.24134610251407)'::box3d, 4326)
  27. 2015-11-10 15:29:00.574 UTCLOG:  duration: 20125.471 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"landuse","military","natural" FROM (SELECT way,aeroway,amenity,landuse,leisure,man_made,military,"natural",POWER,tourism,name,highway,
  28.            CASE WHEN religion IN ('christian','jewish') THEN religion ELSE 'INT-generic'::text END AS religion
  29.            FROM planet_osm_polygon
  30.            WHERE landuse IS NOT NULL
  31.               OR leisure IS NOT NULL
  32.               OR aeroway IN ('apron','aerodrome')
  33.               OR amenity IN ('parking','university','college','school','hospital','kindergarten','grave_yard','prison')
  34.               OR military IN ('barracks','danger_area')
  35.               OR "natural" IN ('field','beach','desert','heath','mud','grassland','wood','sand','scrub')
  36.               OR POWER IN ('station','sub_station','generator')
  37.               OR tourism IN ('attraction','camp_site','caravan_site','picnic_site','zoo')
  38.               OR highway IN ('services','rest_area')
  39.            ORDER BY z_order,way_area DESC
  40.           ) AS leisure WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  41. 2015-11-10 15:29:02.433 UTCLOG:  duration: 1262.558 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"landuse","natural","waterway" FROM (SELECT way,"natural",waterway,landuse,name
  42.           FROM planet_osm_polygon
  43.           WHERE (waterway IN ('dock','mill_pond','riverbank','canal')
  44.              OR landuse IN ('reservoir','water','basin')
  45.              OR "natural" IN ('lake','water','land','glacier','mud','bay'))
  46.              AND building IS NULL
  47.           ORDER BY z_order,way_area DESC
  48.           ) AS water_areas WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  49. 2015-11-10 15:29:04.725 UTCLOG:  duration: 1026.004 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"waterway" FROM (SELECT way,waterway
  50.           FROM planet_osm_line
  51.           WHERE waterway='river'
  52.           ) AS water_lines WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  53. 2015-11-10 15:29:22.865 UTCLOG:  duration: 17923.403 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"highway","railway","tunnel" FROM (SELECT way,highway,
  54.            CASE WHEN tunnel IN ('yes','true','1') THEN 'yes'::text ELSE tunnel END AS tunnel,
  55.            CASE WHEN railway='preserved' AND service IN ('spur','siding','yard') THEN 'INT-preserved-ssy'::text ELSE railway END AS railway
  56.            FROM planet_osm_roads
  57.            WHERE highway IS NOT NULL
  58.               OR (railway IS NOT NULL AND railway!='preserved' AND (service IS NULL OR service NOT IN ('spur','siding','yard')))
  59.               OR railway='preserved'
  60.            ORDER BY z_order
  61.           ) AS roads WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  62. 2015-11-10 15:29:24.315 UTCLOG:  duration: 207.080 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"admin_level" FROM (SELECT way,admin_level
  63.            FROM planet_osm_roads
  64.            WHERE "boundary"='administrative'
  65.              AND (admin_level IS NULL OR admin_level NOT IN ('0','1','2','3','4','5','6','7','8'))
  66.            ) AS admin WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  67. 2015-11-10 15:29:24.848 UTCLOG:  duration: 292.032 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"name","place" FROM (SELECT way,place,name
  68.           FROM planet_osm_point
  69.           WHERE place IN ('city','metropolis','town','large_town','small_town')
  70.             AND (capital IS NULL OR capital != 'yes')
  71.           ) AS placenames WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  72. 2015-11-10 15:29:25.423 UTCLOG:  duration: 258.644 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"aeroway","name" FROM (SELECT *
  73.           FROM planet_osm_point
  74.           WHERE aeroway IN ('airport','aerodrome','helipad')
  75.              OR barrier IN ('bollard','gate','lift_gate','block')
  76.              OR highway IN ('mini_roundabout','gate')
  77.              OR man_made IN ('lighthouse','power_wind','windmill','mast')
  78.              OR (POWER='generator' AND ("generator:source"='wind' OR power_source='wind'))
  79.              OR "natural" IN ('peak','volcano','spring','tree','cave_entrance')
  80.              OR railway='level_crossing'
  81.           ) AS symbols WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  82. 2015-11-10 15:29:45.795 UTCLOG:  duration: 329.187 ms  statement: SELECT name, tags, highway, ST_AsText(way) AS way FROM planet_osm_line WHERE way && ST_SetSRID('BOX3D(-0.263671875 51.4950637817383,-0.24169921875 51.5087432861328)'::box3d, 4326) AND highway IN ('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','secondary_link','tertiary','residential','road','unclassified','service','living_street');
  83. 2015-11-10 15:29:54.620 UTCLOG:  duration: 29162.234 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"aeroway","name" FROM (SELECT *
  84.           FROM planet_osm_polygon
  85.           WHERE aeroway IN ('airport','aerodrome','helipad')
  86.              OR barrier IN ('bollard','gate','lift_gate','block')
  87.              OR highway IN ('mini_roundabout','gate')
  88.              OR man_made IN ('lighthouse','power_wind','windmill','mast')
  89.              OR (POWER='generator' AND ("generator:source"='wind' OR power_source='wind'))
  90.              OR "natural" IN ('peak','volcano','spring','tree')
  91.              OR railway='level_crossing'
  92.           ) AS symbols WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  93. 2015-11-10 15:29:55.286 UTCLOG:  duration: 662.172 ms  EXECUTE <unnamed>: SELECT ST_AsBinary("way") AS geom,"name","way_area" FROM (SELECT way,way_area,name,boundary FROM planet_osm_polygon WHERE boundary='national_park' AND building IS NULL) AS boundary WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!