explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UmdR

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 36,762.396 ↓ 24.0 24 1

Limit (cost=60,011.33..60,011.66 rows=1 width=2,611) (actual time=36,762.194..36,762.396 rows=24 loops=1)

2. 0.225 36,762.357 ↓ 24.0 24 1

Result (cost=60,011.33..60,011.66 rows=1 width=2,611) (actual time=36,762.190..36,762.357 rows=24 loops=1)

3. 84.149 36,762.132 ↓ 24.0 24 1

Sort (cost=60,011.33..60,011.34 rows=1 width=2,579) (actual time=36,762.127..36,762.132 rows=24 loops=1)

  • Sort Key: pc.associated_date DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 66kB
4. 755.604 36,677.983 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..60,011.32 rows=1 width=2,579) (actual time=6,997.568..36,677.983 rows=33,540 loops=1)

  • Join Filter: (approver.id = offer_approval.user_account_id)
  • Rows Removed by Join Filter: 11805238
5. 239.365 34,916.179 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,924.36 rows=1 width=2,580) (actual time=6,997.189..34,916.179 rows=33,540 loops=1)

  • Join Filter: (offer.id = offer_approval.offer_id)
  • Rows Removed by Join Filter: 637255
6. 2,149.560 34,643.274 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,922.93 rows=1 width=2,572) (actual time=6,997.170..34,643.274 rows=33,540 loops=1)

  • Join Filter: (c.id = cgc.candidate_id)
  • Rows Removed by Join Filter: 66715445
7. 34.149 8,646.774 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,733.36 rows=1 width=2,578) (actual time=6,995.900..8,646.774 rows=33,540 loops=1)

8. 45.499 8,478.465 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,711.68 rows=1 width=2,570) (actual time=6,995.892..8,478.465 rows=33,540 loops=1)

9. 490.535 8,164.646 ↓ 33,540.0 33,540 1

Hash Left Join (cost=56,207.60..59,650.33 rows=1 width=2,562) (actual time=6,995.801..8,164.646 rows=33,540 loops=1)

  • Hash Cond: (pc.candidate_id = tgc.candidate_id)
  • Filter: ((tgc.team_group_id IS NULL) OR (tgc.team_group_id = 1133))
  • Rows Removed by Filter: 177105
10. 50.577 7,581.979 ↓ 199.5 135,095 1

Nested Loop Left Join (cost=43,372.61..44,532.45 rows=677 width=2,558) (actual time=6,901.956..7,581.979 rows=135,095 loops=1)

11. 126.233 7,396.307 ↓ 199.5 135,095 1

Nested Loop Left Join (cost=43,372.34..44,335.35 rows=677 width=2,545) (actual time=6,901.945..7,396.307 rows=135,095 loops=1)

12. 6.296 7,270.074 ↓ 199.5 135,094 1

Nested Loop Left Join (cost=43,372.19..44,216.63 rows=677 width=2,275) (actual time=6,901.931..7,270.074 rows=135,094 loops=1)

13. 131.444 7,128.684 ↓ 199.5 135,094 1

Merge Left Join (cost=43,372.05..44,099.20 rows=677 width=2,267) (actual time=6,901.906..7,128.684 rows=135,094 loops=1)

  • Merge Cond: (pc.candidate_id = cd.candidate_id)
14. 531.068 6,849.759 ↓ 199.5 135,094 1

Sort (cost=16,810.62..16,812.32 rows=677 width=2,226) (actual time=6,767.209..6,849.759 rows=135,094 loops=1)

  • Sort Key: pc.candidate_id
  • Sort Method: external merge Disk: 77136kB
15. 74.855 6,318.691 ↓ 199.5 135,094 1

Hash Left Join (cost=3,358.89..16,778.80 rows=677 width=2,226) (actual time=26.282..6,318.691 rows=135,094 loops=1)

  • Hash Cond: ((interview_1.id = ih.interview_id) AND (interview_1.interview_no = ih.interview_no))
16. 134.828 6,240.043 ↓ 199.5 135,084 1

Nested Loop Left Join (cost=2,874.04..16,269.28 rows=677 width=2,218) (actual time=22.435..6,240.043 rows=135,084 loops=1)

17. 154.862 5,970.132 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=2,873.47..15,794.04 rows=677 width=2,172) (actual time=22.427..5,970.132 rows=135,083 loops=1)

