explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cYtG

Settings
# exclusive inclusive rows x rows loops node
1. 0.337 4,616.459 ↑ 1.0 1 1

Subquery Scan on counter (cost=20,000,342,232.26..20,000,342,232.29 rows=1 width=32) (actual time=4,616.434..4,616.459 rows=1 loops=1)

  • Functions: 281
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 31.295 ms, Inlining 35.156 ms, Optimization 1496.544 ms, Emission 971.878 ms, Total 2534.873 ms
2.          

Initplan (for Subquery Scan)

3. 0.861 1,018.234 ↑ 1.0 1 1

Aggregate (cost=10,000,172,310.24..10,000,172,310.25 rows=1 width=32) (actual time=1,018.214..1,018.234 rows=1 loops=1)

4. 0.006 1,017.373 ↑ 1.0 7 1

Limit (cost=10,000,169,859.76..10,000,172,310.15 rows=7 width=32) (actual time=1,003.312..1,017.373 rows=7 loops=1)

5. 3.464 1,017.367 ↑ 768.3 7 1

Nested Loop Left Join (cost=10,000,169,859.76..10,002,052,460.12 rows=5,378 width=32) (actual time=1,003.308..1,017.367 rows=7 loops=1)

6. 0.015 1,011.488 ↑ 768.3 7 1

Nested Loop Left Join (cost=10,000,169,859.21..10,001,728,328.06 rows=5,378 width=929) (actual time=1,001.083..1,011.488 rows=7 loops=1)

7. 0.014 1,009.233 ↑ 768.3 7 1

Nested Loop Left Join (cost=10,000,169,858.66..10,001,680,773.10 rows=5,378 width=913) (actual time=999.985..1,009.233 rows=7 loops=1)

8. 0.017 999.818 ↑ 768.3 7 1

Nested Loop Left Join (cost=10,000,169,856.45..10,000,758,647.77 rows=5,378 width=881) (actual time=996.097..999.818 rows=7 loops=1)

9. 0.029 993.347 ↑ 768.3 7 1

Nested Loop (cost=10,000,169,855.21..10,000,204,727.22 rows=5,378 width=849) (actual time=993.275..993.347 rows=7 loops=1)

10. 190.986 993.241 ↑ 768.3 7 1

Sort (cost=10,000,169,854.78..10,000,169,868.23 rows=5,378 width=337) (actual time=993.227..993.241 rows=7 loops=1)

  • Sort Key: "*SELECT* 1_1".begin_date DESC NULLS LAST
  • Sort Method: external merge Disk: 13,328kB
11. 41.004 802.255 ↓ 84.4 453,963 1

Result (cost=0.01..10,000,169,521.54 rows=5,378 width=337) (actual time=0.180..802.255 rows=453,963 loops=1)

12. 27.038 761.251 ↓ 84.4 453,963 1

Append (cost=0.01..10,000,169,467.76 rows=5,378 width=9) (actual time=0.179..761.251 rows=453,963 loops=1)

13. 0.001 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=0.01..0.03 rows=1 width=9) (actual time=0.008..0.009 rows=0 loops=1)

14. 0.001 0.008 ↓ 0.0 0 1

Unique (cost=0.01..0.02 rows=1 width=169) (actual time=0.007..0.008 rows=0 loops=1)

15. 0.005 0.007 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=169) (actual time=0.006..0.007 rows=0 loops=1)

  • Sort Key: id
  • Sort Method: quicksort Memory: 25kB
16. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=169) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: false
17. 33.101 734.204 ↓ 84.4 453,963 1

Subquery Scan on *SELECT* 2_1 (cost=10,000,000,000.42..10,000,169,440.84 rows=5,377 width=9) (actual time=0.170..734.204 rows=453,963 loops=1)

18. 44.301 701.103 ↓ 84.4 453,963 1

Nested Loop (cost=10,000,000,000.42..10,000,169,387.07 rows=5,377 width=169) (actual time=0.168..701.103 rows=453,963 loops=1)

19. 0.033 0.033 ↑ 1.0 1 1

Index Only Scan using advisor_id_sugar on advisor adv_1 (cost=0.41..4.43 rows=1 width=0) (actual time=0.029..0.033 rows=1 loops=1)

  • Index Cond: (id_sugar = '2040697876119550'::text)
  • Heap Fetches: 0
20. 656.769 656.769 ↓ 84.4 453,963 1

