explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wTA

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 18.151 ↑ 566.7 3 1

HashAggregate (cost=29,412.42..29,429.42 rows=1,700 width=230) (actual time=18.140..18.151 rows=3 loops=1)

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

CTE get_person

3. 0.006 0.309 ↑ 1.0 1 1

Nested Loop (cost=0.58..5.12 rows=1 width=50) (actual time=0.307..0.309 rows=1 loops=1)

4. 0.277 0.277 ↑ 1.0 1 1

Index Only Scan using idx_persons_person_id_not_deleted on persons p (cost=0.29..1.81 rows=1 width=8) (actual time=0.276..0.277 rows=1 loops=1)

  • Index Cond: (person_id = 203126)
  • Heap Fetches: 0
5. 0.026 0.026 ↑ 1.0 1 1

Index Scan using recipients_pk on recipients r (cost=0.29..3.31 rows=1 width=42) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: (recipient_id = 203126)
  • Filter: (deleted_id = '-1'::integer)
6.          

CTE person_and_devices_recipients

7. 0.002 0.707 ↓ 1.5 3 1

Append (cost=0.72..4.05 rows=2 width=126) (actual time=0.672..0.707 rows=3 loops=1)

8. 0.019 0.703 ↓ 2.0 2 1

Nested Loop (cost=0.72..4.01 rows=1 width=54) (actual time=0.672..0.703 rows=2 loops=1)

9. 0.002 0.670 ↓ 2.0 2 1

Nested Loop (cost=0.58..3.84 rows=1 width=50) (actual time=0.641..0.670 rows=2 loops=1)

10. 0.003 0.618 ↓ 2.0 2 1

Nested Loop (cost=0.29..3.33 rows=1 width=8) (actual time=0.614..0.618 rows=2 loops=1)

11. 0.311 0.311 ↑ 1.0 1 1

CTE Scan on get_person p_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.309..0.311 rows=1 loops=1)

12. 0.304 0.304 ↓ 2.0 2 1

Index Scan using idx_dvc2 on dvc (cost=0.29..3.31 rows=1 width=16) (actual time=0.303..0.304 rows=2 loops=1)

  • Index Cond: (person_id = p_1.person_id)
13. 0.050 0.050 ↑ 1.0 1 2

Index Scan using recipients_pk on recipients r_1 (cost=0.29..0.51 rows=1 width=50) (actual time=0.025..0.025 rows=1 loops=2)

  • Index Cond: (recipient_id = dvc.dvc_id)
  • Filter: (deleted_id = '-1'::integer)
14. 0.014 0.014 ↑ 1.0 1 2

Index Scan using org_dvc_names_pk on org_dvc_names dn (cost=0.14..0.16 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=2)

  • Index Cond: (org_dvc_name_id = r_1.org_dvc_name_id)
15. 0.001 0.002 ↑ 1.0 1 1

Subquery Scan on "*SELECT* 2" (cost=0.00..0.03 rows=1 width=126) (actual time=0.002..0.002 rows=1 loops=1)

16. 0.001 0.001 ↑ 1.0 1 1

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

17.          

CTE group_rec

18. 0.001 0.734 ↓ 0.0 0 1

Recursive Union (cost=1.15..550.09 rows=193 width=115) (actual time=0.734..0.734 rows=0 loops=1)

19. 0.000 0.731 ↓ 0.0 0 1

Nested Loop (cost=1.15..7.43 rows=3 width=115) (actual time=0.731..0.731 rows=0 loops=1)

20. 0.000 0.731 ↓ 0.0 0 1

Nested Loop (cost=0.86..6.51 rows=3 width=58) (actual time=0.731..0.731 rows=0 loops=1)

21. 0.001 0.731 ↓ 0.0 0 1

Nested Loop (cost=0.57..4.64 rows=3 width=16) (actual time=0.731..0.731 rows=0 loops=1)

22. 0.002 0.730 ↓ 0.0 0 1

Nested Loop (cost=0.29..3.67 rows=3 width=8) (actual time=0.730..0.730 rows=0 loops=1)

23. 0.710 0.710 ↓ 1.5 3 1

CTE Scan on person_and_devices_recipients pdr (cost=0.00..0.04 rows=2 width=8) (actual time=0.674..0.710 rows=3 loops=1)

24. 0.018 0.018 ↓ 0.0 0 3

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m (cost=0.29..1.81 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=3)

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

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

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

Index Scan using recipients_pk on recipients r_2 (cost=0.29..0.62 rows=1 width=42) (never executed)

  • Index Cond: (recipient_id = tm.grp_id)
  • Filter: (deleted_id = '-1'::integer)
27. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grp_pk on grp g (cost=0.29..0.31 rows=1 width=8) (never executed)

  • Index Cond: (grp_id = tm.grp_id)
  • Heap Fetches: 0
28. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=1.15..53.88 rows=19 width=115) (actual time=0.002..0.002 rows=0 loops=1)

29. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.86..47.68 rows=19 width=122) (actual time=0.002..0.002 rows=0 loops=1)

30. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.57..34.60 rows=21 width=80) (actual time=0.002..0.002 rows=0 loops=1)

31. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.29..27.82 rows=21 width=72) (actual time=0.001..0.001 rows=0 loops=1)

32. 0.001 0.001 ↓ 0.0 0 1

WorkTable Scan on group_rec gr (cost=0.00..0.60 rows=15 width=72) (actual time=0.001..0.001 rows=0 loops=1)

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

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m_1 (cost=0.29..1.81 rows=1 width=16) (never executed)

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

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

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

Index Scan using recipients_pk on recipients r_3 (cost=0.29..0.62 rows=1 width=42) (never executed)

  • Index Cond: (recipient_id = tm_1.grp_id)
  • Filter: (deleted_id = '-1'::integer)
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grp_pk on grp g_1 (cost=0.29..0.31 rows=1 width=8) (never executed)

  • Index Cond: (grp_id = tm_1.grp_id)
  • Heap Fetches: 0
37.          

CTE supervisor_rec

38. 0.000 17.338 ↓ 0.0 0 1

Recursive Union (cost=1.15..27,475.79 rows=16,744 width=115) (actual time=17.338..17.338 rows=0 loops=1)

39. 0.001 0.166 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.15..390.71 rows=254 width=115) (actual time=0.165..0.166 rows=0 loops=1)

40. 0.000 0.165 ↓ 0.0 0 1

Nested Loop (cost=0.86..309.87 rows=254 width=42) (actual time=0.165..0.165 rows=0 loops=1)

41. 0.001 0.165 ↓ 0.0 0 1

Nested Loop (cost=0.57..232.70 rows=254 width=50) (actual time=0.164..0.165 rows=0 loops=1)

42. 0.001 0.164 ↓ 0.0 0 1

Nested Loop (cost=0.29..58.12 rows=276 width=8) (actual time=0.164..0.164 rows=0 loops=1)

43. 0.001 0.001 ↑ 1.0 1 1

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

44. 0.162 0.162 ↓ 0.0 0 1

Index Scan using idx_grp_sups2 on grp_sups sups (cost=0.29..55.34 rows=276 width=16) (actual time=0.162..0.162 rows=0 loops=1)

  • Index Cond: (person_id = p_2.person_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_pk on recipients r_4 (cost=0.29..0.63 rows=1 width=42) (never executed)

  • Index Cond: (recipient_id = sups.grp_id)
  • Filter: (deleted_id = '-1'::integer)
46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grp_pk on grp g_2 (cost=0.29..0.30 rows=1 width=8) (never executed)

  • Index Cond: (grp_id = r_4.recipient_id)
  • Heap Fetches: 0
47. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships tm_2 (cost=0.29..0.31 rows=1 width=16) (never executed)

  • Index Cond: (recipient_id = r_4.recipient_id)
  • Heap Fetches: 0
48. 0.001 17.172 ↓ 0.0 0 1

Nested Loop (cost=1,197.85..2,675.02 rows=1,649 width=115) (actual time=17.172..17.172 rows=0 loops=1)

49. 0.055 17.171 ↓ 0.0 0 1

Hash Join (cost=1,197.56..1,511.41 rows=1,795 width=88) (actual time=17.171..17.171 rows=0 loops=1)

  • Hash Cond: (g_3.grp_id = tm_3.grp_id)
50. 17.107 17.107 ↑ 11,621.0 1 1

Index Only Scan using grp_pk on grp g_3 (cost=0.29..252.60 rows=11,621 width=8) (actual time=17.107..17.107 rows=1 loops=1)

  • Heap Fetches: 0
51. 0.000 0.009 ↓ 0.0 0 1

Hash (cost=1,174.84..1,174.84 rows=1,795 width=80) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
52. 0.002 0.009 ↓ 0.0 0 1

Hash Join (cost=527.57..1,174.84 rows=1,795 width=80) (actual time=0.009..0.009 rows=0 loops=1)

  • Hash Cond: (m_2.tm_id = tm_3.tm_id)
53. 0.001 0.007 ↓ 0.0 0 1

Nested Loop (cost=0.29..642.85 rows=1,795 width=72) (actual time=0.007..0.007 rows=0 loops=1)

54. 0.006 0.006 ↓ 0.0 0 1

WorkTable Scan on supervisor_rec s (cost=0.00..50.80 rows=1,270 width=72) (actual time=0.006..0.006 rows=0 loops=1)

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

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m_2 (cost=0.29..0.46 rows=1 width=16) (never executed)

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

Hash (cost=377.68..377.68 rows=11,968 width=16) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Seq Scan on tm tm_3 (cost=0.00..377.68 rows=11,968 width=16) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_pk on recipients r_5 (cost=0.29..0.63 rows=1 width=42) (never executed)

  • Index Cond: (recipient_id = g_3.grp_id)
  • Filter: (deleted_id = '-1'::integer)
59.          

CTE temporary_rec

60. 0.002 0.039 ↓ 0.0 0 1

Recursive Union (cost=1.00..187.37 rows=61 width=115) (actual time=0.039..0.039 rows=0 loops=1)

61. 0.000 0.033 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.00..7.64 rows=1 width=115) (actual time=0.033..0.033 rows=0 loops=1)

