explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i4LS

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 28,920.559 ↓ 24.0 24 1

Limit (cost=60,075.54..60,075.88 rows=1 width=2,611) (actual time=28,920.343..28,920.559 rows=24 loops=1)

2. 0.293 28,920.525 ↓ 24.0 24 1

Result (cost=60,075.54..60,075.88 rows=1 width=2,611) (actual time=28,920.342..28,920.525 rows=24 loops=1)

3. 78.147 28,920.232 ↓ 24.0 24 1

Sort (cost=60,075.54..60,075.55 rows=1 width=2,579) (actual time=28,920.227..28,920.232 rows=24 loops=1)

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

Nested Loop Left Join (cost=56,229.59..60,075.53 rows=1 width=2,579) (actual time=6,946.516..28,842.085 rows=33,540 loops=1)

  • Join Filter: (approver.id = offer_approval.user_account_id)
  • Rows Removed by Join Filter: 11805238
5. 227.059 27,119.005 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,988.57 rows=1 width=2,580) (actual time=6,946.150..27,119.005 rows=33,540 loops=1)

  • Join Filter: (offer.id = offer_approval.offer_id)
  • Rows Removed by Join Filter: 637255
6. 9,244.304 26,858.406 ↓ 33,540.0 33,540 1

Nested Loop Left Join (cost=56,229.59..59,987.14 rows=1 width=2,572) (actual time=6,946.117..26,858.406 rows=33,540 loops=1)

  • Join Filter: (c.id = cgc.candidate_id)
  • Rows Removed by Join Filter: 229747604
7. 26.749 8,524.762 ↓ 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,945.061..8,524.762 rows=33,540 loops=1)

8. 23.173 8,363.853 ↓ 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,945.053..8,363.853 rows=33,540 loops=1)

9. 463.201 8,072.360 ↓ 33,540.0 33,540 1

Hash Left Join (cost=56,207.60..59,650.33 rows=1 width=2,562) (actual time=6,944.983..8,072.360 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. 46.583 7,509.697 ↓ 199.5 135,095 1

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

11. 123.716 7,328.019 ↓ 199.5 135,095 1

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

12. 4.734 7,204.303 ↓ 199.5 135,094 1

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

13. 127.470 7,064.475 ↓ 199.5 135,094 1

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

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

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

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

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

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

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

17. 154.920 5,913.854 ↓ 199.5 135,083 1

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

18. 58.634 5,623.851 ↓ 199.5 135,083 1

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

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

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

20. 89.262 5,464.858 ↓ 199.5 135,083 1

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

21. 88.040 5,375.596 ↓ 199.5 135,083 1

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

22. 62.310 5,287.556 ↓ 199.5 135,083 1

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

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

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

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

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

25. 12.795 4,897.210 ↓ 199.5 135,083 1

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

26. 85.675 4,614.249 ↓ 199.5 135,083 1

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

27. 64.530 4,393.491 ↓ 199.5 135,083 1

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

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

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

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

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

30. 132.458 4,017.754 ↓ 199.5 135,083 1

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

31. 49.744 3,209.881 ↓ 199.5 135,083 1

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

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

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

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

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

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

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

35. 67.233 844.440 ↓ 199.9 136,165 1

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

36. 3.554 364.631 ↓ 199.9 25,786 1

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

37. 25.423 206.361 ↓ 199.9 25,786 1

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

38. 52.008 52.008 ↓ 199.9 25,786 1

Seq Scan on position_description pd (cost=0.00..3,217.32 rows=129 width=102) (actual time=0.035..52.008 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.045 0.150 ↓ 1.0 352 1

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

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

46. 0.040 0.092 ↓ 1.0 352 1

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

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

Seq Scan on user_account sent_user_account (cost=0.00..82.51 rows=351 width=21) (actual time=0.005..0.052 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.043 0.087 ↓ 1.0 352 1

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

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

52. 0.006 0.023 ↓ 1.0 38 1

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

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

Seq Scan on candidate_source (cost=0.00..1.37 rows=37 width=520) (actual time=0.014..0.017 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.565 20.642 ↑ 1.0 42,546 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2552kB
58. 13.077 13.077 ↑ 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..13.077 rows=42,546 loops=1)

59. 0.013 0.030 ↑ 1.0 52 1

Hash (cost=1.52..1.52 rows=52 width=22) (actual time=0.030..0.030 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.041 0.107 ↑ 1.0 438 1

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

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

Seq Scan on offer_fee_model (cost=0.00..9.38 rows=438 width=16) (actual time=0.011..0.066 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.589 3.793 ↑ 1.0 12,594 1

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

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

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

72. 5.986 146.890 ↓ 1.5 52,536 1

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

73. 42.060 140.904 ↑ 1.0 54,059 1

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

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

  • Recheck Cond: ((document_type)::text = 'candidate_photo'::text)
  • Heap Blocks: exact=12845
75. 4.622 4.622 ↑ 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.622..4.622 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. 53.054 99.462 ↑ 1.0 369,418 1

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

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

81. 33.540 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. 0.000 234.780 ↓ 0.0 0 33,540

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

  • One-Time Filter: (pc.status >= 102)
83. 67.034 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.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. 9,089.340 9,089.340 ↑ 1.0 6,850 33,540

Seq Scan on candidate_gdpr_compliance cgc (cost=0.00..164.46 rows=7,146 width=6) (actual time=0.002..0.271 rows=6,850 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. 972.660 972.660 ↓ 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.029 rows=352 loops=33,540)