Seq Scan on opportunity o_2 (cost=10,000,000,000.00..10,000,169,315.43 rows=5,377 width=25) (actual time=0.136..656.769 rows=453,963 loops=1)

  • Filter: (((status).code)::text = ANY ('{219902325556334}'::text[]))
  • Rows Removed by Filter: 621,365
21. 0.077 0.077 ↑ 1.0 1 7

Index Scan using opportunity_pk on opportunity o_1 (cost=0.43..6.47 rows=1 width=848) (actual time=0.011..0.011 rows=1 loops=7)

  • Index Cond: (id = "*SELECT* 1_1".id)
22. 2.639 6.454 ↑ 1.0 1 7

Subquery Scan on p_2 (cost=1.24..102.98 rows=1 width=32) (actual time=0.919..0.922 rows=1 loops=7)

23. 0.070 3.815 ↑ 1.0 1 7

Nested Loop Left Join (cost=1.24..102.72 rows=1 width=723) (actual time=0.543..0.545 rows=1 loops=7)

24. 0.021 0.182 ↑ 1.0 1 7

Nested Loop (cost=0.97..17.01 rows=1 width=451) (actual time=0.025..0.026 rows=1 loops=7)

25. 0.084 0.084 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner kp (cost=0.55..8.57 rows=1 width=334) (actual time=0.012..0.012 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_1.assigned_to)::text)
26. 0.077 0.077 ↑ 1.0 1 7

Index Scan using advisor_id_sugar on advisor adv_2 (cost=0.41..8.43 rows=1 width=117) (actual time=0.011..0.011 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_1.assigned_to)::text)
27. 0.469 3.353 ↑ 1.0 1 7

Subquery Scan on ov (cost=0.27..34.50 rows=1 width=32) (actual time=0.478..0.479 rows=1 loops=7)

28. 0.070 2.884 ↑ 1.0 1 7

Index Scan using oz_id_sugar on oz o_3 (cost=0.27..34.24 rows=1 width=126) (actual time=0.411..0.412 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (adv_2.oz_id)::text)
29.          

SubPlan (for Index Scan)

30. 0.238 0.238 ↑ 1.0 1 7

Index Scan using address_pk on address a_2 (cost=0.42..8.69 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=7)

  • Index Cond: (id = o_3.address_id)
31. 2.429 2.576 ↑ 1.0 1 7

Nested Loop (cost=0.97..17.27 rows=1 width=32) (actual time=0.366..0.368 rows=1 loops=7)

32. 0.070 0.070 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner p (cost=0.55..8.57 rows=1 width=342) (actual time=0.009..0.010 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_3.head_id)::text)
33. 0.077 0.077 ↑ 1.0 1 7

Index Scan using advisor_id_sugar on advisor ad (cost=0.41..8.43 rows=1 width=102) (actual time=0.010..0.011 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_3.head_id)::text)
34.          

SubPlan (for Nested Loop Left Join)

35. 0.140 0.140 ↑ 1.0 1 7

Index Scan using address_pk on address a (cost=0.42..8.69 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=7)

  • Index Cond: (id = kp.primary_address)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using address_pk on address a_1 (cost=0.42..8.69 rows=1 width=32) (never executed)

  • Index Cond: (id = kp.alt_address)
37. 0.035 0.035 ↑ 1.0 1 7

Index Scan using contact_pk on contact c (cost=0.43..8.45 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=7)

  • Index Cond: (id = kp.primary_email)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_1 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp.secondary_email)
39. 0.035 0.035 ↑ 1.0 1 7

Index Scan using contact_pk on contact c_2 (cost=0.43..8.45 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=7)

  • Index Cond: (id = kp.phone_mobile)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_3 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp.phone)
41. 2.849 9.401 ↑ 1.0 1 7

Subquery Scan on p_3 (cost=2.21..171.44 rows=1 width=32) (actual time=1.337..1.343 rows=1 loops=7)

42. 0.051 6.552 ↑ 1.0 1 7

Nested Loop Left Join (cost=2.21..171.18 rows=1 width=740) (actual time=0.931..0.936 rows=1 loops=7)

43. 0.021 0.168 ↑ 1.0 1 7

Nested Loop (cost=0.98..17.02 rows=1 width=540) (actual time=0.023..0.024 rows=1 loops=7)

44. 0.077 0.077 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner kp_1 (cost=0.55..8.57 rows=1 width=342) (actual time=0.011..0.011 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_1.contact_id)::text)
45. 0.070 0.070 ↑ 1.0 1 7

Index Scan using kapitol_contact_id_sugar on kapitol_contact cbase (cost=0.42..8.44 rows=1 width=235) (actual time=0.010..0.010 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_1.contact_id)::text)
46. 2.576 6.006 ↑ 1.0 1 7

