explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J939

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 21,366.662 ↓ 24.0 24 1

Limit (cost=59,969.28..59,969.61 rows=1 width=2,611) (actual time=21,366.422..21,366.662 rows=24 loops=1)

2. 0.320 21,366.616 ↓ 24.0 24 1

Result (cost=59,969.28..59,969.61 rows=1 width=2,611) (actual time=21,366.420..21,366.616 rows=24 loops=1)

3. 93.200 21,366.296 ↓ 24.0 24 1

Sort (cost=59,969.28..59,969.28 rows=1 width=2,579) (actual time=21,366.289..21,366.296 rows=24 loops=1)

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

Nested Loop Left Join (cost=56,229.87..59,969.27 rows=1 width=2,579) (actual time=7,556.525..21,273.096 rows=33,540 loops=1)

  • Join Filter: (approver.id = offer_approval.user_account_id)
  • Rows Removed by Join Filter: 11805238
5. 275.207 19,406.906 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.87..59,882.30 rows=1 width=2,580) (actual time=7,556.119..19,406.906 rows=33,540 loops=1)

  • Join Filter: (offer.id = offer_approval.offer_id)
  • Rows Removed by Join Filter: 637255
6. 3,015.783 19,098.159 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.87..59,880.87 rows=1 width=2,572) (actual time=7,556.101..19,098.159 rows=33,540 loops=1)

  • Join Filter: (c.id = cgc.candidate_id)
  • Rows Removed by Join Filter: 66708231
7. 37.883 9,307.296 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,733.36 rows=1 width=2,578) (actual time=7,555.211..9,307.296 rows=33,540 loops=1)

8. 43.109 9,135.253 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,711.68 rows=1 width=2,570) (actual time=7,555.203..9,135.253 rows=33,540 loops=1)

9. 516.349 8,790.284 ↓ 33,540.0 33,540 1

Hash Left Join (cost=56,207.60..59,650.33 rows=1 width=2,562) (actual time=7,555.133..8,790.284 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. 64.001 8,173.781 ↓ 199.5 135,095 1

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

11. 132.163 7,974.685 ↓ 199.5 135,095 1

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

12. 13.425 7,842.522 ↓ 199.5 135,094 1

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

13. 138.254 7,694.003 ↓ 199.5 135,094 1

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

  • Merge Cond: (pc.candidate_id = cd.candidate_id)
14. 575.304 7,400.140 ↓ 199.5 135,094 1

Sort (cost=16,810.62..16,812.32 rows=677 width=2,226) (actual time=7,311.527..7,400.140 rows=135,094 loops=1)

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

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

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

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

17. 44.020 6,441.581 ↓ 199.5 135,083 1

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

18. 65.877 6,127.395 ↓ 199.5 135,083 1

Hash Left Join (cost=2,873.18..15,573.43 rows=677 width=2,140) (actual time=23.233..6,127.395 rows=135,083 loops=1)

  • Hash Cond: (offer.id = offer_fee_model.offer_id)
19. 110.268 6,061.407 ↓ 199.5 135,083 1

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

20. 96.001 5,951.139 ↓ 199.5 135,083 1

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

21. 94.556 5,855.138 ↓ 199.5 135,083 1

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

22. 68.611 5,760.582 ↓ 199.5 135,083 1

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

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

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

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

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

25. 41.176 5,327.877 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=265.76..11,203.76 rows=677 width=1,938) (actual time=0.680..5,327.877 rows=135,083 loops=1)

26. 113.293 5,016.535 ↓ 199.5 135,083 1

Nested Loop Left Join (cost=265.48..10,988.37 rows=677 width=1,893) (actual time=0.664..5,016.535 rows=135,083 loops=1)

27. 72.827 4,768.159 ↓ 199.5 135,083 1

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

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

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

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

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

30. 64.344 4,345.458 ↓ 199.5 135,083 1

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

31. 53.418 3,470.616 ↓ 199.5 135,083 1

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

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

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

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

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

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

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

35. 85.063 925.698 ↓ 199.9 136,165 1

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

36. 19.239 402.273 ↓ 199.9 25,786 1

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

37. 13.681 228.318 ↓ 199.9 25,786 1

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

38. 59.921 59.921 ↓ 199.9 25,786 1

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

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

Index Scan using contact__pkey on contact (cost=0.42..7.88 rows=1 width=21) (actual time=0.006..0.006 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. 438.362 438.362 ↑ 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.017 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.048 0.176 ↓ 1.0 352 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
45. 0.128 0.128 ↓ 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.128 rows=352 loops=1)

46. 0.041 0.105 ↓ 1.0 352 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
47. 0.064 0.064 ↓ 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.064 rows=352 loops=1)

48. 810.498 810.498 ↑ 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.006..0.006 rows=1 loops=135,083)

  • Index Cond: (c.current_location_id = id)
49. 270.166 270.166 ↓ 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.002..0.002 rows=0 loops=135,083)

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

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

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

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

52. 0.006 0.034 ↓ 1.0 38 1

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

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

Seq Scan on candidate_source (cost=0.00..1.37 rows=37 width=520) (actual time=0.026..0.028 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. 7.892 22.149 ↑ 1.0 42,546 1

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

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

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

59. 0.012 0.029 ↑ 1.0 52 1

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

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

Seq Scan on vertical (cost=0.00..1.52 rows=52 width=22) (actual time=0.013..0.017 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.043 0.111 ↑ 1.0 438 1

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

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

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

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

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

Nested Loop Left Join (cost=0.57..0.69 rows=1 width=50) (actual time=0.001..0.002 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.597 3.877 ↑ 1.0 12,594 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 857kB
71. 2.280 2.280 ↑ 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.280 rows=12,594 loops=1)

72. 6.794 155.609 ↓ 1.5 52,536 1

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

73. 46.574 148.815 ↑ 1.0 54,059 1

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

  • Sort Key: cd.candidate_id, cd.created DESC
  • Sort Method: external merge Disk: 3496kB
74. 97.544 102.241 ↑ 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=6.057..102.241 rows=54,062 loops=1)

  • Recheck Cond: ((document_type)::text = 'candidate_photo'::text)
  • Heap Blocks: exact=12845
75. 4.697 4.697 ↑ 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.697..4.697 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. 49.399 100.154 ↑ 1.0 369,418 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3207kB
80. 50.755 50.755 ↑ 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.030..50.755 rows=369,418 loops=1)

81. 0.000 301.860 ↓ 0.0 0 33,540

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

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

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

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

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

  • Hash Cond: (cd_1.document_types_id = odt.document_type_id)
84. 0.046 0.046 ↑ 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.046..0.046 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. 6,775.080 6,775.080 ↑ 1.0 1,989 33,540

Index Scan using candidate_gdpr_compliance_portal_status_idx on candidate_gdpr_compliance cgc (cost=0.28..122.40 rows=2,009 width=6) (actual time=0.003..0.202 rows=1,989 loops=33,540)

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,073.280 1,073.280 ↓ 1.0 352 33,540

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