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

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.