explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zXLn

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 22.029 ↑ 21,866,450.0 2 1

Append (cost=11,982,773.84..12,864,820.03 rows=43,732,900 width=200) (actual time=1.044..22.029 rows=2 loops=1)

2.          

CTE recip

3. 0.044 0.169 ↑ 8,681.0 1 1

Recursive Union (cost=0.43..33,956.07 rows=8,681 width=120) (actual time=0.080..0.169 rows=1 loops=1)

4. 0.037 0.037 ↑ 1.0 1 1

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r_1 (cost=0.43..3.45 rows=1 width=120) (actual time=0.036..0.037 rows=1 loops=1)

  • Index Cond: (recipient_id = ANY ('{1947001}'::bigint[]))
5. 0.001 0.088 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.03..3,377.90 rows=868 width=120) (actual time=0.088..0.088 rows=0 loops=1)

  • Filter: (COALESCE(r_memofteam.recipient_id, r_memofgroup.recipient_id) IS NOT NULL)
  • Rows Removed by Filter: 1
6. 0.002 0.086 ↑ 872.0 1 1

Nested Loop Left Join (cost=3.60..1,475.12 rows=872 width=118) (actual time=0.085..0.086 rows=1 loops=1)

7. 0.001 0.061 ↑ 17.0 1 1

Nested Loop Left Join (cost=1.26..116.87 rows=17 width=102) (actual time=0.060..0.061 rows=1 loops=1)

8. 0.001 0.058 ↑ 17.0 1 1

Nested Loop Left Join (cost=0.84..80.14 rows=17 width=96) (actual time=0.058..0.058 rows=1 loops=1)

9. 0.002 0.032 ↑ 17.0 1 1

Nested Loop Left Join (cost=0.42..48.27 rows=17 width=96) (actual time=0.032..0.032 rows=1 loops=1)

10. 0.004 0.004 ↑ 10.0 1 1

WorkTable Scan on recip recip_1 (cost=0.00..0.30 rows=10 width=72) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (NOT (ARRAY[chain_ids[array_upper(chain_ids, 1)]] <@ chain_ids[1:(array_upper(chain_ids, 1) - 1)]))
11. 0.026 0.026 ↓ 0.0 0 1

Index Scan using idx_grp_tf1 on grp_tf gtf (cost=0.42..4.78 rows=2 width=24) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: (recip_1.recipient_id = grp_id)
  • Filter: (deleted_id = '-1'::integer)
12. 0.025 0.025 ↓ 0.0 0 1