18. 62.528 5,680.187 ↓ 199.5 135,083 1

Hash Left Join (cost=2,873.18..15,573.43 rows=677 width=2,140) (actual time=22.363..5,680.187 rows=135,083 loops=1)

  • Hash Cond: (offer.id = offer_fee_model.offer_id)
19. 101.128 5,617.547 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=2,858.33..15,555.43 rows=677 width=2,128) (actual time=22.247..5,617.547 rows=135,083 loops=1)

20. 88.600 5,516.419 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=2,858.04..15,245.26 rows=677 width=2,072) (actual time=22.242..5,516.419 rows=135,083 loops=1)

21. 87.256 5,427.819 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=2,857.76..15,022.10 rows=677 width=2,058) (actual time=22.237..5,427.819 rows=135,083 loops=1)

22. 62.336 5,340.563 ↓ 199.5 135,083 1

Hash Left Join (cost=2,857.49..14,823.79 rows=677 width=2,041) (actual time=22.229..5,340.563 rows=135,083 loops=1)

  • Hash Cond: (pd.vertical_id = vertical.id)
23. 132.628 5,278.200 ↓ 199.5 135,083 1

Hash Left Join (cost=2,855.32..14,819.81 rows=677 width=2,023) (actual time=22.190..5,278.200 rows=135,083 loops=1)

  • Hash Cond: (pd.company_location_id = company_location.id)
24. 42.891 5,124.248 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=266.04..11,401.74 rows=677 width=1,955) (actual time=0.644..5,124.248 rows=135,083 loops=1)

25. 21.307 4,946.274 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=265.76..11,203.76 rows=677 width=1,938) (actual time=0.637..4,946.274 rows=135,083 loops=1)

26. 85.929 4,654.801 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=265.48..10,988.37 rows=677 width=1,893) (actual time=0.622..4,654.801 rows=135,083 loops=1)

27. 66.559 4,433.789 ↓ 199.5 135,083 1

Hash Left Join (cost=265.19..10,781.67 rows=677 width=1,785) (actual time=0.614..4,433.789 rows=135,083 loops=1)

  • Hash Cond: (c.candidate_source_id = candidate_source.id)
28. 54.294 4,367.213 ↓ 199.5 135,083 1

Hash Left Join (cost=263.36..10,777.86 rows=677 width=1,273) (actual time=0.594..4,367.213 rows=135,083 loops=1)

  • Hash Cond: (c.deleted_by_user_id = ua_deleted_by.id)
29. 125.037 4,312.834 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=176.46..10,689.19 rows=677 width=1,260) (actual time=0.504..4,312.834 rows=135,083 loops=1)

30. 142.120 4,052.714 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=176.32..10,579.15 rows=677 width=1,252) (actual time=0.502..4,052.714 rows=135,083 loops=1)

31. 49.620 3,235.179 ↓ 199.5 135,083 1

Hash Left Join (cost=175.89..10,215.93 rows=677 width=1,218) (actual time=0.475..3,235.179 rows=135,083 loops=1)

  • Hash Cond: (pc.sent_user_id = sent_user_account.id)
32. 57.008 3,185.469 ↓ 199.5 135,083 1

Hash Left Join (cost=89.00..10,127.25 rows=677 width=1,201) (actual time=0.380..3,185.469 rows=135,083 loops=1)

  • Hash Cond: (pc.shortlisted_user_id = shortlisted_user_account.id)
33. 33.318 3,128.286 ↓ 199.5 135,083 1

Nested Loop (cost=2.10..10,038.58 rows=677 width=1,184) (actual time=0.153..3,128.286 rows=135,083 loops=1)

  • Join Filter: (pc.candidate_id = c.id)
34. 63.687 1,597.153 ↓ 199.9 136,165 1

Nested Loop (cost=1.68..8,809.52 rows=681 width=994) (actual time=0.124..1,597.153 rows=136,165 loops=1)

35. 71.522 852.641 ↓ 199.9 136,165 1

Nested Loop (cost=1.25..8,404.09 rows=681 width=544) (actual time=0.107..852.641 rows=136,165 loops=1)

36. 5.594 368.543 ↓ 199.9 25,786 1

Nested Loop Left Join (cost=0.83..5,229.56 rows=129 width=147) (actual time=0.086..368.543 rows=25,786 loops=1)

