explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8xMp

Settings
# exclusive inclusive rows x rows loops node
1. 56,992.663 56,992.663 ↓ 0.0 0 1

CTE Scan on search_graph (cost=238,488.03..247,641.78 rows=704 width=57) (actual time=56,992.663..56,992.663 rows=0 loops=1)

  • Filter: (path[array_length(path, 1)] = 79579)
  • Rows Removed by Filter: 11082242
  • Buffers: shared hit=27571557, temp read=121594 written=243331
2.          

CTE search_graph

3. 6,789.425 42,457.621 ↓ 78.7 11,082,242 1

Recursive Union (cost=0.86..238,488.03 rows=140,827 width=57) (actual time=0.055..42,457.621 rows=11,082,242 loops=1)

  • Buffers: shared hit=27571557, temp read=121594 written=121594
4. 0.200 3.851 ↑ 1.3 501 1

Nested Loop (cost=0.86..2,321.98 rows=667 width=57) (actual time=0.051..3.851 rows=501 loops=1)

  • Buffers: shared hit=2619
5. 1.161 1.161 ↓ 1.4 415 1

Index Scan using treenode_connector_skeleton_id_idx on treenode_connector tc1 (cost=0.43..816.04 rows=295 width=24) (actual time=0.028..1.161 rows=415 loops=1)

  • Index Cond: (skeleton_id = 905634)
  • Filter: ((relation_id = 14) AND (project_id = 1))
  • Rows Removed by Filter: 306
  • Buffers: shared hit=590
6. 2.490 2.490 ↑ 2.0 1 415

Index Scan using treenode_connector_connector_id_idx on treenode_connector tc2 (cost=0.43..5.04 rows=2 width=24) (actual time=0.005..0.006 rows=1 loops=415)

  • Index Cond: (connector_id = tc1.connector_id)
  • Filter: ((relation_id = 8) AND (project_id = 1) AND (tc1.treenode_id <> treenode_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2029
7. 7,622.147 35,664.345 ↓ 263.5 3,693,914 3

Nested Loop (cost=0.86..22,771.64 rows=14,016 width=57) (actual time=508.091..11,888.115 rows=3,693,914 loops=3)

  • Buffers: shared hit=27568938, temp read=121594 written=1
8. 450.468 10,428.819 ↓ 135.4 838,732 3

Nested Loop (cost=0.43..17,984.48 rows=6,195 width=64) (actual time=508.083..3,476.273 rows=838,732 loops=3)

  • Buffers: shared hit=9430290, temp read=121594 written=1
9. 1,531.203 1,531.203 ↓ 4.4 4,838 3

WorkTable Scan on search_graph sg (cost=0.00..433.55 rows=1,112 width=48) (actual time=508.046..510.401 rows=4,838 loops=3)

  • Filter: ((NOT cycle) AND (depth < (max_depth + 1)))
  • Rows Removed by Filter: 3689243
  • Buffers: temp read=121594 written=1
10. 8,447.148 8,447.148 ↓ 28.8 173 14,514

Index Scan using treenode_connector_skeleton_id_idx on treenode_connector tc1_1 (cost=0.43..15.60 rows=6 width=24) (actual time=0.010..0.582 rows=173 loops=14,514)

  • Index Cond: (skeleton_id = sg.skid_b)
  • Filter: ((relation_id = 14) AND (project_id = 1))
  • Rows Removed by Filter: 876
  • Buffers: shared hit=9430290
11. 17,613.379 17,613.379 ↓ 2.0 4 2,516,197

Index Scan using treenode_connector_connector_id_idx on treenode_connector tc2_1 (cost=0.43..0.67 rows=2 width=24) (actual time=0.003..0.007 rows=4 loops=2,516,197)

  • Index Cond: (connector_id = tc1_1.connector_id)
  • Filter: ((relation_id = 8) AND (project_id = 1) AND (tc1_1.treenode_id <> treenode_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=18138648
Planning time : 1.225 ms
Execution time : 57,261.498 ms