explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SE6k

Settings
# exclusive inclusive rows x rows loops node
1. 0.085 57,501.557 ↑ 284.0 10 1

HashAggregate (cost=91,481.64..91,510.04 rows=2,840 width=263) (actual time=57,501.529..57,501.557 rows=10 loops=1)

  • Group Key: retrieve_person_linked_recipients.recipient_id, retrieve_person_linked_recipients.recipient_uuid, retrieve_person_linked_recipients.target_name, retrieve_person_linked_recipients.recipient_cat, retrieve_person_linked_recipients.is_tmp_rpmt, retrieve_person_linked_recipients.grp_association, retrieve_person_linked_recipients.tm_id, retrieve_person_linked_recipients.path, retrieve_person_linked_recipients.hierarchy, retrieve_person_linked_recipients.org_id, retrieve_person_linked_recipients.org_uuid
2.          

CTE get_person

3. 0.009 49.955 ↑ 1.0 1 1

Nested Loop (cost=0.85..5.39 rows=1 width=98) (actual time=49.953..49.955 rows=1 loops=1)

4. 49.848 49.848 ↑ 1.0 1 1

Index Only Scan using idx_persons_person_id_not_deleted on persons p (cost=0.42..1.94 rows=1 width=8) (actual time=49.847..49.848 rows=1 loops=1)

  • Index Cond: (person_id = '6051063'::bigint)
  • Heap Fetches: 0
5. 0.098 0.098 ↑ 1.0 1 1

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r (cost=0.43..3.45 rows=1 width=90) (actual time=0.097..0.098 rows=1 loops=1)

  • Index Cond: (recipient_id = '6051063'::bigint)
6.          

CTE get_person_and_global_temporary_replacements

7. 0.002 50.119 ↑ 2.0 1 1

Append (cost=1.45..19.19 rows=2 width=171) (actual time=50.117..50.119 rows=1 loops=1)

8. 0.000 50.112 ↓ 0.0 0 1

Nested Loop (cost=1.45..19.16 rows=1 width=99) (actual time=50.112..50.112 rows=0 loops=1)

9.          

Initplan (for Nested Loop)

10. 49.961 49.961 ↑ 1.0 1 1

CTE Scan on get_person (cost=0.00..0.02 rows=1 width=8) (actual time=49.959..49.961 rows=1 loops=1)

11. 0.000 50.111 ↓ 0.0 0 1

Nested Loop (cost=1.00..18.10 rows=1 width=16) (actual time=50.111..50.111 rows=0 loops=1)

12. 0.001 50.111 ↓ 0.0 0 1

Nested Loop (cost=0.71..14.78 rows=1 width=24) (actual time=50.111..50.111 rows=0 loops=1)

13. 50.110 50.110 ↓ 0.0 0 1

Index Scan using idx_tmp_rpmt1 on tmp_rpmt tr (cost=0.29..8.96 rows=3 width=16) (actual time=50.110..50.110 rows=0 loops=1)

  • Index Cond: (rpmt_id = $1)
  • Filter: (((rpmt_type)::text = 'REPLACEMENT'::text) AND ((cat)::text = 'GLOBAL'::text))
14. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_persons_person_id_not_deleted on persons p_1 (cost=0.42..1.94 rows=1 width=8) (never executed)

  • Index Cond: (person_id = tr.tmper_id)
  • Heap Fetches: 0
15. 0.000 0.000 ↓ 0.0 0

Index Scan using date_rng_pk on date_rng dr (cost=0.29..3.32 rows=1 width=8) (never executed)

  • Index Cond: (date_rng_id = tr.date_rng_id)
  • Filter: ((now() >= start_date) AND (now() <= end_date))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_1 (cost=0.43..1.03 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = p_1.person_id)
17. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on get_person get_person_1 (cost=0.00..0.02 rows=1 width=241) (actual time=0.003..0.005 rows=1 loops=1)

18.          

CTE person_and_devices_recipients

19. 0.004 61.736 ↑ 1.5 4 1

Append (cost=1.14..25.76 rows=6 width=233) (actual time=61.443..61.736 rows=4 loops=1)

20. 0.042 61.729 ↑ 1.3 3 1

Nested Loop (cost=1.14..25.66 rows=4 width=108) (actual time=61.442..61.729 rows=3 loops=1)

21. 0.013 50.716 ↑ 1.3 3 1

Nested Loop (cost=0.85..24.45 rows=4 width=99) (actual time=50.451..50.716 rows=3 loops=1)

22. 0.008 50.550 ↑ 3.3 3 1

Nested Loop (cost=0.43..18.72 rows=10 width=9) (actual time=50.396..50.550 rows=3 loops=1)

