explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o7tp

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 0.635 ↑ 40.0 5 1

HashAggregate (cost=3,097.52..3,099.52 rows=200 width=230) (actual time=0.633..0.635 rows=5 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 person_and_devices_recipients

3. 0.001 0.402 ↓ 1.2 5 1

Append (cost=0.83..33.33 rows=4 width=30) (actual time=0.155..0.402 rows=5 loops=1)

4. 0.012 0.291 ↓ 1.3 4 1

Nested Loop (cost=0.83..26.54 rows=3 width=30) (actual time=0.155..0.291 rows=4 loops=1)

5. 0.127 0.127 ↑ 1.2 4 1

Index Scan using idx_dvc2 on dvc (cost=0.42..9.38 rows=5 width=8) (actual time=0.079..0.127 rows=4 loops=1)

  • Index Cond: (person_id = '417371'::bigint)
6. 0.152 0.152 ↑ 1.0 1 4

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r (cost=0.41..3.43 rows=1 width=30) (actual time=0.038..0.038 rows=1 loops=4)

  • Index Cond: (recipient_id = dvc.dvc_id)
7. 0.002 0.110 ↑ 1.0 1 1

Nested Loop (cost=0.70..6.75 rows=1 width=30) (actual time=0.109..0.110 rows=1 loops=1)

8. 0.080 0.080 ↑ 1.0 1 1

Index Only Scan using persons_person_id_upper_first_name_upper_last_name_fbi on persons p (cost=0.29..3.31 rows=1 width=8) (actual time=0.079..0.080 rows=1 loops=1)

  • Index Cond: (person_id = '417371'::bigint)
  • Heap Fetches: 1
9. 0.028 0.028 ↑ 1.0 1 1

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_1 (cost=0.41..3.43 rows=1 width=30) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: (recipient_id = '417371'::bigint)
10.          

CTE group_rec

11. 0.000 0.479 ↓ 0.0 0 1

Recursive Union (cost=1.34..2,994.12 rows=647 width=103) (actual time=0.479..0.479 rows=0 loops=1)

12. 0.000 0.477 ↓ 0.0 0 1

Nested Loop (cost=1.34..36.50 rows=7 width=103) (actual time=0.477..0.477 rows=0 loops=1)

13. 0.001 0.477 ↓ 0.0 0 1

Nested Loop (cost=1.07..34.44 rows=7 width=46) (actual time=0.477..0.477 rows=0 loops=1)

14. 0.000 0.476 ↓ 0.0 0 1

Nested Loop (cost=0.65..14.90 rows=13 width=16) (actual time=0.476..0.476 rows=0 loops=1)

15. 0.004 0.476 ↓ 0.0 0 1

Nested Loop (cost=0.37..10.58 rows=13 width=8) (actual time=0.476..0.476 rows=0 loops=1)

16. 0.010 0.417 ↓ 1.2 5 1

HashAggregate (cost=0.09..0.13 rows=4 width=8) (actual time=0.416..0.417 rows=5 loops=1)

  • Group Key: person_and_devices_recipients.recipient_id
17. 0.407 0.407 ↓ 1.2 5 1

CTE Scan on person_and_devices_recipients (cost=0.00..0.08 rows=4 width=8) (actual time=0.157..0.407 rows=5 loops=1)

18. 0.055 0.055 ↓ 0.0 0 5

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m (cost=0.28..2.58 rows=3 width=16) (actual time=0.011..0.011 rows=0 loops=5)

  • Index Cond: (recipient_id = person_and_devices_recipients.recipient_id)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_2 (cost=0.41..1.50 rows=1 width=30) (never executed)

  • Index Cond: (recipient_id = tm.grp_id)
21. 0.000 0.000 ↓ 0.0 0

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

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

Nested Loop (cost=1.25..294.47 rows=64 width=103) (actual time=0.001..0.002 rows=0 loops=1)

23. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.98..274.52 rows=64 width=110) (actual time=0.001..0.001 rows=0 loops=1)

24. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.56..109.14 rows=110 width=80) (actual time=0.001..0.001 rows=0 loops=1)

25. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.28..72.65 rows=110 width=72) (actual time=0.001..0.001 rows=0 loops=1)

26. 0.001 0.001 ↓ 0.0 0 1

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

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

Index Only Scan using idx_tm_mbrships_mbr on tm_mbrships m_1 (cost=0.28..2.01 rows=3 width=16) (never executed)

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

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

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

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_3 (cost=0.41..1.50 rows=1 width=30) (never executed)

  • Index Cond: (recipient_id = tm_1.grp_id)
30. 0.000 0.000 ↓ 0.0 0

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

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

CTE supervisor_rec

32. 0.001 0.050 ↓ 0.0 0 1

Nested Loop (cost=2.77..15.01 rows=1 width=30) (actual time=0.050..0.050 rows=0 loops=1)

