explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FAzO

Settings
# exclusive inclusive rows x rows loops node
1. 2,065,102.545 2,065,102.545 ↓ 0.0 0 1

CTE Scan on search_graph (cost=9,799,429.07..11,115,902.69 rows=32,911,840 width=45) (actual time=2,065,102.545..2,065,102.545 rows=0 loops=1)

  • Output: search_graph.idkey, search_graph.idparent, search_graph.depth, search_graph.path, search_graph.cycle
  • Filter: search_graph.cycle
  • Rows Removed by Filter: 442,400,089
  • Buffers: shared hit=9,068,492 read=5,496, temp read=7,725,437 written=11,667,231
2.          

CTE search_graph

3. 271,814.768 1,719,360.564 ↓ 6.7 442,400,089 1

Recursive Union (cost=0.00..9,799,429.07 rows=65,823,681 width=45) (actual time=0.093..1,719,360.564 rows=442,400,089 loops=1)

  • Buffers: shared hit=9,068,492 read=5,496, temp read=7,725,437 written=7,733,073
4. 457.092 457.092 ↑ 1.0 675,051 1

Seq Scan on castoncast_local_8319.keypar g (cost=0.00..10,399.51 rows=675,051 width=45) (actual time=0.091..457.092 rows=675,051 loops=1)

  • Output: g.idkey, g.idparent, 1, ARRAY[g.idkey], false
  • Buffers: shared read=3,649
5. 491,723.584 1,447,088.704 ↓ 4.2 27,607,815 16

Merge Join (cost=593,294.06..847,255.59 rows=6,514,863 width=45) (actual time=32,922.252..90,443.044 rows=27,607,815 loops=16)

  • Output: g_1.idkey, g_1.idparent, (sg.depth + 1), (sg.path || g_1.idkey), (g_1.idkey = ANY (sg.path))
  • Merge Cond: (g_1.idkey = sg.idparent)
  • Buffers: shared hit=9,068,492 read=1,847, temp read=7,725,437 written=3,807,585
6. 6,211.328 6,211.328 ↑ 1.0 650,761 16

Index Scan using idx_keyparkey on castoncast_local_8319.keypar g_1 (cost=0.42..32,103.15 rows=675,051 width=8) (actual time=0.918..388.208 rows=650,761 loops=16)

  • Output: g_1.idkeypar, g_1.idkey, g_1.idparent, g_1.partyp
  • Buffers: shared hit=9,068,488 read=1,847
7. 122,579.792 949,153.792 ↓ 15.6 52,535,396 16

Materialize (cost=593,293.64..610,169.91 rows=3,375,255 width=40) (actual time=32,871.891..59,322.112 rows=52,535,396 loops=16)

  • Output: sg.depth, sg.path, sg.idparent
  • Buffers: shared hit=4, temp read=7,725,437 written=3,807,585
8. 553,257.696 826,574.000 ↓ 8.2 27,591,942 16

Sort (cost=593,293.64..601,731.77 rows=3,375,255 width=40) (actual time=32,700.111..51,660.875 rows=27,591,942 loops=16)

  • Output: sg.depth, sg.path, sg.idparent
  • Sort Key: sg.idparent
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=4, temp read=7,725,437 written=3,807,585
9. 273,316.304 273,316.304 ↓ 8.2 27,650,006 16

WorkTable Scan on search_graph sg (cost=0.00..135,010.20 rows=3,375,255 width=40) (actual time=0.318..17,082.269 rows=27,650,006 loops=16)

  • Output: sg.depth, sg.path, sg.idparent
  • Filter: (NOT sg.cycle)
  • Buffers: temp read=3,925,508 written=10