23. 50.123 50.123 ↑ 2.0 1 1

CTE Scan on get_person_and_global_temporary_replacements p_2 (cost=0.00..0.04 rows=2 width=9) (actual time=50.119..50.123 rows=1 loops=1)

24. 0.419 0.419 ↑ 1.7 3 1

Index Scan using idx_dvc2 on dvc (cost=0.43..9.29 rows=5 width=16) (actual time=0.272..0.419 rows=3 loops=1)

  • Index Cond: (person_id = p_2.person_id)
25. 0.153 0.153 ↑ 1.0 1 3

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_2 (cost=0.43..0.57 rows=1 width=98) (actual time=0.051..0.051 rows=1 loops=3)

  • Index Cond: (recipient_id = dvc.dvc_id)
26. 10.971 10.971 ↑ 1.0 1 3

Index Scan using org_dvc_names_pk on org_dvc_names dn (cost=0.28..0.30 rows=1 width=18) (actual time=3.657..3.657 rows=1 loops=3)

  • Index Cond: (org_dvc_name_id = r_2.org_dvc_name_id)
27. 0.001 0.003 ↑ 2.0 1 1

Subquery Scan on "*SELECT* 2" (cost=0.00..0.06 rows=2 width=233) (actual time=0.003..0.003 rows=1 loops=1)

28. 0.002 0.002 ↑ 2.0 1 1

CTE Scan on get_person_and_global_temporary_replacements (cost=0.00..0.04 rows=2 width=233) (actual time=0.002..0.002 rows=1 loops=1)

29.          

CTE group_rec

30. 0.002 91.165 ↓ 0.0 0 1

Recursive Union (cost=1.27..55,884.67 rows=10,305 width=164) (actual time=91.165..91.165 rows=0 loops=1)

31. 0.001 91.157 ↓ 0.0 0 1

Nested Loop (cost=1.27..193.57 rows=35 width=164) (actual time=91.157..91.157 rows=0 loops=1)

32. 0.000 91.156 ↓ 0.0 0 1

Nested Loop (cost=0.84..67.99 rows=81 width=17) (actual time=91.156..91.156 rows=0 loops=1)

33. 0.012 91.156 ↓ 0.0 0 1

Nested Loop (cost=0.42..29.78 rows=81 width=9) (actual time=91.155..91.156 rows=0 loops=1)

34. 61.772 61.772 ↑ 1.5 4 1

CTE Scan on person_and_devices_recipients pdr (cost=0.00..0.12 rows=6 width=9) (actual time=61.449..61.772 rows=4 loops=1)

35. 29.372 29.372 ↓ 0.0 0 4

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m (cost=0.42..4.81 rows=13 width=16) (actual time=7.343..7.343 rows=0 loops=4)

  • Index Cond: (recipient_id = pdr.recipient_id)
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Scan using tm_pk on tm (cost=0.42..0.47 rows=1 width=16) (never executed)

  • Index Cond: (tm_id = m.tm_id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_3 (cost=0.43..1.55 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = tm.grp_id)
38. 0.001 0.006 ↓ 0.0 0 1

Nested Loop (cost=1.27..5,548.50 rows=1,027 width=164) (actual time=0.006..0.006 rows=0 loops=1)

39. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.84..1,888.67 rows=2,349 width=81) (actual time=0.005..0.005 rows=0 loops=1)

40. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.42..780.74 rows=2,349 width=73) (actual time=0.004..0.004 rows=0 loops=1)

41. 0.003 0.003 ↓ 0.0 0 1

WorkTable Scan on group_rec gr (cost=0.00..7.00 rows=175 width=73) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (NOT cycle)
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m_1 (cost=0.42..4.29 rows=13 width=16) (never executed)

  • Index Cond: (recipient_id = gr.recipient_id)
  • Heap Fetches: 0
43. 0.000 0.000 ↓ 0.0 0

Index Scan using tm_pk on tm tm_1 (cost=0.42..0.47 rows=1 width=16) (never executed)

  • Index Cond: (tm_id = m_1.tm_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_4 (cost=0.43..1.55 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = tm_1.grp_id)
45.          

CTE supervisor_rec

46. 0.003 20.529 ↓ 0.0 0 1

Recursive Union (cost=2.86..19,310.10 rows=3,532 width=163) (actual time=20.529..20.529 rows=0 loops=1)

47. 0.001 20.519 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.86..99.17 rows=12 width=163) (actual time=20.519..20.519 rows=0 loops=1)

48. 0.001 20.518 ↓ 0.0 0 1

Nested Loop (cost=2.44..89.72 rows=12 width=90) (actual time=20.518..20.518 rows=0 loops=1)