37. 26.698 208.233 ↓ 199.9 25,786 1

Nested Loop Left Join (cost=0.42..4,233.44 rows=129 width=119) (actual time=0.069..208.233 rows=25,786 loops=1)

38. 52.605 52.605 ↓ 199.9 25,786 1

Seq Scan on position_description pd (cost=0.00..3,217.32 rows=129 width=102) (actual time=0.037..52.605 rows=25,786 loops=1)

  • Filter: (position_category = 1)
39. 128.930 128.930 ↑ 1.0 1 25,786

Index Scan using contact__pkey on contact (cost=0.42..7.88 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=25,786)

  • Index Cond: (id = pd.contact_id)
40. 154.716 154.716 ↑ 1.0 1 25,786

Index Scan using company__pkey on company (cost=0.42..7.72 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=25,786)

  • Index Cond: (id = pd.company_id)
41. 412.576 412.576 ↑ 1.8 5 25,786

Index Scan using index_pc_pd on position_candidate pc (cost=0.42..24.52 rows=9 width=405) (actual time=0.005..0.016 rows=5 loops=25,786)

  • Index Cond: (position_description_id = pd.id)
  • Filter: ((status >= 102) AND (associated_date <= now()) AND (associated_date >= (date_trunc('year'::text, now()) - '5 years'::interval)))
42. 680.825 680.825 ↑ 1.0 1 136,165

Index Scan using candidate_extension_candidate_id__pkey on candidate_extension ce (cost=0.42..0.60 rows=1 width=450) (actual time=0.005..0.005 rows=1 loops=136,165)

  • Index Cond: (candidate_id = pc.candidate_id)
43. 1,497.815 1,497.815 ↑ 1.0 1 136,165

Index Scan using candidate_pkey on candidate c (cost=0.42..1.79 rows=1 width=198) (actual time=0.011..0.011 rows=1 loops=136,165)

  • Index Cond: (id = ce.candidate_id)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 0
44. 0.042 0.175 ↓ 1.0 352 1

