secret
Last active

  • Download Gist
query_plan_a.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
gis=# explain analyze 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','bay'))
and building is null
order by z_order,way_area desc
) as water_areas WHERE "way" && ST_SetSRID('BOX3D(313086.0678561255 4696291.017841195,2817774.610704787 7200979.560689856)'::box3d, 900913);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan water_areas (cost=6751977.49..6759592.43 rows=507663 width=128) (actual time=339731.427..342057.974 rows=647857 loops=1)
-> Sort (cost=6751977.49..6753246.64 rows=507663 width=1036) (actual time=339731.257..340460.693 rows=647857 loops=1)
Sort Key: planet_osm_polygon.z_order, planet_osm_polygon.way_area
Sort Method: external merge Disk: 241560kB
-> Seq Scan on planet_osm_polygon (cost=0.00..5780749.50 rows=507663 width=1036) (actual time=27290.824..330357.245 rows=647857 loops=1)
Filter: ((building IS NULL) AND (way && '010300002031BF0D0001000000050000007F137C45F81B1341654F24C138EA51417F137C45F81B1341B557E2E334785B4110932B4E777F4541B557E2E334785B4110932B4E777F4541654F24C138EA51417F137C45F81B1341654F24C138EA5141'::geometry) AND ((waterway = ANY ('{dock,mill_pond,riverbank,canal}'::text[])) OR (landuse = ANY ('{reservoir,water,basin}'::text[])) OR ("natural" = ANY ('{lake,water,land,glacier,mud,bay}'::text[]))))
Total runtime: 342152.035 ms
(7 rows)
slow_query_a.sql
SQL
1 2 3 4 5 6 7 8
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','bay'))
and building is null
order by z_order,way_area desc
) as water_areas WHERE "way" && ST_SetSRID('BOX3D(313086.0678561255 4696291.017841195,2817774.610704787 7200979.560689856)'::box3d, 900913)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.