49. 0.010 20.517 ↓ 0.0 0 1

Nested Loop (cost=2.01..42.32 rows=28 width=8) (actual time=20.517..20.517 rows=0 loops=1)

50. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on get_person p_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.004 rows=1 loops=1)

51. 0.019 20.503 ↓ 0.0 0 1

Bitmap Heap Scan on grp_sups sups (cost=2.01..42.02 rows=28 width=16) (actual time=20.503..20.503 rows=0 loops=1)

  • Recheck Cond: (person_id = p_3.person_id)
52. 20.484 20.484 ↓ 0.0 0 1

Bitmap Index Scan on idx_grp_sups2 (cost=0.00..2.00 rows=28 width=0) (actual time=20.483..20.484 rows=0 loops=1)

  • Index Cond: (person_id = p_3.person_id)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_5 (cost=0.43..1.69 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = sups.grp_id)
54. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships tm_2 (cost=0.42..0.66 rows=13 width=16) (never executed)

  • Index Cond: (recipient_id = r_5.recipient_id)
  • Heap Fetches: 0
55. 0.002 0.007 ↓ 0.0 0 1

Nested Loop (cost=1.27..1,914.03 rows=352 width=163) (actual time=0.007..0.007 rows=0 loops=1)

56. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.84..659.81 rows=805 width=80) (actual time=0.005..0.005 rows=0 loops=1)

57. 0.000 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.42..280.12 rows=805 width=72) (actual time=0.004..0.004 rows=0 loops=1)

58. 0.004 0.004 ↓ 0.0 0 1

WorkTable Scan on supervisor_rec s (cost=0.00..2.40 rows=60 width=72) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (NOT cycle)
59. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m_2 (cost=0.42..4.50 rows=13 width=16) (never executed)

  • Index Cond: (recipient_id = s.recipient_id)
  • Heap Fetches: 0
60. 0.000 0.000 ↓ 0.0 0

Index Scan using tm_pk on tm tm_3 (cost=0.42..0.47 rows=1 width=16) (never executed)

  • Index Cond: (tm_id = m_2.tm_id)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_6 (cost=0.43..1.55 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = tm_3.grp_id)
62.          

CTE temporary_rec_coverage

63. 0.001 0.050 ↓ 0.0 0 1

Recursive Union (cost=1.43..1,626.61 rows=291 width=163) (actual time=0.050..0.050 rows=0 loops=1)

64. 0.001 0.045 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..14.07 rows=1 width=163) (actual time=0.045..0.045 rows=0 loops=1)

65. 0.000 0.044 ↓ 0.0 0 1

Nested Loop (cost=1.00..13.29 rows=1 width=90) (actual time=0.044..0.044 rows=0 loops=1)

66. 0.001 0.044 ↓ 0.0 0 1

Nested Loop (cost=0.57..10.27 rows=1 width=8) (actual time=0.044..0.044 rows=0 loops=1)

67. 0.006 0.043 ↓ 0.0 0 1

Nested Loop (cost=0.29..9.00 rows=2 width=16) (actual time=0.043..0.043 rows=0 loops=1)

68. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on get_person p_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

69. 0.035 0.035 ↓ 0.0 0 1

Index Scan using idx_tmp_rpmt1 on tmp_rpmt tr_1 (cost=0.29..8.96 rows=2 width=24) (actual time=0.035..0.035 rows=0 loops=1)

  • Index Cond: (rpmt_id = p_4.person_id)
  • Filter: (((rpmt_type)::text = 'REPLACEMENT'::text) AND ((cat)::text = 'COVERAGE'::text))
70. 0.000 0.000 ↓ 0.0 0

Index Scan using date_rng_pk on date_rng dr_1 (cost=0.29..0.63 rows=1 width=8) (never executed)

  • Index Cond: (date_rng_id = tr_1.date_rng_id)
  • Filter: ((now() >= start_date) AND (now() <= end_date))
71. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_7 (cost=0.43..3.02 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = tr_1.grp_id)
72. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships tm_4 (cost=0.42..0.66 rows=13 width=16) (never executed)

  • Index Cond: (recipient_id = r_7.recipient_id)
  • Heap Fetches: 0
73. 0.000 0.004 ↓ 0.0 0 1

Nested Loop (cost=1.27..160.67 rows=29 width=163) (actual time=0.004..0.004 rows=0 loops=1)

74. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.84..56.29 rows=67 width=80) (actual time=0.004..0.004 rows=0 loops=1)

75. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.42..24.69 rows=67 width=72) (actual time=0.003..0.003 rows=0 loops=1)

76. 0.003 0.003 ↓ 0.0 0 1

