explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9XOk

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 134.589 ↑ 201.0 1 1

Append (cost=17,366,098.62..17,366,126.16 rows=201 width=32) (actual time=134.588..134.589 rows=1 loops=1)

2.          

CTE discovery

3. 0.119 1.302 ↑ 4.5 199 1

Recursive Union (cost=0.18..108.29 rows=903 width=12) (actual time=0.010..1.302 rows=199 loops=1)

4. 0.000 0.010 ↑ 1.5 2 1

Subquery Scan on calls (cost=0.18..0.24 rows=3 width=12) (actual time=0.010..0.010 rows=2 loops=1)

5. 0.003 0.010 ↑ 1.5 2 1

HashAggregate (cost=0.18..0.21 rows=3 width=8) (actual time=0.009..0.010 rows=2 loops=1)

  • Group Key: (1), ($7)
6.          

CTE pred_1

7. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.01..0.02 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=1)

8.          

Initplan (forResult)

9. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

10.          

CTE pred_2

11. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.01..0.02 rows=1 width=1) (actual time=0.000..0.001 rows=1 loops=1)

12.          

Initplan (forResult)

13. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

14. 0.000 0.007 ↑ 1.5 2 1

Append (cost=0.01..0.12 rows=3 width=8) (actual time=0.005..0.007 rows=2 loops=1)

15. 0.004 0.004 ↓ 0.0 0 1

CTE Scan on pred_1 p (cost=0.01..0.03 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: v
  • Rows Removed by Filter: 1
16.          

Initplan (forCTE Scan)

17. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=4) (never executed)

18. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on pred_2 p_1 (cost=0.01..0.03 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: v
19.          

Initplan (forCTE Scan)

20. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

21. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on pred_2 p_2 (cost=0.01..0.03 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

  • Filter: v
22.          

Initplan (forCTE Scan)

23. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

24. 0.178 1.173 ↑ 11.2 8 51

Nested Loop (cost=0.19..9.00 rows=90 width=12) (actual time=0.005..0.023 rows=8 loops=51)

25. 0.000 0.000 ↑ 7.5 4 51

WorkTable Scan on discovery d (cost=0.00..0.60 rows=30 width=4) (actual time=0.000..0.000 rows=4 loops=51)

26. 0.199 0.995 ↑ 1.5 2 199

HashAggregate (cost=0.19..0.22 rows=3 width=8) (actual time=0.005..0.005 rows=2 loops=199)

  • Group Key: (1), ($15)
27.          

CTE pred_1

28. 0.000 0.000 ↑ 1.0 1 199

Result (cost=0.01..0.03 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=199)

29.          

Initplan (forResult)

30. 0.000 0.000 ↑ 1.0 1 199

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=199)

31.          

CTE pred_2

32. 0.000 0.000 ↑ 1.0 1 199

Result (cost=0.01..0.03 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=199)

33.          

Initplan (forResult)

34. 0.000 0.000 ↑ 1.0 1 199

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=199)

35. 0.199 0.796 ↑ 1.5 2 199

Append (cost=0.01..0.12 rows=3 width=8) (actual time=0.002..0.004 rows=2 loops=199)

36. 0.199 0.199 ↓ 0.0 0 199

CTE Scan on pred_1 p_3 (cost=0.01..0.03 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=199)

  • Filter: v
  • Rows Removed by Filter: 1
37.          

Initplan (forCTE Scan)

38. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

39. 0.199 0.199 ↑ 1.0 1 199

CTE Scan on pred_2 p_4 (cost=0.01..0.03 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=199)

  • Filter: v
  • Rows Removed by Filter: 0
40.          

Initplan (forCTE Scan)

41. 0.000 0.000 ↑ 1.0 1 195

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=195)

42. 0.199 0.199 ↑ 1.0 1 199

CTE Scan on pred_2 p_5 (cost=0.01..0.03 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=199)

  • Filter: v
  • Rows Removed by Filter: 0
43.          

Initplan (forCTE Scan)

44. 0.000 0.000 ↑ 1.0 1 195

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=195)

45.          

CTE base_cases

46. 4.327 4.328 ↑ 100.0 1 1

CTE Scan on d d_2 (cost=11,043.69..11,050.19 rows=100 width=36) (actual time=4.327..4.328 rows=1 loops=1)

  • Filter: (SubPlan 18)
47.          

CTE d

48. 0.001 4.325 ↑ 200.0 1 1

Unique (cost=11,041.42..11,043.68 rows=200 width=4) (actual time=4.325..4.325 rows=1 loops=1)

49. 0.007 4.324 ↑ 226.0 2 1

Sort (cost=11,041.42..11,042.55 rows=452 width=4) (actual time=4.324..4.324 rows=2 loops=1)

  • Sort Key: d_1.param_1
  • Sort Method: quicksort Memory: 25kB