Subquery Scan on p_4 (cost=1.24..102.98 rows=1 width=32) (actual time=0.854..0.858 rows=1 loops=7)

47. 0.063 3.430 ↑ 1.0 1 7

Nested Loop Left Join (cost=1.24..102.72 rows=1 width=724) (actual time=0.486..0.490 rows=1 loops=7)

48. 0.021 0.133 ↑ 1.0 1 7

Nested Loop (cost=0.97..17.01 rows=1 width=420) (actual time=0.018..0.019 rows=1 loops=7)

49. 0.056 0.056 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner kp_2 (cost=0.55..8.57 rows=1 width=334) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (kp_1.assigned_to)::text)
50. 0.056 0.056 ↑ 1.0 1 7

Index Scan using advisor_id_sugar on advisor adv_3 (cost=0.41..8.43 rows=1 width=86) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (kp_1.assigned_to)::text)
51. 0.462 3.052 ↑ 1.0 1 7

Subquery Scan on ov_1 (cost=0.27..34.50 rows=1 width=32) (actual time=0.434..0.436 rows=1 loops=7)

52. 0.056 2.590 ↑ 1.0 1 7

Index Scan using oz_id_sugar on oz o_4 (cost=0.27..34.24 rows=1 width=126) (actual time=0.369..0.370 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (adv_3.oz_id)::text)
53.          

SubPlan (for Index Scan)

54. 0.147 0.147 ↑ 1.0 1 7

Index Scan using address_pk on address a_5 (cost=0.42..8.69 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=7)

  • Index Cond: (id = o_4.address_id)
55. 2.275 2.387 ↑ 1.0 1 7

Nested Loop (cost=0.97..17.27 rows=1 width=32) (actual time=0.339..0.341 rows=1 loops=7)

56. 0.056 0.056 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner p_1 (cost=0.55..8.57 rows=1 width=342) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_4.head_id)::text)
57. 0.056 0.056 ↑ 1.0 1 7

Index Scan using advisor_id_sugar on advisor ad_1 (cost=0.41..8.43 rows=1 width=102) (actual time=0.007..0.008 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_4.head_id)::text)
58.          

SubPlan (for Nested Loop Left Join)

59. 0.133 0.133 ↑ 1.0 1 7

Index Scan using address_pk on address a_3 (cost=0.42..8.69 rows=1 width=32) (actual time=0.018..0.019 rows=1 loops=7)

  • Index Cond: (id = kp_2.primary_address)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using address_pk on address a_4 (cost=0.42..8.69 rows=1 width=32) (never executed)

  • Index Cond: (id = kp_2.alt_address)
61. 0.028 0.028 ↑ 1.0 1 7

Index Scan using contact_pk on contact c_8 (cost=0.43..8.45 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=7)

  • Index Cond: (id = kp_2.primary_email)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_9 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_2.secondary_email)
63. 0.021 0.021 ↑ 1.0 1 7

Index Scan using contact_pk on contact c_10 (cost=0.43..8.45 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (id = kp_2.phone_mobile)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_11 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_2.phone)
65.          

SubPlan (for Nested Loop Left Join)

66. 0.175 0.175 ↑ 1.0 1 7

Index Scan using address_pk on address a_1_1 (cost=0.42..8.69 rows=1 width=32) (actual time=0.024..0.025 rows=1 loops=7)

  • Index Cond: (id = kp_1.primary_address)
67. 0.064 0.064 ↑ 1.0 1 2

Index Scan using address_pk on address a_1_2 (cost=0.42..8.69 rows=1 width=32) (actual time=0.032..0.032 rows=1 loops=2)

  • Index Cond: (id = kp_1.alt_address)
68. 0.042 0.042 ↑ 1.0 1 6

Index Scan using contact_pk on contact c_4 (cost=0.43..8.45 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=6)

  • Index Cond: (id = kp_1.primary_email)
69. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_5 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_1.secondary_email)
70. 0.030 0.030 ↑ 1.0 1 6

Index Scan using contact_pk on contact c_6 (cost=0.43..8.45 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (id = kp_1.phone_mobile)
71. 0.016 0.016 ↑ 1.0 1 4

Index Scan using contact_pk on contact c_7 (cost=0.43..8.45 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=4)

  • Index Cond: (id = kp_1.phone)
72. 2.240 2.240 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner p_5 (cost=0.55..8.82 rows=1 width=32) (actual time=0.319..0.320 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_1.partner_id)::text)
73. 2.135 2.415 ↑ 1.0 1 7