62. 0.001 0.033 ↓ 0.0 0 1

Nested Loop (cost=0.72..7.31 rows=1 width=50) (actual time=0.033..0.033 rows=0 loops=1)

  • Join Filter: (tr.grp_id = r_6.recipient_id)
63. 0.001 0.032 ↓ 0.0 0 1

Nested Loop (cost=0.43..6.67 rows=1 width=16) (actual time=0.032..0.032 rows=0 loops=1)

64. 0.000 0.031 ↓ 0.0 0 1

Nested Loop (cost=0.14..4.86 rows=1 width=8) (actual time=0.031..0.031 rows=0 loops=1)

  • Join Filter: (tr.date_rng_id = dr.date_rng_id)
65. 0.005 0.031 ↓ 0.0 0 1

Nested Loop (cost=0.14..3.19 rows=1 width=16) (actual time=0.031..0.031 rows=0 loops=1)

66. 0.002 0.002 ↑ 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.002 rows=1 loops=1)

67. 0.024 0.024 ↓ 0.0 0 1

Index Scan using idx_tmp_rpmt1 on tmp_rpmt tr (cost=0.14..3.16 rows=1 width=24) (actual time=0.024..0.024 rows=0 loops=1)

  • Index Cond: (rpmt_id = p_3.person_id)
  • Filter: ((rpmt_type)::text = 'REPLACEMENT'::text)
68. 0.000 0.000 ↓ 0.0 0

Seq Scan on date_rng dr (cost=0.00..1.66 rows=1 width=8) (never executed)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
69. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grp_pk on grp g_4 (cost=0.29..1.80 rows=1 width=8) (never executed)

  • Index Cond: (grp_id = tr.grp_id)
  • Heap Fetches: 0
70. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_pk on recipients r_6 (cost=0.29..0.63 rows=1 width=42) (never executed)

  • Index Cond: (recipient_id = g_4.grp_id)
  • Filter: (deleted_id = '-1'::integer)
71. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships tm_4 (cost=0.29..0.32 rows=1 width=16) (never executed)

  • Index Cond: (recipient_id = g_4.grp_id)
  • Heap Fetches: 0
72. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=1.15..17.85 rows=6 width=115) (actual time=0.004..0.004 rows=0 loops=1)

73. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.86..15.89 rows=6 width=122) (actual time=0.003..0.003 rows=0 loops=1)

74. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.57..11.53 rows=7 width=80) (actual time=0.003..0.003 rows=0 loops=1)

75. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.29..9.28 rows=7 width=72) (actual time=0.002..0.002 rows=0 loops=1)

76. 0.002 0.002 ↓ 0.0 0 1

WorkTable Scan on temporary_rec t (cost=0.00..0.20 rows=5 width=72) (actual time=0.002..0.002 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.29..1.81 rows=1 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.29..0.32 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_pk on recipients r_7 (cost=0.29..0.62 rows=1 width=42) (never executed)

  • Index Cond: (recipient_id = tm_5.grp_id)
  • Filter: (deleted_id = '-1'::integer)
80. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grp_pk on grp g_5 (cost=0.29..0.31 rows=1 width=8) (never executed)

  • Index Cond: (grp_id = tm_5.grp_id)
  • Heap Fetches: 0
81.          

CTE retrieve_person_linked_recipients

82. 0.002 18.119 ↑ 5,666.7 3 1

Append (cost=0.00..510.00 rows=17,000 width=230) (actual time=0.737..18.119 rows=3 loops=1)

83. 0.735 0.735 ↓ 0.0 0 1

CTE Scan on group_rec (cost=0.00..3.86 rows=193 width=230) (actual time=0.735..0.735 rows=0 loops=1)

84. 0.001 0.003 ↓ 1.5 3 1

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

85. 0.002 0.002 ↓ 1.5 3 1

CTE Scan on person_and_devices_recipients (cost=0.00..0.04 rows=2 width=230) (actual time=0.001..0.002 rows=3 loops=1)

86. 17.339 17.339 ↓ 0.0 0 1

CTE Scan on supervisor_rec (cost=0.00..334.88 rows=16,744 width=230) (actual time=17.339..17.339 rows=0 loops=1)

87. 0.040 0.040 ↓ 0.0 0 1

CTE Scan on temporary_rec (cost=0.00..1.22 rows=61 width=230) (actual time=0.040..0.040 rows=0 loops=1)

88. 18.127 18.127 ↑ 5,666.7 3 1

CTE Scan on retrieve_person_linked_recipients (cost=0.00..340.00 rows=17,000 width=230) (actual time=0.741..18.127 rows=3 loops=1)

Planning time : 350.468 ms
Execution time : 19.251 ms