explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a22Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 186.588 ↑ 265,242.7 3 1

Append (cost=158,160.45..180,042.97 rows=795,728 width=64) (actual time=186.184..186.588 rows=3 loops=1)

  • Functions: 232
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 41.907 ms, Inlining 0.000 ms, Optimization 7.211 ms, Emission 176.562 ms, Total 225.680 ms
2.          

CTE nodes.draft

3. 0.000 0.071 ↑ 33.3 3 1

Hash Join (cost=20.84..45.03 rows=100 width=60) (actual time=0.067..0.071 rows=3 loops=1)

  • Hash Cond: ((_n.node ->> 'oid'::text) = _c.oid)
4.          

Initplan (for Hash Join)

5. 0.003 0.003 ↑ 1.0 1 1

Index Scan using flows_pkey on flows (cost=0.15..8.17 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (node_id = 4)
6. 0.014 0.014 ↑ 1.0 1 1

Index Scan using flows_pkey on flows flows_1 (cost=0.15..8.17 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (node_id = 4)
7. 0.008 0.027 ↑ 33.3 3 1

Hash Join (cost=2.25..22.70 rows=100 width=36) (actual time=0.025..0.027 rows=3 loops=1)

  • Hash Cond: (n_t.constant = (_n.node ->> 'type'::text))
8. 0.004 0.004 ↑ 90.0 7 1

Seq Scan on node_types n_t (cost=0.00..16.30 rows=630 width=36) (actual time=0.003..0.004 rows=7 loops=1)

9. 0.004 0.015 ↑ 33.3 3 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.014..0.015 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
10. 0.011 0.011 ↑ 33.3 3 1

Function Scan on jsonb_array_elements _n (cost=0.00..1.00 rows=100 width=32) (actual time=0.011..0.011 rows=3 loops=1)

11. 0.004 0.032 ↑ 33.3 3 1

Hash (cost=1.00..1.00 rows=100 width=64) (actual time=0.032..0.032 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.028 0.028 ↑ 33.3 3 1

Function Scan on jsonb_each _c (cost=0.00..1.00 rows=100 width=64) (actual time=0.028..0.028 rows=3 loops=1)

13.          

CTE edges.draft

14. 0.012 0.016 ↑ 50.0 2 1

Function Scan on jsonb_array_elements _t (cost=8.17..11.42 rows=100 width=34) (actual time=0.014..0.016 rows=2 loops=1)

15.          

Initplan (for Function Scan)

16. 0.004 0.004 ↑ 1.0 1 1

Index Scan using flows_pkey on flows flows_2 (cost=0.15..8.17 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (node_id = 4)
17.          

CTE edges.published

18. 1.687 1.687 ↑ 3,984.5 2 1

CTE Scan on _edges (cost=626.37..785.75 rows=7,969 width=22) (actual time=0.021..1.687 rows=2 loops=1)

19.          

CTE _edges

20. 0.008 1.684 ↑ 3,984.5 2 1

Recursive Union (cost=4.22..626.37 rows=7,969 width=22) (actual time=0.019..1.684 rows=2 loops=1)

21. 0.008 0.018 ↑ 9.0 1 1

Bitmap Heap Scan on edges e (cost=4.22..14.76 rows=9 width=22) (actual time=0.017..0.018 rows=1 loops=1)

  • Recheck Cond: (from_node_id = 4)
  • Heap Blocks: exact=1
22. 0.010 0.010 ↑ 9.0 1 1

Bitmap Index Scan on edges_from_node_id_to_node_id_sort_key (cost=0.00..4.22 rows=9 width=0) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (from_node_id = 4)
23. 0.014 1.658 ↓ 0.0 0 2

Hash Join (cost=2.92..45.22 rows=796 width=22) (actual time=0.829..0.829 rows=0 loops=2)

  • Hash Cond: (e_1.from_node_id = _t_1.to_node_id)
24. 0.012 0.012 ↑ 177.0 10 2

Seq Scan on edges e_1 (cost=0.00..27.70 rows=1,770 width=18) (actual time=0.004..0.006 rows=10 loops=2)

25. 0.006 1.632 ↑ 90.0 1 2

Hash (cost=1.80..1.80 rows=90 width=12) (actual time=0.816..0.816 rows=1 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 1.626 1.626 ↑ 90.0 1 2

WorkTable Scan on _edges _t_1 (cost=0.00..1.80 rows=90 width=12) (actual time=0.813..0.813 rows=1 loops=2)

27.          

CTE nodes.published

28. 0.014 186.114 ↑ 5,312.7 3 1

Hash Join (cost=661.69..1,022.57 rows=15,938 width=12) (actual time=186.028..186.114 rows=3 loops=1)

  • Hash Cond: (""edges.published"".from_node_id = n.id)
29. 0.120 1.815 ↑ 5,312.7 3 1

HashAggregate (cost=637.52..796.90 rows=15,938 width=12) (actual time=1.730..1.815 rows=3 loops=1)

  • Group Key: ""edges.published"".root_node_id, ""edges.published"".from_node_id
30. 0.002 1.695 ↑ 3,984.5 4 1

Append (cost=0.00..557.83 rows=15,938 width=12) (actual time=0.024..1.695 rows=4 loops=1)

31. 1.691 1.691 ↑ 3,984.5 2 1

CTE Scan on ""edges.published"" (cost=0.00..159.38 rows=7,969 width=12) (actual time=0.023..1.691 rows=2 loops=1)

32. 0.002 0.002 ↑ 3,984.5 2 1

CTE Scan on ""edges.published"" ""edges.published_1"" (cost=0.00..159.38 rows=7,969 width=12) (actual time=0.001..0.002 rows=2 loops=1)

33. 0.008 184.285 ↑ 63.0 10 1

Hash (cost=16.30..16.30 rows=630 width=8) (actual time=184.285..184.285 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 184.277 184.277 ↑ 63.0 10 1

Seq Scan on nodes n (cost=0.00..16.30 rows=630 width=8) (actual time=184.264..184.277 rows=10 loops=1)

35.          

CTE nodes.for_delete

36. 0.003 0.022 ↓ 0.0 0 1

Hash Join (cost=28.65..486.86 rows=7,969 width=8) (actual time=0.022..0.022 rows=0 loops=1)

  • Hash Cond: (_p.id = n_1.id)
37. 0.001 0.001 ↑ 5,312.7 3 1

CTE Scan on ""nodes.published"" _p (cost=0.00..318.76 rows=15,938 width=8) (actual time=0.000..0.001 rows=3 loops=1)

38. 0.003 0.018 ↑ 45.0 7 1

Hash (cost=24.71..24.71 rows=315 width=8) (actual time=0.018..0.018 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.007 0.015 ↑ 45.0 7 1

Hash Anti Join (cost=3.25..24.71 rows=315 width=8) (actual time=0.011..0.015 rows=7 loops=1)

  • Hash Cond: (n_1.oid = _d.oid)
40. 0.003 0.003 ↑ 63.0 10 1

Seq Scan on nodes n_1 (cost=0.00..16.30 rows=630 width=24) (actual time=0.001..0.003 rows=10 loops=1)

41. 0.002 0.005 ↑ 33.3 3 1

Hash (cost=2.00..2.00 rows=100 width=16) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.003 0.003 ↑ 33.3 3 1

CTE Scan on ""nodes.draft"" _d (cost=0.00..2.00 rows=100 width=16) (actual time=0.001..0.003 rows=3 loops=1)

43.          

CTE edges.deleted_by_nodes

44. 0.001 0.002 ↓ 0.0 0 1

Delete on edges (cost=13.66..144,466.08 rows=140,699 width=38) (actual time=0.002..0.002 rows=0 loops=1)

45. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=13.66..144,466.08 rows=140,699 width=38) (actual time=0.001..0.001 rows=0 loops=1)

46. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on ""nodes.for_delete"" _t_2 (cost=0.00..159.38 rows=7,969 width=40) (actual time=0.001..0.001 rows=0 loops=1)

47. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on edges (cost=13.66..17.93 rows=18 width=22) (never executed)

  • Recheck Cond: ((_t_2.id = from_node_id) OR (_t_2.id = to_node_id))
48. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=13.66..13.66 rows=18 width=0) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on edges_from_node_id_to_node_id_sort_key (cost=0.00..0.22 rows=9 width=0) (never executed)

  • Index Cond: (from_node_id = _t_2.id)
50. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on edges_from_node_id_to_node_id_sort_key (cost=0.00..13.43 rows=9 width=0) (never executed)

  • Index Cond: (to_node_id = _t_2.id)
51.          

CTE edges.deleted

52. 0.000 0.061 ↓ 0.0 0 1

Delete on edges edges_1 (cost=59.13..285.05 rows=1 width=122) (actual time=0.061..0.061 rows=0 loops=1)

53. 0.002 0.061 ↓ 0.0 0 1

Nested Loop Anti Join (cost=59.13..285.05 rows=1 width=122) (actual time=0.061..0.061 rows=0 loops=1)

  • Join Filter: ((_d_1.from_oid = n_from.oid) AND (_d_1.to_oid = n_to.oid))
  • Rows Removed by Join Filter: 1
54. 0.005 0.055 ↓ 2.0 2 1

Nested Loop (cost=59.13..281.55 rows=1 width=98) (actual time=0.048..0.055 rows=2 loops=1)

  • Join Filter: (e_2.to_node_id = n_to.id)
55. 0.005 0.044 ↓ 2.0 2 1

Nested Loop (cost=58.98..281.36 rows=1 width=100) (actual time=0.039..0.044 rows=2 loops=1)

  • Join Filter: (e_2.from_node_id = n_from.id)
56. 0.006 0.035 ↓ 2.0 2 1

Nested Loop (cost=58.83..281.17 rows=1 width=102) (actual time=0.032..0.035 rows=2 loops=1)

57. 0.012 0.025 ↑ 1.0 2 1

Hash Join (cost=58.67..280.81 rows=2 width=84) (actual time=0.023..0.025 rows=2 loops=1)

  • Hash Cond: ((_p_1.from_node_id = edges_1.from_node_id) AND (_p_1.to_node_id = edges_1.to_node_id) AND (_p_1.sort = edges_1.sort))
58. 0.004 0.004 ↑ 3,984.5 2 1

CTE Scan on ""edges.published"" _p_1 (cost=0.00..159.38 rows=7,969 width=60) (actual time=0.004..0.004 rows=2 loops=1)

59. 0.005 0.009 ↑ 177.0 10 1

Hash (cost=27.70..27.70 rows=1,770 width=24) (actual time=0.009..0.009 rows=10 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
60. 0.004 0.004 ↑ 177.0 10 1

Seq Scan on edges edges_1 (cost=0.00..27.70 rows=1,770 width=24) (actual time=0.003..0.004 rows=10 loops=1)

61. 0.004 0.004 ↑ 1.0 1 2

Index Scan using edges_from_node_id_to_node_id_sort_key on edges e_2 (cost=0.15..0.18 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=2)

  • Index Cond: ((from_node_id = _p_1.from_node_id) AND (to_node_id = _p_1.to_node_id) AND (sort = _p_1.sort))
62. 0.004 0.004 ↑ 1.0 1 2

Index Scan using nodes_pkey on nodes n_from (cost=0.15..0.17 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=2)

  • Index Cond: (id = _p_1.from_node_id)
63. 0.006 0.006 ↑ 1.0 1 2

Index Scan using nodes_pkey on nodes n_to (cost=0.15..0.17 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (id = _p_1.to_node_id)
64. 0.004 0.004 ↑ 50.0 2 2

CTE Scan on ""edges.draft"" _d_1 (cost=0.00..2.00 rows=100 width=88) (actual time=0.002..0.002 rows=2 loops=2)

65.          

CTE nodes.deleted

66. 0.001 0.024 ↓ 0.0 0 1

Delete on nodes (cost=24.18..204.62 rows=7,969 width=38) (actual time=0.024..0.024 rows=0 loops=1)

67. 0.000 0.023 ↓ 0.0 0 1

Hash Join (cost=24.18..204.62 rows=7,969 width=38) (actual time=0.023..0.023 rows=0 loops=1)

  • Hash Cond: (_t_3.id = nodes.id)
68. 0.023 0.023 ↓ 0.0 0 1

CTE Scan on ""nodes.for_delete"" _t_3 (cost=0.00..159.38 rows=7,969 width=40) (actual time=0.023..0.023 rows=0 loops=1)

69. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.30..16.30 rows=630 width=14) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Seq Scan on nodes (cost=0.00..16.30 rows=630 width=14) (never executed)

71.          

CTE nodes.new

72. 0.000 0.036 ↓ 0.0 0 1

Insert on nodes nodes_1 (cost=584.28..588.41 rows=50 width=100) (actual time=0.036..0.036 rows=0 loops=1)

73. 0.016 0.036 ↓ 0.0 0 1

Hash Anti Join (cost=584.28..588.41 rows=50 width=100) (actual time=0.035..0.036 rows=0 loops=1)

  • Hash Cond: (_d_2.oid = n_2.oid)
74. 0.001 0.001 ↑ 33.3 3 1

CTE Scan on ""nodes.draft"" _d_2 (cost=0.00..2.00 rows=100 width=60) (actual time=0.000..0.001 rows=3 loops=1)

75. 0.002 0.019 ↑ 5,312.7 3 1

Hash (cost=385.05..385.05 rows=15,938 width=16) (actual time=0.019..0.019 rows=3 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 129kB
76. 0.007 0.017 ↑ 5,312.7 3 1

Hash Join (cost=24.18..385.05 rows=15,938 width=16) (actual time=0.016..0.017 rows=3 loops=1)

  • Hash Cond: (_p_2.id = n_2.id)
77. 0.001 0.001 ↑ 5,312.7 3 1

CTE Scan on ""nodes.published"" _p_2 (cost=0.00..318.76 rows=15,938 width=8) (actual time=0.000..0.001 rows=3 loops=1)

78. 0.003 0.009 ↑ 63.0 10 1

Hash (cost=16.30..16.30 rows=630 width=24) (actual time=0.009..0.009 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
79. 0.006 0.006 ↑ 63.0 10 1

Seq Scan on nodes n_2 (cost=0.00..16.30 rows=630 width=24) (actual time=0.004..0.006 rows=10 loops=1)

80.          

CTE nodes.updated

81. 0.035 186.273 ↑ 2,656.3 3 1

Update on nodes nodes_2 (cost=29.00..487.22 rows=7,969 width=222) (actual time=186.175..186.273 rows=3 loops=1)

82. 0.009 186.238 ↑ 2,656.3 3 1

Hash Join (cost=29.00..487.22 rows=7,969 width=222) (actual time=186.150..186.238 rows=3 loops=1)

  • Hash Cond: (_p_3.id = nodes_2.id)
83. 186.122 186.122 ↑ 5,312.7 3 1

CTE Scan on ""nodes.published"" _p_3 (cost=0.00..318.76 rows=15,938 width=40) (actual time=186.034..186.122 rows=3 loops=1)

84. 0.004 0.107 ↑ 105.0 3 1

Hash (cost=25.06..25.06 rows=315 width=190) (actual time=0.107..0.107 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
85. 0.010 0.103 ↑ 105.0 3 1

Hash Join (cost=3.25..25.06 rows=315 width=190) (actual time=0.101..0.103 rows=3 loops=1)

  • Hash Cond: (nodes_2.oid = _d_3.oid)
86. 0.010 0.010 ↑ 63.0 10 1

Seq Scan on nodes nodes_2 (cost=0.00..16.30 rows=630 width=62) (actual time=0.007..0.010 rows=10 loops=1)

87. 0.003 0.083 ↑ 33.3 3 1

Hash (cost=2.00..2.00 rows=100 width=144) (actual time=0.083..0.083 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
88. 0.080 0.080 ↑ 33.3 3 1

CTE Scan on ""nodes.draft"" _d_3 (cost=0.00..2.00 rows=100 width=144) (actual time=0.073..0.080 rows=3 loops=1)

89.          

CTE edges.new

90. 0.011 0.172 ↓ 0.0 0 1

Insert on edges edges_2 (cost=1,428.52..9,777.45 rows=639,040 width=18) (actual time=0.172..0.172 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Tuples Inserted: 0
  • Conflicting Tuples: 2
91.          

CTE _nodes

92. 0.093 0.115 ↑ 5,329.3 3 1

HashAggregate (cost=705.81..865.69 rows=15,988 width=24) (actual time=0.037..0.115 rows=3 loops=1)

  • Group Key: n_3.id, n_3.oid
93. 0.001 0.022 ↑ 5,329.3 3 1

Append (cost=24.18..625.87 rows=15,988 width=24) (actual time=0.019..0.022 rows=3 loops=1)

94. 0.007 0.020 ↑ 5,312.7 3 1

Hash Join (cost=24.18..385.05 rows=15,938 width=24) (actual time=0.019..0.020 rows=3 loops=1)

  • Hash Cond: (_p_4.id = n_3.id)
95. 0.001 0.001 ↑ 5,312.7 3 1

CTE Scan on ""nodes.published"" _p_4 (cost=0.00..318.76 rows=15,938 width=8) (actual time=0.000..0.001 rows=3 loops=1)

96. 0.003 0.012 ↑ 63.0 10 1

Hash (cost=16.30..16.30 rows=630 width=24) (actual time=0.012..0.012 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
97. 0.009 0.009 ↑ 63.0 10 1

Seq Scan on nodes n_3 (cost=0.00..16.30 rows=630 width=24) (actual time=0.008..0.009 rows=10 loops=1)

98. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on ""nodes.new"" ""nodes.new_1"" (cost=0.00..1.00 rows=50 width=24) (actual time=0.001..0.001 rows=0 loops=1)

99. 0.010 0.161 ↑ 319,520.0 2 1

Hash Join (cost=562.83..8,911.76 rows=639,040 width=18) (actual time=0.160..0.161 rows=2 loops=1)

  • Hash Cond: (_n_to.oid = _d_4.to_oid)
100. 0.038 0.038 ↑ 5,329.3 3 1

CTE Scan on _nodes _n_to (cost=0.00..319.76 rows=15,988 width=24) (actual time=0.038..0.038 rows=3 loops=1)

101. 0.005 0.113 ↑ 3,997.0 2 1

Hash (cost=462.91..462.91 rows=7,994 width=26) (actual time=0.112..0.113 rows=2 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 65kB
102. 0.006 0.108 ↑ 3,997.0 2 1

Hash Join (cost=3.25..462.91 rows=7,994 width=26) (actual time=0.070..0.108 rows=2 loops=1)

  • Hash Cond: (_n_from.oid = _d_4.from_oid)
103. 0.080 0.080 ↑ 5,329.3 3 1

CTE Scan on _nodes _n_from (cost=0.00..319.76 rows=15,988 width=24) (actual time=0.001..0.080 rows=3 loops=1)

104. 0.002 0.022 ↑ 50.0 2 1

Hash (cost=2.00..2.00 rows=100 width=34) (actual time=0.022..0.022 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
105. 0.020 0.020 ↑ 50.0 2 1

CTE Scan on ""edges.draft"" _d_4 (cost=0.00..2.00 rows=100 width=34) (actual time=0.017..0.020 rows=2 loops=1)

106. 186.287 186.287 ↑ 2,656.3 3 1

CTE Scan on ""nodes.updated"" (cost=0.00..179.30 rows=7,969 width=64) (actual time=186.183..186.287 rows=3 loops=1)

107. 0.037 0.037 ↓ 0.0 0 1

CTE Scan on ""nodes.new"" (cost=0.00..1.12 rows=50 width=64) (actual time=0.037..0.037 rows=0 loops=1)

108. 0.025 0.025 ↓ 0.0 0 1

CTE Scan on ""nodes.deleted"" (cost=0.00..179.30 rows=7,969 width=64) (actual time=0.025..0.025 rows=0 loops=1)

109. 0.173 0.173 ↓ 0.0 0 1

CTE Scan on ""edges.new"" (cost=0.00..14,378.40 rows=639,040 width=64) (actual time=0.173..0.173 rows=0 loops=1)

110. 0.062 0.062 ↓ 0.0 0 1

CTE Scan on ""edges.deleted"" (cost=0.00..0.02 rows=1 width=64) (actual time=0.062..0.062 rows=0 loops=1)

111. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on ""edges.deleted_by_nodes"" (cost=0.00..3,165.73 rows=140,699 width=64) (actual time=0.002..0.002 rows=0 loops=1)

Planning time : 4.112 ms
Execution time : 229.743 ms