WorkTable Scan on temporary_rec_coverage t (cost=0.00..0.20 rows=5 width=72) (actual time=0.002..0.003 rows=0 loops=1)

  • Filter: (NOT cycle)
77. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m_3 (cost=0.42..4.77 rows=13 width=16) (never executed)

  • Index Cond: (recipient_id = t.recipient_id)
  • Heap Fetches: 0
78. 0.000 0.000 ↓ 0.0 0

Index Scan using tm_pk on tm tm_5 (cost=0.42..0.47 rows=1 width=16) (never executed)

  • Index Cond: (tm_id = m_3.tm_id)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_8 (cost=0.43..1.55 rows=1 width=90) (never executed)

  • Index Cond: (recipient_id = tm_5.grp_id)
80.          

CTE dynamic_team_recipients

81. 4.446 57,389.599 ↑ 10.8 6 1

Subquery Scan on pdt (cost=186.09..13,438.50 rows=65 width=90) (actual time=1,650.842..57,389.599 rows=6 loops=1)

  • Filter: (pdt.person_id = $35)
  • Rows Removed by Filter: 54644
82.          

Initplan (for Subquery Scan)

83. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on get_person get_person_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

84. 8.448 57,385.150 ↓ 4.2 54,650 1

Result (cost=186.07..13,275.98 rows=13,000 width=98) (actual time=750.281..57,385.150 rows=54,650 loops=1)

85.          

Initplan (for Result)

86. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on get_person get_person_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

87. 54,785.156 57,376.701 ↓ 4.2 54,650 1

ProjectSet (cost=186.05..9,895.96 rows=13,000 width=122) (actual time=750.275..57,376.701 rows=54,650 loops=1)

88. 22.280 2,591.545 ↓ 99.2 1,289 1

Hash Join (cost=186.05..9,827.65 rows=13 width=90) (actual time=604.643..2,591.545 rows=1,289 loops=1)

  • Hash Cond: (r_9.recipient_id = dt.dyn_tm_id)
89. 2,567.290 2,567.290 ↓ 8.8 60,318 1

Index Scan using idx_recipients_del_org_id_cat on recipients r_9 (cost=0.43..9,624.05 rows=6,851 width=90) (actual time=0.170..2,567.290 rows=60,318 loops=1)

  • Index Cond: ((deleted_id = '-1'::integer) AND (org_id = $36))
90. 0.883 1.975 ↑ 1.0 5,703 1

Hash (cost=114.33..114.33 rows=5,703 width=8) (actual time=1.975..1.975 rows=5,703 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
91. 1.092 1.092 ↑ 1.0 5,703 1

Index Only Scan using dyn_tms_pk on dyn_tms dt (cost=0.28..114.33 rows=5,703 width=8) (actual time=0.073..1.092 rows=5,703 loops=1)

  • Heap Fetches: 0
92.          

CTE retrieve_person_linked_recipients

93. 0.010 57,501.451 ↑ 1,419.9 10 1

Append (cost=0.00..496.96 rows=14,199 width=263) (actual time=91.191..57,501.451 rows=10 loops=1)

94. 91.166 91.166 ↓ 0.0 0 1

CTE Scan on group_rec (cost=0.00..257.62 rows=10,305 width=263) (actual time=91.166..91.166 rows=0 loops=1)

95. 0.002 0.027 ↑ 1.5 4 1

Subquery Scan on "*SELECT* 2_1" (cost=0.00..0.21 rows=6 width=263) (actual time=0.023..0.027 rows=4 loops=1)

96. 0.025 0.025 ↑ 1.5 4 1

CTE Scan on person_and_devices_recipients (cost=0.00..0.15 rows=6 width=263) (actual time=0.022..0.025 rows=4 loops=1)

97. 20.531 20.531 ↓ 0.0 0 1

CTE Scan on supervisor_rec (cost=0.00..88.30 rows=3,532 width=263) (actual time=20.531..20.531 rows=0 loops=1)

98. 0.052 0.052 ↓ 0.0 0 1

CTE Scan on temporary_rec_coverage (cost=0.00..7.28 rows=291 width=263) (actual time=0.052..0.052 rows=0 loops=1)

99. 57,389.665 57,389.665 ↑ 10.8 6 1

CTE Scan on dynamic_team_recipients (cost=0.00..1.62 rows=65 width=263) (actual time=1,650.851..57,389.665 rows=6 loops=1)

100. 57,501.472 57,501.472 ↑ 1,419.9 10 1

CTE Scan on retrieve_person_linked_recipients (cost=0.00..283.98 rows=14,199 width=263) (actual time=91.194..57,501.472 rows=10 loops=1)

Planning time : 1,235.425 ms
Execution time : 57,505.878 ms