[postgis-users] Index question

[Ben Madin]

G'day all,

I am trying to pre-populate a routing table by creating a route from  
the road segment nearest the centroid of each of 520 shires to any  
other shire... oops, sounds awful, but potentially better than  
creating too many on the fly...

So I thought I could create a multilinestring by getting all the line  
segments, dumping and collecting them

so I created a query to find the start and end of the route, and  
create the desired geometry.

Then I was going to run it on the entire table....

but looking at the query analysis (and I don't think I fully  
understand them yet), there is an inexplicable gap between about 100  
ms (see the second Limit group):

                  ->  Limit  (cost=41.85..41.85 rows=1 width=547)  
(actual time=105.348..105.349 rows=1 loops=1)

  and the final time of around 1700ms - is this because of time being  
taken by the function?

          ->  Function Scan on dijkstra_sp_delta  (cost=83.71..346.21  
rows=1000 width=36) (actual time=1748.717..1753.921 rows=154 loops=1)

ie I don't really understand what happened between 105.349 and  
1748.717? but I want to update a large number of rows, so any saving  
would be great!

cheers

Ben

australia=# EXPLAIN ANALYZE
SELECT st_asEWKT(ST_Collect(rt.the_geom)) AS roadroute
FROM mrwa_net
JOIN
   (SELECT gid, (ST_Dump(the_geom)).geom AS the_geom
   FROM dijkstra_sp_delta(
     'mrwa_net',
     (SELECT m.source
       FROM mrwa_net m JOIN tailtagarea r
       ON st_dwithin(m.the_geom, r.c_geom, 2) AND r.shortcode like 'BE'
       ORDER BY st_distance(m.the_geom, r.c_geom) limit 1),
     (SELECT n.target
       FROM mrwa_net n JOIN tailtagarea a
       ON st_dwithin(n.the_geom, a.c_geom, 2) AND a.shortcode like 'GH'
       ORDER BY st_distance(n.the_geom, a.c_geom) limit 1),
     3000)
   ) as rt
ON mrwa_net.gid=rt.gid;

                                                                               QUERY 
  PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=6079.39..6079.40 rows=1 width=32) (actual  
time=1785.492..1785.492 rows=1 loops=1)
    ->  Nested Loop  (cost=83.71..6076.89 rows=1000 width=32) (actual  
time=1748.735..1755.042 rows=154 loops=1)
          ->  Function Scan on dijkstra_sp_delta  (cost=83.71..346.21  
rows=1000 width=36) (actual time=1748.717..1753.921 rows=154 loops=1)
                InitPlan
                  ->  Limit  (cost=41.85..41.85 rows=1 width=547)  
(actual time=11.887..11.887 rows=1 loops=1)
                        ->  Sort  (cost=41.85..41.85 rows=1 width=547)  
(actual time=11.887..11.887 rows=1 loops=1)
                              Sort Key: (st_distance(m.the_geom,  
r.c_geom))
                              Sort Method:  top-N heapsort  Memory: 25kB
                              ->  Nested Loop  (cost=0.00..41.84  
rows=1 width=547) (actual time=0.171..11.313 rows=1124 loops=1)
                                    Join Filter:  
(_st_dwithin(m.the_geom, r.c_geom, 2::double precision) AND (r.c_geom  
&& st_expand(m.the_geom, 2::double precision)))
                                    ->  Seq Scan on tailtagarea r   
(cost=0.00..33.50 rows=1 width=100) (actual time=0.044..0.400 rows=1  
loops=1)
                                          Filter: ((shortcode)::text  
~~ 'BE'::text)
                                    ->  Index Scan using idx_gist_geom  
on mrwa_net m  (cost=0.00..8.31 rows=1 width=447) (actual  
time=0.104..6.116 rows=1134 loops=1)
                                          Index Cond: (m.the_geom &&  
st_expand(r.c_geom, 2::double precision))
                                          Filter: (m.the_geom &&  
st_expand(r.c_geom, 2::double precision))
                  ->  Limit  (cost=41.85..41.85 rows=1 width=547)  
(actual time=105.348..105.349 rows=1 loops=1)
                        ->  Sort  (cost=41.85..41.85 rows=1 width=547)  
(actual time=105.346..105.346 rows=1 loops=1)
                              Sort Key: (st_distance(n.the_geom,  
a.c_geom))
                              Sort Method:  top-N heapsort  Memory: 25kB
                              ->  Nested Loop  (cost=0.00..41.84  
rows=1 width=547) (actual time=0.152..101.115 rows=11536 loops=1)
                                    Join Filter:  
(_st_dwithin(n.the_geom, a.c_geom, 2::double precision) AND (a.c_geom  
&& st_expand(n.the_geom, 2::double precision)))
                                    ->  Seq Scan on tailtagarea a   
(cost=0.00..33.50 rows=1 width=100) (actual time=0.023..0.376 rows=2  
loops=1)
                                          Filter: ((shortcode)::text  
~~ 'GH'::text)
                                    ->  Index Scan using idx_gist_geom  
on mrwa_net n  (cost=0.00..8.31 rows=1 width=447) (actual  
time=0.141..30.874 rows=6848 loops=2)
                                          Index Cond: (n.the_geom &&  
st_expand(a.c_geom, 2::double precision))
                                          Filter: (n.the_geom &&  
st_expand(a.c_geom, 2::double precision))
          ->  Index Scan using mrwa_net_pkey on mrwa_net   
(cost=0.00..5.71 rows=1 width=4) (actual time=0.005..0.006 rows=1  
loops=154)
                Index Cond: (mrwa_net.gid = dijkstra_sp_delta.gid)
  Total runtime: 1786.102 ms
(29 rows)

-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...