Index Scan using tm_pk on tm t (cost=0.42..1.87 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: (gtf.tm_id = tm_id)
  • Filter: ((deleted_id = '-1'::integer) AND (grp_id = gtf.grp_id))
13. 0.002 0.002 ↓ 0.0 0 1

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_memofgroup (cost=0.43..2.16 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (t.tm_id = recipient_id)
14. 0.002 0.023 ↓ 0.0 0 1

Bitmap Heap Scan on tm_mbrships tm (cost=2.34..79.37 rows=53 width=24) (actual time=0.022..0.023 rows=0 loops=1)

  • Recheck Cond: (recip_1.recipient_id = tm_id)
15. 0.021 0.021 ↓ 0.0 0 1

Bitmap Index Scan on tm_mbrships_pk (cost=0.00..2.32 rows=53 width=0) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (recip_1.recipient_id = tm_id)
16. 0.001 0.001 ↓ 0.0 0 1

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_memofteam (cost=0.43..1.91 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (tm.recipient_id = recipient_id)
17.          

CTE all_persons

18. 0.000 0.002 ↑ 43,043.0 1 1

Append (cost=0.25..1,681.33 rows=43,043 width=80) (actual time=0.001..0.002 rows=1 loops=1)

19. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.25..1,055.57 rows=43,000 width=80) (actual time=0.001..0.001 rows=0 loops=1)

20. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on recip recip_2 (cost=0.00..195.32 rows=43 width=80) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (recipient_cat = 'DYNAMIC_TEAM'::text)
  • Rows Removed by Filter: 1
21. 0.000 0.000 ↓ 0.0 0

Function Scan on retrieve_sortable_people_from_dynamic_team dyt (cost=0.25..10.25 rows=1,000 width=72) (never executed)

22. 0.001 0.001 ↑ 43.0 1 1

CTE Scan on recip recip_3 (cost=0.00..195.32 rows=43 width=80) (actual time=0.001..0.001 rows=1 loops=1)

  • Filter: (recipient_cat = 'PERSON'::text)
23.          

CTE replacements

24. 0.001 0.168 ↓ 0.0 0 1

Nested Loop (cost=0.25..861,721.11 rows=43,043,000 width=112) (actual time=0.168..0.168 rows=0 loops=1)

25. 0.004 0.004 ↑ 43,043.0 1 1

CTE Scan on all_persons (cost=0.00..860.86 rows=43,043 width=72) (actual time=0.003..0.004 rows=1 loops=1)

26. 0.163 0.163 ↓ 0.0 0 1

Function Scan on retrieve_replacements rr (cost=0.25..10.25 rows=1,000 width=112) (actual time=0.163..0.163 rows=0 loops=1)

27.          

CTE replaced

28. 0.001 0.937 ↑ 43,075,282.0 1 1

Append (cost=0.00..3,204,083.93 rows=43,075,282 width=112) (actual time=0.826..0.937 rows=1 loops=1)

29. 0.169 0.169 ↓ 0.0 0 1

CTE Scan on replacements rpl (cost=0.00..860,860.00 rows=43,043,000 width=112) (actual time=0.169..0.169 rows=0 loops=1)

30. 0.764 0.767 ↑ 32,282.0 1 1

Hash Anti Join (cost=1,842,779.00..2,342,901.11 rows=32,282 width=112) (actual time=0.657..0.767 rows=1 loops=1)

  • Hash Cond: ((ap.recipient_id = repl.replaced_person_id) AND (ap.chain_ids[1:(array_length(ap.chain_ids, 1) - 1)] = repl.chain_ids[1:(array_length(repl.chain_ids, 1) - 1)]))
31. 0.001 0.001 ↑ 43,043.0 1 1

CTE Scan on all_persons ap (cost=0.00..860.86 rows=43,043 width=72) (actual time=0.001..0.001 rows=1 loops=1)

32. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=860,860.00..860,860.00 rows=43,043,000 width=40) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 262144 Batches: 256 Memory Usage: 2048kB
33. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on replacements repl (cost=0.00..860,860.00 rows=43,043,000 width=40) (actual time=0.001..0.001 rows=0 loops=1)

34.          

CTE devices

35. 18.340 20.867 ↑ 66,563.0 1 1

Hash Join (cost=1,946,811.60..5,974,109.03 rows=66,563 width=80) (actual time=19.688..20.867 rows=1 loops=1)

  • Hash Cond: (d_1.person_id = repl_1.replacement_person_id)
36. 2.347 2.472 ↑ 1.4 460 1

Bitmap Heap Scan on dvc d_1 (cost=9.93..949.04 rows=646 width=24) (actual time=0.171..2.472 rows=460 loops=1)

  • Recheck Cond: ((org_uuid = 'cbb40e91-1659-4cf6-8d9e-df215cc48d6a'::uuid) AND (deleted_id = '-1'::integer))
  • Heap Blocks: exact=350
37. 0.125 0.125 ↑ 1.4 460 1

Bitmap Index Scan on idx_dvc_org_uuid_person_id_not_deleted (cost=0.00..9.77 rows=646 width=0) (actual time=0.125..0.125 rows=460 loops=1)

  • Index Cond: (org_uuid = 'cbb40e91-1659-4cf6-8d9e-df215cc48d6a'::uuid)
38. 0.053 0.055 ↑ 43,075,282.0 1 1

Hash (cost=861,505.64..861,505.64 rows=43,075,282 width=80) (actual time=0.055..0.055 rows=1 loops=1)

  • Buckets: 262144 Batches: 512 Memory Usage: 2048kB
39. 0.002 0.002 ↑ 43,075,282.0 1 1

CTE Scan on replaced repl_1 (cost=0.00..861,505.64 rows=43,075,282 width=80) (actual time=0.002..0.002 rows=1 loops=1)

40.          

CTE teamschedule

41. 0.001 0.172 ↓ 0.0 0 1

Nested Loop (cost=0.25..172,933.87 rows=8,638,000 width=104) (actual time=0.171..0.172 rows=0 loops=1)

42. 0.171 0.171 ↓ 0.0 0 1

CTE Scan on recip recip_4 (cost=0.00..173.62 rows=8,638 width=8) (actual time=0.171..0.171 rows=0 loops=1)

  • Filter: (grp_id IS NOT NULL)
  • Rows Removed by Filter: 1
43. 0.000 0.000 ↓ 0.0 0

Function Scan on retrieve_team_schedules rts (cost=0.25..10.25 rows=1,000 width=104) (never executed)

44. 0.000 0.215 ↓ 0.0 0 1

Unique (cost=1,734,288.51..1,747,587.24 rows=591,055 width=200) (actual time=0.215..0.215 rows=0 loops=1)

45. 0.010 0.215 ↓ 0.0 0 1

Sort (cost=1,734,288.51..1,735,766.14 rows=591,055 width=200) (actual time=0.215..0.215 rows=0 loops=1)

  • Sort Key: recip.recipient_id, recip.chain_ids, recip.sort_chain_ids, ts.grp_tf_id, ts.drs_id, ts.date_rule_id, ts.incorexc, ts.chain_ids
  • Sort Method: quicksort Memory: 25kB
46. 0.001 0.205 ↓ 0.0 0 1

Merge Right Join (cost=1,496,916.34..1,641,262.72 rows=591,055 width=200) (actual time=0.205..0.205 rows=0 loops=1)

  • Merge Cond: ((ts.group_id = (recip.chain_ids[(array_length(recip.chain_ids, 1) - 1)])) AND (ts.team_id = (recip.chain_ids[array_length(recip.chain_ids, 1)])))
47. 0.004 0.176 ↓ 0.0 0 1

Sort (cost=1,471,635.71..1,493,230.71 rows=8,638,000 width=104) (actual time=0.176..0.176 rows=0 loops=1)

  • Sort Key: ts.group_id, ts.team_id
  • Sort Method: quicksort Memory: 25kB
48. 0.172 0.172 ↓ 0.0 0 1

CTE Scan on teamschedule ts (cost=0.00..172,760.00 rows=8,638,000 width=104) (actual time=0.172..0.172 rows=0 loops=1)

49. 0.003 0.028 ↓ 0.0 0 1

Sort (cost=25,280.63..25,287.47 rows=2,737 width=72) (actual time=0.028..0.028 rows=0 loops=1)

  • Sort Key: (recip.chain_ids[(array_length(recip.chain_ids, 1) - 1)]), (recip.chain_ids[array_length(recip.chain_ids, 1)])
  • Sort Method: quicksort Memory: 25kB
50. 0.001 0.025 ↓ 0.0 0 1

Nested Loop (cost=0.43..25,124.37 rows=2,737 width=72) (actual time=0.025..0.025 rows=0 loops=1)

51. 0.001 0.001 ↑ 8,681.0 1 1

CTE Scan on recip (cost=0.00..173.62 rows=8,681 width=72) (actual time=0.001..0.001 rows=1 loops=1)

52. 0.023 0.023 ↓ 0.0 0 1

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients rorg (cost=0.43..2.87 rows=1 width=8) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: (recipient_id = recip.recipient_id)
  • Filter: ((recipient_cat)::text <> 'PERSON'::text)
  • Rows Removed by Filter: 1
53. 0.940 0.940 ↑ 43,075,282.0 1 1

CTE Scan on replaced r (cost=0.00..861,505.64 rows=43,075,282 width=200) (actual time=0.828..0.940 rows=1 loops=1)

54. 20.873 20.873 ↑ 66,563.0 1 1

CTE Scan on devices d (cost=0.00..1,331.26 rows=66,563 width=200) (actual time=19.693..20.873 rows=1 loops=1)

Planning time : 3.130 ms
Execution time : 22.376 ms