Hash (cost=82.51..82.51 rows=351 width=21) (actual time=0.175..0.175 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
45. 0.133 0.133 ↓ 1.0 352 1

Seq Scan on user_account shortlisted_user_account (cost=0.00..82.51 rows=351 width=21) (actual time=0.008..0.133 rows=352 loops=1)

46. 0.040 0.090 ↓ 1.0 352 1

Hash (cost=82.51..82.51 rows=351 width=21) (actual time=0.090..0.090 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
47. 0.050 0.050 ↓ 1.0 352 1

Seq Scan on user_account sent_user_account (cost=0.00..82.51 rows=351 width=21) (actual time=0.007..0.050 rows=352 loops=1)

48. 675.415 675.415 ↑ 1.0 1 135,083

Index Scan using common_location_pkey on common_location cl (cost=0.42..0.54 rows=1 width=46) (actual time=0.005..0.005 rows=1 loops=135,083)

  • Index Cond: (c.current_location_id = id)
49. 135.083 135.083 ↓ 0.0 0 135,083

Index Scan using country_pkey on country co (cost=0.14..0.16 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=135,083)

  • Index Cond: ((cl.country_code)::text = (code)::text)
50. 0.040 0.085 ↓ 1.0 352 1

Hash (cost=82.51..82.51 rows=351 width=21) (actual time=0.085..0.085 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
51. 0.045 0.045 ↓ 1.0 352 1

Seq Scan on user_account ua_deleted_by (cost=0.00..82.51 rows=351 width=21) (actual time=0.005..0.045 rows=352 loops=1)

52. 0.006 0.017 ↓ 1.0 38 1

Hash (cost=1.37..1.37 rows=37 width=520) (actual time=0.017..0.017 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
53. 0.011 0.011 ↓ 1.0 38 1

Seq Scan on candidate_source (cost=0.00..1.37 rows=37 width=520) (actual time=0.009..0.011 rows=38 loops=1)

54. 135.083 135.083 ↓ 0.0 0 135,083

Index Scan using offer_valid_position_candidate_id__unq on offer (cost=0.28..0.31 rows=1 width=112) (actual time=0.001..0.001 rows=0 loops=135,083)

  • Index Cond: (pc.id = position_candidate_id)
55. 270.166 270.166 ↑ 1.0 1 135,083

Index Scan using position_extension__pkey on position_extension pe (cost=0.29..0.32 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=135,083)

  • Index Cond: (pc.position_description_id = position_id)
56. 135.083 135.083 ↑ 1.0 1 135,083

Index Scan using client_account_pkey on user_account ua (cost=0.27..0.29 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=135,083)

  • Index Cond: (id = pc.created_by)
57. 8.011 21.324 ↑ 1.0 42,546 1

Hash (cost=1,558.46..1,558.46 rows=42,546 width=72) (actual time=21.324..21.324 rows=42,546 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2552kB
58. 13.313 13.313 ↑ 1.0 42,546 1

Seq Scan on company_location (cost=0.00..1,558.46 rows=42,546 width=72) (actual time=0.017..13.313 rows=42,546 loops=1)

59. 0.011 0.027 ↑ 1.0 52 1

Hash (cost=1.52..1.52 rows=52 width=22) (actual time=0.027..0.027 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
60. 0.016 0.016 ↑ 1.0 52 1

Seq Scan on vertical (cost=0.00..1.52 rows=52 width=22) (actual time=0.012..0.016 rows=52 loops=1)

61. 0.000 0.000 ↓ 0.0 0 135,083

Index Scan using client_account_pkey on user_account (cost=0.27..0.29 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=135,083)

  • Index Cond: (company.company_owner_id = id)
62. 0.000 0.000 ↓ 0.0 0 135,083

Index Scan using invoice_offer_id__unq on invoice (cost=0.28..0.33 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=135,083)

  • Index Cond: (offer_id = offer.id)
63. 0.000 0.000 ↓ 0.0 0 135,083

Index Scan using offer_personal_info__offer__fkey on offer_personal_info (cost=0.28..0.46 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=135,083)

  • Index Cond: (offer_id = offer.id)
64. 0.045 0.112 ↑ 1.0 438 1

Hash (cost=9.38..9.38 rows=438 width=16) (actual time=0.112..0.112 rows=438 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
65. 0.067 0.067 ↑ 1.0 438 1

Seq Scan on offer_fee_model (cost=0.00..9.38 rows=438 width=16) (actual time=0.010..0.067 rows=438 loops=1)

66. 135.083 135.083 ↑ 1.0 1 135,083

Index Scan using compensation_position_id__uidx on compensation (cost=0.29..0.33 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=135,083)

  • Index Cond: (pc.position_description_id = position_id)
67. 0.000 135.083 ↓ 0.0 0 135,083

Nested Loop Left Join (cost=0.57..0.69 rows=1 width=50) (actual time=0.001..0.001 rows=0 loops=135,083)

68. 135.083 135.083 ↓ 0.0 0 135,083

Index Scan using interview_position_candidate_id on interview interview_1 (cost=0.29..0.31 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=135,083)

  • Index Cond: (position_candidate_id = pc.id)
  • Filter: ((pc.status - interview_no) = 103)
  • Rows Removed by Filter: 0
69. 26.739 26.739 ↑ 1.0 1 8,913

Index Scan using interview_time__interview_id_fkey on interview_time (cost=0.29..0.36 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=8,913)

  • Index Cond: (interview_1.id = interview_id)
  • Filter: ((selected = 1) OR (index_num = 1))
  • Rows Removed by Filter: 0
70. 1.564 3.793 ↑ 1.0 12,594 1

Hash (cost=295.94..295.94 rows=12,594 width=24) (actual time=3.792..3.793 rows=12,594 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 857kB
71. 2.229 2.229 ↑ 1.0 12,594 1

Seq Scan on interview_history ih (cost=0.00..295.94 rows=12,594 width=24) (actual time=0.016..2.229 rows=12,594 loops=1)

72. 6.092 147.481 ↓ 1.5 52,536 1

Unique (cost=26,561.42..26,837.78 rows=35,488 width=53) (actual time=134.490..147.481 rows=52,536 loops=1)

73. 42.174 141.389 ↑ 1.0 54,059 1

Sort (cost=26,561.42..26,699.60 rows=55,271 width=53) (actual time=134.488..141.389 rows=54,059 loops=1)

  • Sort Key: cd.candidate_id, cd.created DESC
  • Sort Method: external merge Disk: 3496kB
74. 94.605 99.215 ↑ 1.0 54,062 1

Bitmap Heap Scan on candidate_document cd (cost=1,072.78..20,317.66 rows=55,271 width=53) (actual time=5.982..99.215 rows=54,062 loops=1)

  • Recheck Cond: ((document_type)::text = 'candidate_photo'::text)
  • Heap Blocks: exact=12845
75. 4.610 4.610 ↑ 1.0 54,062 1

Bitmap Index Scan on candidate_document_document_type_idx (cost=0.00..1,058.96 rows=55,271 width=0) (actual time=4.610..4.610 rows=54,062 loops=1)

  • Index Cond: ((document_type)::text = 'candidate_photo'::text)
76. 135.094 135.094 ↓ 0.0 0 135,094

Index Scan using compensation_fee_model_compensation_id__fkey on compensation_fee_model (cost=0.14..0.16 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=135,094)

  • Index Cond: (compensation_id = compensation.id)
77. 0.000 0.000 ↓ 0.0 0 135,094

Index Scan using company_response__position_candidate_id__fkey on position_candidate_company_response (cost=0.15..0.17 rows=1 width=274) (actual time=0.000..0.000 rows=0 loops=135,094)

  • Index Cond: (position_candidate_id = pc.id)
78. 135.095 135.095 ↑ 1.0 1 135,095

Index Scan using client_account_pkey on user_account user_temp (cost=0.27..0.29 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=135,095)

  • Index Cond: (c.user_account_id = id)
79. 45.766 92.132 ↑ 1.0 369,418 1

Hash (cost=6,411.66..6,411.66 rows=369,466 width=12) (actual time=92.132..92.132 rows=369,418 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3207kB
80. 46.366 46.366 ↑ 1.0 369,418 1

Seq Scan on team_group_candidate tgc (cost=0.00..6,411.66 rows=369,466 width=12) (actual time=0.024..46.366 rows=369,418 loops=1)

81. 0.000 268.320 ↓ 0.0 0 33,540

GroupAggregate (cost=21.99..61.34 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=33,540)

  • Group Key: cd_1.candidate_id
82. 33.540 268.320 ↓ 0.0 0 33,540

Result (cost=21.99..61.32 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=33,540)

  • One-Time Filter: (pc.status >= 102)
83. 67.012 234.780 ↓ 0.0 0 33,540

Hash Join (cost=21.99..61.32 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=33,540)

  • Hash Cond: (cd_1.document_types_id = odt.document_type_id)
84. 0.068 0.068 ↑ 14.0 1 1

Index Scan using candidate_document_candidate_id_idx on candidate_document cd_1 (cost=0.42..39.69 rows=14 width=8) (actual time=0.068..0.068 rows=1 loops=1)

  • Index Cond: (pc.candidate_id = candidate_id)
85. 167.700 167.700 ↓ 0.0 0 33,540

Hash (cost=21.50..21.50 rows=5 width=4) (actual time=0.005..0.005 rows=0 loops=33,540)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
86. 0.000 0.000 ↓ 0.0 0 33,540

Seq Scan on onboarding_document_type odt (cost=0.00..21.50 rows=5 width=4) (actual time=0.000..0.000 rows=0 loops=33,540)

  • Filter: (pc.position_description_id = job_id)
87. 134.160 134.160 ↓ 0.0 0 33,540

GroupAggregate (cost=0.00..21.57 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=33,540)

  • Group Key: onboarding_document_type.job_id
88. 0.000 0.000 ↓ 0.0 0 33,540

Seq Scan on onboarding_document_type (cost=0.00..21.50 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=33,540)

  • Filter: (job_id = pc.position_description_id)
89. 23,846.940 23,846.940 ↑ 1.0 1,989 33,540

Seq Scan on candidate_gdpr_compliance cgc (cost=0.00..164.46 rows=2,009 width=6) (actual time=0.002..0.711 rows=1,989 loops=33,540)

  • Filter: (portal_status IS NOT NULL)
  • Rows Removed by Filter: 5090
90. 33.540 33.540 ↑ 1.0 19 33,540

Seq Scan on offer_approval (cost=0.00..1.19 rows=19 width=12) (actual time=0.001..0.001 rows=19 loops=33,540)

91. 1,006.200 1,006.200 ↓ 1.0 352 33,540

Seq Scan on user_account approver (cost=0.00..82.51 rows=351 width=21) (actual time=0.000..0.030 rows=352 loops=33,540)