50. 1.499 4.317 ↑ 226.0 2 1

Nested Loop Anti Join (cost=0.00..11,021.49 rows=452 width=4) (actual time=4.288..4.317 rows=2 loops=1)

  • Join Filter: (NOT (d2.in_1 IS DISTINCT FROM d_1.param_1))
  • Rows Removed by Join Filter: 20196
51. 0.032 0.032 ↑ 4.5 199 1

CTE Scan on discovery d_1 (cost=0.00..18.06 rows=903 width=4) (actual time=0.011..0.032 rows=199 loops=1)

52. 2.786 2.786 ↑ 8.9 102 199

CTE Scan on discovery d2 (cost=0.00..18.06 rows=903 width=4) (actual time=0.000..0.014 rows=102 loops=199)

53.          

Initplan (forCTE Scan)

54. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

55.          

SubPlan (forCTE Scan)

56. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

57.          

CTE termination_check

58. 0.000 0.002 ↑ 101.0 1 1

Recursive Union (cost=0.00..4.43 rows=101 width=32) (actual time=0.001..0.002 rows=1 loops=1)

59. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

60. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.04..0.24 rows=10 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: ((NOT $22) AND $23)
61.          

Initplan (forResult)

62. 0.000 0.000 ↑ 100.0 1 1

CTE Scan on base_cases (cost=0.00..2.00 rows=100 width=0) (actual time=0.000..0.000 rows=1 loops=1)

63. 0.000 0.000 ↓ 0.0 0

CTE Scan on discovery (cost=0.00..18.06 rows=903 width=0) (never executed)

64. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on termination_check (cost=0.04..0.24 rows=10 width=32) (never executed)

65.          

CTE evaluation

66. 0.696 133.500 ↑ 198.1 5,151 1

Recursive Union (cost=0.00..17,331,665.49 rows=1,020,270 width=36) (actual time=4.331..133.500 rows=5,151 loops=1)

67. 0.000 4.332 ↑ 10,100.0 1 1

Nested Loop (cost=0.00..205.01 rows=10,100 width=36) (actual time=4.330..4.332 rows=1 loops=1)

68. 4.329 4.329 ↑ 100.0 1 1

CTE Scan on base_cases bc (cost=0.00..2.00 rows=100 width=36) (actual time=4.328..4.329 rows=1 loops=1)

69. 0.003 0.003 ↑ 101.0 1 1

CTE Scan on termination_check t (cost=0.00..2.02 rows=101 width=0) (actual time=0.001..0.003 rows=1 loops=1)

70. 0.404 128.472 ↑ 1,980.7 51 101

Append (cost=1,727,065.14..1,731,105.51 rows=101,017 width=36) (actual time=1.257..1.272 rows=51 loops=101)

71.          

CTE e

72. 0.303 0.303 ↑ 1,980.4 51 101

WorkTable Scan on evaluation (cost=0.00..2,020.00 rows=101,000 width=36) (actual time=0.000..0.003 rows=51 loops=101)

73.          

CTE callgraph_results

74. 22.220 125.644 ↑ 76,002.7 3 101

Nested Loop (cost=0.00..1,714,758.68 rows=228,008 width=44) (actual time=1.233..1.244 rows=3 loops=101)

  • Join Filter: (NOT (e_1.in_1 IS DISTINCT FROM d_3.param_1))
  • Rows Removed by Join Filter: 3397
75. 0.404 0.404 ↑ 1,980.4 51 101

CTE Scan on e e_1 (cost=0.00..2,020.00 rows=101,000 width=36) (actual time=0.000..0.004 rows=51 loops=101)

76. 13.433 103.020 ↑ 6.7 67 5,151

Materialize (cost=0.00..1,027,959.81 rows=452 width=12) (actual time=0.001..0.020 rows=67 loops=5,151)

77. 47.773 89.587 ↑ 4.5 100 101

Nested Loop Anti Join (cost=0.00..1,027,957.55 rows=452 width=12) (actual time=0.025..0.887 rows=100 loops=101)

  • Join Filter: (NOT (e_2.in_1 IS DISTINCT FROM d_3.in_1))
  • Rows Removed by Join Filter: 6799
78. 1.616 1.616 ↑ 4.5 199 101

CTE Scan on discovery d_3 (cost=0.00..18.06 rows=903 width=12) (actual time=0.000..0.016 rows=199 loops=101)

79. 40.198 40.198 ↑ 2,885.7 35 20,099

CTE Scan on e e_2 (cost=0.00..2,020.00 rows=101,000 width=4) (actual time=0.000..0.002 rows=35 loops=20,099)

80.          

CTE new_results

81. 0.101 126.957 ↑ 17.0 1 101

