khamooshi

Performance problem explain analyze

Nov 10th, 2015
203
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. EXPLAIN analyze SELECT ST_AsBinary("way") AS geom,"landuse","military","natural" FROM (SELECT way,aeroway,amenity,landuse,leisure,man_made,military,"natural",POWER,tourism,name,highway,
  2.            CASE WHEN religion IN ('christian','jewish') THEN religion ELSE 'INT-generic'::text END AS religion
  3.            FROM planet_osm_polygon
  4.            WHERE landuse IS NOT NULL
  5.               OR leisure IS NOT NULL
  6.               OR aeroway IN ('apron','aerodrome')
  7.               OR amenity IN ('parking','university','college','school','hospital','kindergarten','grave_yard','prison')
  8.               OR military IN ('barracks','danger_area')
  9.               OR "natural" IN ('field','beach','desert','heath','mud','grassland','wood','sand','scrub')
  10.               OR POWER IN ('station','sub_station','generator')
  11.               OR tourism IN ('attraction','camp_site','caravan_site','picnic_site','zoo')
  12.               OR highway IN ('services','rest_area')
  13.            ORDER BY z_order,way_area DESC
  14.           ) AS leisure WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  15.  
  16.  
  17.  
  18. Subquery Scan ON leisure  (cost=560972.26..566486.72 ROWS=100263 width=296) (actual TIME=795.796..944.726 ROWS=109992 loops=1)
  19.   ->  Sort  (cost=560972.26..561222.91 ROWS=100263 width=391) (actual TIME=795.762..830.641 ROWS=109992 loops=1)
  20.         Sort KEY: planet_osm_polygon.z_order, planet_osm_polygon.way_area
  21.         Sort Method: quicksort  Memory: 66602kB
  22.         ->  Bitmap Heap Scan ON planet_osm_polygon  (cost=5552.37..552643.70 ROWS=100263 width=391) (actual TIME=57.694..282.032 ROWS=109992 loops=1)
  23.               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)))
  24.               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[])))
  25.               ROWS Removed BY FILTER: 52924
  26.               ->  Bitmap INDEX Scan ON planet_osm_polygon_leisure  (cost=0.00..5527.31 ROWS=149185 width=0) (actual TIME=38.468..38.468 ROWS=162916 loops=1)
  27.                     INDEX Cond: (way && '0103000020E6100000010000000500000078000000809656C09A378EE7625B444078000000809656C064C8550C19AB464088FFFFFF7F0155C064C8550C19AB464088FFFFFF7F0155C09A378EE7625B444078000000809656C09A378EE7625B4440'::geometry)
  28. Total runtime: 967.613 ms
  29.  
  30.  
  31.  
  32.  
  33. EXPLAIN analyze SELECT ST_AsBinary("way") AS geom,"aeroway","name" FROM (SELECT *
  34.           FROM planet_osm_polygon
  35.           WHERE aeroway IN ('airport','aerodrome','helipad')
  36.              OR barrier IN ('bollard','gate','lift_gate','block')
  37.              OR highway IN ('mini_roundabout','gate')
  38.              OR man_made IN ('lighthouse','power_wind','windmill','mast')
  39.              OR (POWER='generator' AND ("generator:source"='wind' OR power_source='wind'))
  40.              OR "natural" IN ('peak','volcano','spring','tree')
  41.              OR railway='level_crossing'
  42.           ) AS symbols WHERE "way" && ST_SetSRID('BOX3D(-90.35156250000171 40.71395582622718,-84.02343749999829 45.33670190991032)'::box3d, 4326)
  43.  
  44.  
  45.  
  46. Bitmap Heap Scan ON planet_osm_polygon  (cost=44733.09..2770447.55 ROWS=4646 width=300) (actual TIME=296.556..997.751 ROWS=234 loops=1)
  47.   Recheck Cond: (way && '0103000020E6100000010000000500000078000000809656C09A378EE7625B444078000000809656C064C8550C19AB464088FFFFFF7F0155C064C8550C19AB464088FFFFFF7F0155C09A378EE7625B444078000000809656C09A378EE7625B4440'::geometry)
  48.   FILTER: ((aeroway = ANY ('{airport,aerodrome,helipad}'::text[])) OR (barrier = ANY ('{bollard,gate,lift_gate,block}'::text[])) OR (highway = ANY ('{mini_roundabout,gate}'::text[])) OR (man_made = ANY ('{lighthouse,power_wind,windmill,mast}'::text[])) OR ((POWER = 'generator'::text) AND (("generator:source" = 'wind'::text) OR (power_source = 'wind'::text))) OR ("natural" = ANY ('{peak,volcano,spring,tree}'::text[])) OR (railway = 'level_crossing'::text))
  49.   ROWS Removed BY FILTER: 1174268
  50.   ->  Bitmap INDEX Scan ON planet_osm_polygon_index  (cost=0.00..44731.93 ROWS=917251 width=0) (actual TIME=253.408..253.408 ROWS=1174502 loops=1)
  51.         INDEX Cond: (way && '0103000020E6100000010000000500000078000000809656C09A378EE7625B444078000000809656C064C8550C19AB464088FFFFFF7F0155C064C8550C19AB464088FFFFFF7F0155C09A378EE7625B444078000000809656C09A378EE7625B4440'::geometry)
  52. Total runtime: 997.892 ms
RAW Paste Data