Explain Analyze Results

bad_recursion.sql

                                                                                 QUERY PLAN                                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2889817.55..2895073.57 rows=2102405 width=68) (actual time=345.103..360.131 rows=78331 loops=1)
   Sort Key: search_graph.depth
   Sort Method: external merge  Disk: 4224kB
   CTE search_graph
     ->  Recursive Union  (cost=13075.32..2162313.09 rows=2102405 width=56) (actual time=59.622..257.259 rows=78331 loops=1)
           ->  Merge Right Join  (cost=13075.32..18806.76 rows=61235 width=52) (actual time=59.619..97.254 rows=61235 loops=1)
                 Merge Cond: (e.vertex_0 = v.id)
                 ->  Index Scan using edges_vertex_0_type_idx on edges e  (cost=0.00..3669.65 rows=17128 width=32) (actual time=0.710..10.818 rows=17096 loops=1)
                       Index Cond: (edge_type_id = 0)
                 ->  Sort  (cost=13075.32..13228.40 rows=61235 width=36) (actual time=58.896..65.662 rows=61235 loops=1)
                       Sort Key: v.id
                       Sort Method: quicksort  Memory: 6862kB
                       ->  Seq Scan on vertices v  (cost=0.00..8206.50 rows=61235 width=36) (actual time=0.031..19.469 rows=61235 loops=1)
           ->  Hash Join  (cost=25083.35..172302.53 rows=204117 width=56) (actual time=54.177..67.667 rows=8548 loops=2)
                 Hash Cond: (sg.parent = v.id)
                 ->  WorkTable Scan on search_graph sg  (cost=0.00..124000.88 rows=204117 width=20) (actual time=0.006..8.142 rows=39166 loops=2)
                       Filter: (depth < 5)
                 ->  Hash  (cost=18806.76..18806.76 rows=61235 width=52) (actual time=103.976..103.976 rows=61235 loops=1)
                       Buckets: 8192  Batches: 1  Memory Usage: 4305kB
                       ->  Merge Right Join  (cost=13075.32..18806.76 rows=61235 width=52) (actual time=52.123..84.734 rows=61235 loops=1)
                             Merge Cond: (e.vertex_0 = v.id)
                             ->  Index Scan using edges_vertex_0_type_idx on edges e  (cost=0.00..3669.65 rows=17128 width=32) (actual time=0.117..6.302 rows=17096 loops=1)
                                   Index Cond: (edge_type_id = 0)
                             ->  Sort  (cost=13075.32..13228.40 rows=61235 width=36) (actual time=52.001..58.007 rows=61235 loops=1)
                                   Sort Key: v.id
                                   Sort Method: quicksort  Memory: 6862kB
                                   ->  Seq Scan on vertices v  (cost=0.00..8206.50 rows=61235 width=36) (actual time=0.015..16.342 rows=61235 loops=1)
   ->  CTE Scan on search_graph  (cost=0.00..420481.00 rows=2102405 width=68) (actual time=59.627..301.120 rows=78331 loops=1)
 Total runtime: 370.457 ms
(29 rows)