Subquery Scan on p_6 (cost=0.55..59.99 rows=1 width=32) (actual time=0.344..0.345 rows=1 loops=7)

74. 0.105 0.280 ↑ 1.0 1 7

Index Scan using kapitol_partner_id_sugar on kapitol_partner kp_3 (cost=0.55..59.73 rows=1 width=532) (actual time=0.039..0.040 rows=1 loops=7)

  • Index Cond: ((id_sugar)::text = (o_1.created_by)::text)
75.          

SubPlan (for Index Scan)

76. 0.133 0.133 ↑ 1.0 1 7

Index Scan using address_pk on address a_6 (cost=0.42..8.69 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=7)

  • Index Cond: (id = kp_3.primary_address)
77. 0.000 0.000 ↓ 0.0 0

Index Scan using address_pk on address a_7 (cost=0.42..8.69 rows=1 width=32) (never executed)

  • Index Cond: (id = kp_3.alt_address)
78. 0.021 0.021 ↑ 1.0 1 7

Index Scan using contact_pk on contact c_12 (cost=0.43..8.45 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (id = kp_3.primary_email)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_13 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_3.secondary_email)
80. 0.021 0.021 ↑ 1.0 1 7

Index Scan using contact_pk on contact c_14 (cost=0.43..8.45 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (id = kp_3.phone_mobile)
81. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_pk on contact c_15 (cost=0.43..8.45 rows=1 width=18) (never executed)

  • Index Cond: (id = kp_3.phone)
82. 117.286 3,597.888 ↑ 1.0 1 1

Aggregate (cost=10,000,169,922.01..10,000,169,922.02 rows=1 width=8) (actual time=3,597.883..3,597.888 rows=1 loops=1)

83. 168.285 3,480.602 ↓ 84.4 453,963 1

Sort (cost=10,000,169,841.34..10,000,169,854.79 rows=5,378 width=337) (actual time=3,434.386..3,480.602 rows=453,963 loops=1)

  • Sort Key: "*SELECT* 1".begin_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 32,203kB
84. 40.758 3,312.317 ↓ 84.4 453,963 1

Result (cost=0.01..10,000,169,508.10 rows=5,378 width=337) (actual time=2,505.073..3,312.317 rows=453,963 loops=1)

85. 25.685 3,271.559 ↓ 84.4 453,963 1

Append (cost=0.01..10,000,169,454.32 rows=5,378 width=8) (actual time=2,505.071..3,271.559 rows=453,963 loops=1)

86. 0.001 2,504.907 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.01..0.03 rows=1 width=8) (actual time=2,504.906..2,504.907 rows=0 loops=1)

87. 0.001 2,504.906 ↓ 0.0 0 1

Unique (cost=0.01..0.02 rows=1 width=169) (actual time=2,504.905..2,504.906 rows=0 loops=1)

88. 0.006 2,504.905 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=169) (actual time=2,504.904..2,504.905 rows=0 loops=1)

  • Sort Key: id
  • Sort Method: quicksort Memory: 25kB
89. 2,504.899 2,504.899 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=169) (actual time=2,504.899..2,504.899 rows=0 loops=1)

  • One-Time Filter: false
90. 31.740 740.967 ↓ 84.4 453,963 1

Subquery Scan on *SELECT* 2 (cost=10,000,000,000.42..10,000,169,427.40 rows=5,377 width=8) (actual time=0.162..740.967 rows=453,963 loops=1)

91. 41.695 709.227 ↓ 84.4 453,963 1

Nested Loop (cost=10,000,000,000.42..10,000,169,373.63 rows=5,377 width=169) (actual time=0.160..709.227 rows=453,963 loops=1)

92. 0.042 0.042 ↑ 1.0 1 1

Index Only Scan using advisor_id_sugar on advisor adv (cost=0.41..4.43 rows=1 width=0) (actual time=0.039..0.042 rows=1 loops=1)

  • Index Cond: (id_sugar = '2040697876119550'::text)
  • Heap Fetches: 0
93. 667.490 667.490 ↓ 84.4 453,963 1

Seq Scan on opportunity o (cost=10,000,000,000.00..10,000,169,315.43 rows=5,377 width=8) (actual time=0.118..667.490 rows=453,963 loops=1)

  • Filter: (((status).code)::text = ANY ('{219902325556334}'::text[]))
  • Rows Removed by Filter: 621,365
Execution time : 4,652.340 ms