Append (cost=4,903.05..10,286.46 rows=17 width=36) (actual time=1.255..1.257 rows=1 loops=101)

82. 0.000 125.947 ↓ 0.0 0 101

Subquery Scan on arg_res (cost=4,903.05..4,943.09 rows=6 width=36) (actual time=1.247..1.247 rows=0 loops=101)

  • Filter: (arg_res.res_count = 1)
83. 0.000 125.947 ↓ 0.0 0 101

WindowAgg (cost=4,903.05..4,928.70 rows=1,140 width=48) (actual time=1.247..1.247 rows=0 loops=101)

84. 0.202 125.947 ↓ 0.0 0 101

Sort (cost=4,903.05..4,905.90 rows=1,140 width=40) (actual time=1.247..1.247 rows=0 loops=101)

  • Sort Key: r.in_1, r.call_site
  • Sort Method: quicksort Memory: 25kB
85. 125.745 125.745 ↓ 0.0 0 101

CTE Scan on callgraph_results r (cost=0.00..4,845.17 rows=1,140 width=40) (actual time=1.245..1.245 rows=0 loops=101)

  • Filter: (call_site = ANY ('{1}'::integer[]))
  • Rows Removed by Filter: 3
86.          

SubPlan (forSubquery Scan)

87. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.01..0.02 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

88.          

Initplan (forResult)

89. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

90. 0.103 0.909 ↑ 11.0 1 101

Subquery Scan on arg_res_1 (cost=5,257.34..5,343.20 rows=11 width=36) (actual time=0.008..0.009 rows=1 loops=101)

  • Filter: (arg_res_1.res_count = 2)
  • Rows Removed by Filter: 2
91. 0.404 0.707 ↑ 760.0 3 101

WindowAgg (cost=5,257.34..5,314.34 rows=2,280 width=80) (actual time=0.005..0.007 rows=3 loops=101)

92. 0.202 0.303 ↑ 760.0 3 101

Sort (cost=5,257.34..5,263.04 rows=2,280 width=40) (actual time=0.003..0.003 rows=3 loops=101)

  • Sort Key: r_1.in_1, r_1.call_site
  • Sort Method: quicksort Memory: 25kB
93. 0.101 0.101 ↑ 760.0 3 101

CTE Scan on callgraph_results r_1 (cost=0.00..5,130.18 rows=2,280 width=40) (actual time=0.000..0.001 rows=3 loops=101)

  • Filter: (call_site = ANY ('{2,3}'::integer[]))
  • Rows Removed by Filter: 0
94.          

SubPlan (forSubquery Scan)

95. 0.099 0.099 ↑ 1.0 1 99

Result (cost=0.02..0.03 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=99)

96.          

Initplan (forResult)

97. 0.000 0.000 ↑ 1.0 1 99

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=99)

98. 0.000 0.000 ↑ 1.0 1 99

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=99)

99. 0.510 127.866 ↑ 2,020.0 50 101

Nested Loop (cost=0.00..3,030.03 rows=101,000 width=36) (actual time=1.257..1.266 rows=50 loops=101)

100. 0.000 126.856 ↑ 1.0 1 101

Limit (cost=0.00..0.02 rows=1 width=32) (actual time=1.256..1.256 rows=1 loops=101)

101. 126.856 126.856 ↑ 17.0 1 101

CTE Scan on new_results new_results_1 (cost=0.00..0.34 rows=17 width=32) (actual time=1.256..1.256 rows=1 loops=101)

102. 0.500 0.500 ↑ 2,020.0 50 100

CTE Scan on e e_3 (cost=0.00..2,020.00 rows=101,000 width=36) (actual time=0.000..0.005 rows=50 loops=100)

103. 0.202 0.202 ↑ 17.0 1 101

CTE Scan on new_results (cost=0.00..0.34 rows=17 width=36) (actual time=0.000..0.002 rows=1 loops=101)

104. 0.001 134.588 ↑ 200.0 1 1

Unique (cost=23,270.21..23,295.71 rows=200 width=32) (actual time=134.587..134.588 rows=1 loops=1)

105. 0.007 134.587 ↑ 5,101.0 1 1

Sort (cost=23,270.21..23,282.96 rows=5,101 width=32) (actual time=134.587..134.587 rows=1 loops=1)

  • Sort Key: e.res
  • Sort Method: quicksort Memory: 25kB
106. 134.580 134.580 ↑ 5,101.0 1 1

CTE Scan on evaluation e (cost=0.00..22,956.08 rows=5,101 width=32) (actual time=133.294..134.580 rows=1 loops=1)

  • Filter: (NOT (in_1 IS DISTINCT FROM 100))
  • Rows Removed by Filter: 5150
107. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.02..0.03 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: $37
108.          

Initplan (forResult)

109. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=32) (never executed)

110. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)