Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I imported the planet OSM file into a PostgreSQL 9.2/PostGIS 1.5 server using osm2pgsql. I now want to select certain map features which lie within the bounding box formed by a countries borders, e.g. all rivers and lakes within Germany.

I've come up with a working query, but it's horribly slow:

osm=# EXPLAIN ANALYZE
WITH     bbox 
AS        (SELECT ST_Envelope(ST_Union(way)) AS way
        FROM    planet_osm_polygon
        WHERE     boundary = 'administrative' AND admin_level = '2' AND name = 'Deutschland')
SELECT    *                         
FROM    (                    
            (SELECT     way, tags, width, water, waterway   
            FROM        planet_osm_line
            WHERE waterway='river')
        UNION      
            (SELECT     way, tags, width, water, waterway
            FROM        planet_osm_polygon
            WHERE        water IS NOT NULL)
        ) AS q, bbox
WHERE    ST_Within(q.way, bbox.way);
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1558037.52..1676764.41 rows=1 width=192) (actual time=305373.041..311176.612 rows=20435 loops=1)
   Join Filter: ((planet_osm_line.way && bbox.way) AND _st_within(planet_osm_line.way, bbox.way))
   Rows Removed by Join Filter: 851602
   CTE bbox
     ->  Aggregate  (cost=18.27..18.28 rows=1 width=1005) (actual time=374.222..374.222 rows=1 loops=1)
           ->  Index Scan using planet_osm_polygon_boundary_admin_level_name_idx on planet_osm_polygon  (cost=0.00..18.26 rows=1 width=1005) (actual time=27.989..34.350 rows=8 loops=1)
                 Index Cond: ((boundary = 'administrative'::text) AND (admin_level = '2'::text) AND (name = 'Deutschland'::text))
   ->  CTE Scan on bbox  (cost=0.00..0.02 rows=1 width=32) (actual time=374.227..374.228 rows=1 loops=1)
   ->  Unique  (cost=1558019.24..1564213.69 rows=412963 width=1131) (actual time=299869.930..309268.592 rows=872037 loops=1)
         ->  Sort  (cost=1558019.24..1559051.65 rows=412963 width=1131) (actual time=299869.929..300244.843 rows=874853 loops=1)
               Sort Key: planet_osm_line.way, planet_osm_line.tags, planet_osm_line.width, planet_osm_line.water, planet_osm_line.waterway
               Sort Method: external sort  Disk: 693392kB
               ->  Append  (cost=0.00..1314829.27 rows=412963 width=1131) (actual time=64.278..204999.538 rows=874853 loops=1)
                     ->  Index Scan using planet_osm_line_waterway_idx on planet_osm_line  (cost=0.00..440480.43 rows=132130 width=1245) (actual time=64.278..142495.404 rows=595834 loops=1)
                           Index Cond: (waterway = 'river'::text)
                     ->  Bitmap Heap Scan on planet_osm_polygon  (cost=5271.85..870219.21 rows=280833 width=1078) (actual time=186.014..62347.033 rows=279019 loops=1)
                           Recheck Cond: (water IS NOT NULL)
                           ->  Bitmap Index Scan on planet_osm_polygon_water_idx  (cost=0.00..5201.64 rows=280833 width=0) (actual time=123.545..123.545 rows=279019 loops=1)
                                 Index Cond: (water IS NOT NULL)
 Total runtime: 311350.392 ms

I've already touched some PostgreSQL settings:

shared_buffers = 2GB
temp_buffers = 16MB
work_mem = 32MB

I tried to get rid of the external disk sort with a high value for work_mem and set work_mem = 768MB, but the disk sort is still in the query plan.

Is there a way to speed things up?

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.