[postgis-users] optimizeing a query

[Jürgen Mischker]

I have a query, which takes "ages":

SELECT a.area, sum(length(r.the_geom))/1000 AS km FROM protected_area as a, roads as r
WHERE r.the_geom && (SELECT the_geom from protected_area where area='protect1') 
AND contains(a.the_geom, r.the_geom)
GROUP BY r.area;

EXPLAIN ANALYZE:
--------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=8.45..8.45 rows=3 width=214) (actual time=69046.653..69046.660 rows=6 loops=1)
   Sort Key: (sum(length(r.the_geom)) / 1000::double precision)
   InitPlan
     ->  Seq Scan on protected_area  (cost=0.00..1.13 rows=1 width=67441) (actual time=0.045..0.058 rows=1 loops=1)
           Filter: ((area)::text = 'protect1'::text)
   ->  HashAggregate  (cost=7.25..7.30 rows=3 width=214) (actual time=69046.536..69046.549 rows=6 loops=1)
         ->  Nested Loop  (cost=0.00..7.23 rows=3 width=214) (actual time=55.716..68996.660 rows=1293 loops=1)
               Join Filter: within("outer".the_geom, "inner".the_geom)
               ->  Index Scan using idx_roads on roads r  (cost=0.00..6.01 rows=1 width=201) (actual time=4.982..896.924 rows=1331 loops=1)
                     Index Cond: (the_geom && $0)
                     Filter: (the_geom && $0)
               ->  Seq Scan on protected_areas l  (cost=0.00..1.10 rows=10 width=67454) (actual time=0.005..0.050 rows=10 loops=1331)
 Total runtime: 69047.185 ms
(13 rows)

the number of elements (npoints()) returned by this query is about 15.000 points, anyway maybe there is a way to optimize this query.

Cheers for all your ideas.
juergen

DDI Jürgen Mischker
Insitut f. Vermessung, Fernerkundung und Landinformation,
Universität für Bodenkultur, BOKU, Wien
Institute of Surveying, Remote Sensing and Land Information.
University of Natural Resources and Applied Life Science, Vienna.
Tel.: +43 1 47654-5132
Fax.: +43 1 47654-5142
email: jmischker at boku.ac.at