33. 0.000 0.049 ↓ 0.0 0 1

Nested Loop (cost=2.48..11.70 rows=1 width=38) (actual time=0.049..0.049 rows=0 loops=1)

  • Join Filter: (gs.grp_id = g_2.grp_id)
34. 0.000 0.049 ↓ 0.0 0 1

Nested Loop (cost=2.21..11.39 rows=1 width=46) (actual time=0.049..0.049 rows=0 loops=1)

35. 0.008 0.049 ↓ 0.0 0 1

Bitmap Heap Scan on grp_sups gs (cost=1.79..4.53 rows=2 width=16) (actual time=0.049..0.049 rows=0 loops=1)

  • Recheck Cond: (person_id = '417371'::bigint)
36. 0.041 0.041 ↓ 0.0 0 1

Bitmap Index Scan on idx_grp_sups2 (cost=0.00..1.79 rows=2 width=0) (actual time=0.041..0.041 rows=0 loops=1)

  • Index Cond: (person_id = '417371'::bigint)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_4 (cost=0.41..3.43 rows=1 width=30) (never executed)

  • Index Cond: (recipient_id = gs.grp_id)
38. 0.000 0.000 ↓ 0.0 0

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

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

Index Only Scan using persons_person_id_upper_first_name_upper_last_name_fbi on persons p_1 (cost=0.29..3.31 rows=1 width=8) (never executed)

  • Index Cond: (person_id = '417371'::bigint)
  • Heap Fetches: 0
40.          

CTE temporary_rec

41. 0.000 0.085 ↓ 0.0 0 1

Group (cost=15.86..15.87 rows=1 width=30) (actual time=0.085..0.085 rows=0 loops=1)

  • Group Key: r_5.recipient_id
42. 0.060 0.085 ↓ 0.0 0 1

Sort (cost=15.86..15.87 rows=1 width=30) (actual time=0.085..0.085 rows=0 loops=1)

  • Sort Key: r_5.recipient_id
  • Sort Method: quicksort Memory: 25kB
43. 0.000 0.025 ↓ 0.0 0 1

Nested Loop (cost=2.50..15.85 rows=1 width=30) (actual time=0.025..0.025 rows=0 loops=1)

  • Join Filter: (t.grp_id = r_5.recipient_id)
44. 0.000 0.025 ↓ 0.0 0 1

Nested Loop (cost=2.09..14.33 rows=2 width=16) (actual time=0.025..0.025 rows=0 loops=1)

45. 0.001 0.025 ↓ 0.0 0 1

Bitmap Heap Scan on tmp_rpmt t (cost=1.80..5.91 rows=3 width=8) (actual time=0.025..0.025 rows=0 loops=1)

  • Recheck Cond: (rpmt_id = '417371'::bigint)
46. 0.024 0.024 ↓ 0.0 0 1

Bitmap Index Scan on idx_tmp_rpmt1 (cost=0.00..1.80 rows=3 width=0) (actual time=0.024..0.024 rows=0 loops=1)

  • Index Cond: (rpmt_id = '417371'::bigint)
47. 0.000 0.000 ↓ 0.0 0

Index Only Scan using persons_person_id_upper_first_name_upper_last_name_fbi on persons p_2 (cost=0.29..2.81 rows=1 width=8) (never executed)

  • Index Cond: (person_id = t.grp_id)
  • Heap Fetches: 0
48. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_5 (cost=0.41..0.75 rows=1 width=30) (never executed)

  • Index Cond: (recipient_id = p_2.person_id)
49.          

CTE retrieve_person_linked_recipients

50. 0.002 0.619 ↑ 130.6 5 1

Append (cost=0.00..13.06 rows=653 width=230) (actual time=0.481..0.619 rows=5 loops=1)

51. 0.479 0.479 ↓ 0.0 0 1

CTE Scan on group_rec (cost=0.00..12.94 rows=647 width=230) (actual time=0.479..0.479 rows=0 loops=1)

52. 0.002 0.002 ↓ 1.2 5 1

CTE Scan on person_and_devices_recipients person_and_devices_recipients_1 (cost=0.00..0.08 rows=4 width=230) (actual time=0.001..0.002 rows=5 loops=1)

53. 0.050 0.050 ↓ 0.0 0 1

CTE Scan on supervisor_rec (cost=0.00..0.02 rows=1 width=230) (actual time=0.050..0.050 rows=0 loops=1)

54. 0.086 0.086 ↓ 0.0 0 1

CTE Scan on temporary_rec (cost=0.00..0.02 rows=1 width=230) (actual time=0.086..0.086 rows=0 loops=1)

55. 0.625 0.625 ↑ 130.6 5 1

CTE Scan on retrieve_person_linked_recipients (cost=0.00..13.06 rows=653 width=230) (actual time=0.485..0.625 rows=5 loops=1)

Planning time : 16.263 ms