Advertisement
khamooshi

Performance problem when using the openstreetmap-carto style

Nov 10th, 2015
514
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.95 KB | None | 0 0
  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