explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ft68

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 11,425.384 ↑ 1.0 25 1

Limit (cost=14,349,210.71..14,349,210.77 rows=25 width=2,675) (actual time=11,425.342..11,425.384 rows=25 loops=1)

2. 20.418 11,425.366 ↑ 927.4 25 1

Sort (cost=14,349,210.71..14,349,268.67 rows=23,184 width=2,675) (actual time=11,425.341..11,425.366 rows=25 loops=1)

  • Sort Key: invoice.insert_timestamp DESC
  • Sort Method: top-N heapsort Memory: 67kB
3. 66.602 11,404.948 ↑ 6.4 3,639 1

Nested Loop Left Join (cost=32,573.95..14,348,324.63 rows=23,184 width=2,076) (actual time=405.703..11,404.948 rows=3,639 loops=1)

4.          

CTE offer_invoice_item_sum

5. 0.041 0.066 ↑ 1.0 33 1

HashAggregate (cost=1.70..2.03 rows=33 width=24) (actual time=0.052..0.066 rows=33 loops=1)

  • Group Key: offer_invoice_item.offer_id
6. 0.025 0.025 ↑ 1.0 40 1

Seq Scan on offer_invoice_item (cost=0.00..1.40 rows=40 width=24) (actual time=0.003..0.025 rows=40 loops=1)

7.          

CTE user_temp

8. 0.346 0.346 ↑ 1.4 359 1

Seq Scan on user_account (cost=0.00..89.91 rows=495 width=22) (actual time=0.005..0.346 rows=359 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 142
9. 6.891 883.499 ↑ 6.4 3,639 1

Hash Left Join (cost=31,872.69..159,045.09 rows=23,184 width=2,055) (actual time=402.856..883.499 rows=3,639 loops=1)

  • Hash Cond: (candidate.id = cgc.candidate_id)
10. 6.352 863.084 ↑ 6.4 3,639 1

Hash Left Join (cost=31,378.94..158,454.36 rows=23,184 width=2,061) (actual time=389.249..863.084 rows=3,639 loops=1)

  • Hash Cond: (offer.id = offer_approval.offer_id)
11. 6.981 855.086 ↑ 6.4 3,639 1

Hash Left Join (cost=31,249.82..158,214.46 rows=23,184 width=2,035) (actual time=387.593..855.086 rows=3,639 loops=1)

  • Hash Cond: (position_candidate.id = interview.position_candidate_id)
12. 5.838 800.579 ↑ 6.4 3,638 1

Nested Loop Left Join (cost=29,627.01..156,481.85 rows=23,184 width=2,010) (actual time=339.904..800.579 rows=3,638 loops=1)

13. 7.657 776.551 ↑ 3.2 3,638 1

Nested Loop Left Join (cost=29,627.01..140,253.05 rows=11,592 width=2,002) (actual time=339.895..776.551 rows=3,638 loops=1)

14. 6.500 721.600 ↑ 1.6 3,638 1

Hash Left Join (cost=29,625.22..62,818.58 rows=5,796 width=1,994) (actual time=339.857..721.600 rows=3,638 loops=1)

  • Hash Cond: (candidate.user_account_id = user_temp.user_id)
15. 7.968 714.230 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=29,609.13..62,508.06 rows=3,638 width=1,580) (actual time=338.974..714.230 rows=3,638 loops=1)

16. 6.573 684.434 ↑ 1.0 3,638 1

Hash Left Join (cost=29,608.72..60,832.16 rows=3,638 width=1,535) (actual time=338.952..684.434 rows=3,638 loops=1)

  • Hash Cond: (position_candidate.candidate_id = cpv.candidate_id)
17. 6.705 348.612 ↑ 1.0 3,638 1

Hash Left Join (cost=822.18..32,029.39 rows=3,638 width=1,494) (actual time=9.659..348.612 rows=3,638 loops=1)

  • Hash Cond: ((cl.country_code)::text = (ctr.code)::text)
18. 10.637 341.663 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=813.49..31,988.63 rows=3,638 width=1,486) (actual time=9.395..341.663 rows=3,638 loops=1)

19. 9.228 309.198 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=813.06..30,220.91 rows=3,638 width=1,454) (actual time=9.388..309.198 rows=3,638 loops=1)

20. 6.830 278.142 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=812.64..27,781.41 rows=3,638 width=983) (actual time=9.378..278.142 rows=3,638 loops=1)

21. 8.600 249.484 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=812.22..24,054.45 rows=3,638 width=967) (actual time=9.370..249.484 rows=3,638 loops=1)

22. 5.483 229.970 ↑ 1.0 3,638 1

Hash Left Join (cost=811.93..22,574.88 rows=3,638 width=913) (actual time=9.362..229.970 rows=3,638 loops=1)

  • Hash Cond: (offer.id = oiis.offer_id)
23. 7.328 224.367 ↑ 1.0 3,638 1

Hash Left Join (cost=810.86..22,559.95 rows=3,638 width=905) (actual time=9.227..224.367 rows=3,638 loops=1)

  • Hash Cond: (offer.id = offer_personal_info.offer_id)
24. 10.526 209.021 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=199.92..21,899.04 rows=3,638 width=850) (actual time=1.161..209.021 rows=3,638 loops=1)

25. 9.537 180.305 ↑ 1.0 3,638 1

Nested Loop (cost=199.50..19,203.17 rows=3,638 width=826) (actual time=1.152..180.305 rows=3,638 loops=1)

26. 6.944 141.664 ↑ 1.0 3,638 1

Nested Loop (cost=199.08..15,959.22 rows=3,638 width=463) (actual time=1.142..141.664 rows=3,638 loops=1)

27. 6.523 109.254 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=198.79..14,719.41 rows=3,638 width=368) (actual time=1.133..109.254 rows=3,638 loops=1)

28. 4.505 84.541 ↑ 1.0 3,638 1

Hash Left Join (cost=198.50..13,549.71 rows=3,638 width=340) (actual time=1.127..84.541 rows=3,638 loops=1)

  • Hash Cond: (position_candidate.sent_user_id = sent_user_account.id)
29. 5.209 79.542 ↑ 1.0 3,638 1

Hash Left Join (cost=99.95..13,432.36 rows=3,638 width=322) (actual time=0.619..79.542 rows=3,638 loops=1)

  • Hash Cond: (position_candidate.shortlisted_user_id = shortlisted_user_account.id)
30. 5.149 73.762 ↑ 1.0 3,638 1

Nested Loop Left Join (cost=1.41..13,310.17 rows=3,638 width=304) (actual time=0.030..73.762 rows=3,638 loops=1)

31. 10.537 64.975 ↑ 1.0 3,638 1

Merge Join (cost=0.98..5,513.88 rows=3,638 width=274) (actual time=0.028..64.975 rows=3,638 loops=1)

  • Merge Cond: (invoice.offer_id = offer.id)
32. 7.407 40.433 ↑ 1.0 3,638 1

Nested Loop (cost=0.70..5,059.27 rows=3,638 width=182) (actual time=0.018..40.433 rows=3,638 loops=1)

33. 7.560 7.560 ↑ 1.0 3,638 1

Index Scan using invoice_offer_id__unq on invoice (cost=0.28..167.56 rows=3,638 width=85) (actual time=0.007..7.560 rows=3,638 loops=1)

  • Filter: (valid = 1)
  • Rows Removed by Filter: 41
34. 25.466 25.466 ↑ 1.0 1 3,638

Index Scan using position_candidate_pkey on position_candidate (cost=0.42..1.33 rows=1 width=101) (actual time=0.006..0.007 rows=1 loops=3,638)

  • Index Cond: (id = invoice.position_candidate_id)
35. 14.005 14.005 ↑ 1.0 5,782 1

Index Scan using offer_pkey on offer (cost=0.28..395.25 rows=5,783 width=96) (actual time=0.005..14.005 rows=5,782 loops=1)

36. 3.638 3.638 ↓ 0.0 0 3,638

Index Scan using candidate_document_pkey on candidate_document (cost=0.42..2.13 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=3,638)

  • Index Cond: (id = invoice.document_id)
37. 0.251 0.571 ↑ 1.4 501 1

Hash (cost=89.91..89.91 rows=691 width=22) (actual time=0.571..0.571 rows=501 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
38. 0.320 0.320 ↑ 1.4 501 1

Seq Scan on user_account shortlisted_user_account (cost=0.00..89.91 rows=691 width=22) (actual time=0.003..0.320 rows=501 loops=1)

39. 0.235 0.494 ↑ 1.4 501 1

Hash (cost=89.91..89.91 rows=691 width=22) (actual time=0.494..0.494 rows=501 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
40. 0.259 0.259 ↑ 1.4 501 1

Seq Scan on user_account sent_user_account (cost=0.00..89.91 rows=691 width=22) (actual time=0.002..0.259 rows=501 loops=1)

41. 18.190 18.190 ↑ 1.0 1 3,638

Index Scan using compensation_position_id__uidx on compensation (cost=0.29..0.31 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=3,638)

  • Index Cond: (position_candidate.position_description_id = position_id)
42. 25.466 25.466 ↑ 1.0 1 3,638

Index Scan using position_description_pkey on position_description (cost=0.29..0.33 rows=1 width=103) (actual time=0.006..0.007 rows=1 loops=3,638)

  • Index Cond: (id = position_candidate.position_description_id)
43. 29.104 29.104 ↑ 1.0 1 3,638

Index Scan using candidate_pkey on candidate (cost=0.42..0.88 rows=1 width=363) (actual time=0.007..0.008 rows=1 loops=3,638)

  • Index Cond: (id = position_candidate.candidate_id)
44. 18.190 18.190 ↑ 1.0 1 3,638

Index Scan using company__pkey on company (cost=0.42..0.73 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=3,638)

  • Index Cond: (id = position_description.company_id)
45. 3.082 8.018 ↓ 1.0 5,781 1

Hash (cost=538.75..538.75 rows=5,775 width=59) (actual time=8.018..8.018 rows=5,781 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 461kB
46. 4.936 4.936 ↓ 1.0 5,781 1

Seq Scan on offer_personal_info (cost=0.00..538.75 rows=5,775 width=59) (actual time=0.004..4.936 rows=5,781 loops=1)

47. 0.018 0.120 ↑ 1.0 33 1

Hash (cost=0.66..0.66 rows=33 width=16) (actual time=0.120..0.120 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
48. 0.102 0.102 ↑ 1.0 33 1

CTE Scan on offer_invoice_item_sum oiis (cost=0.00..0.66 rows=33 width=16) (actual time=0.058..0.102 rows=33 loops=1)

49. 10.914 10.914 ↑ 1.0 1 3,638

Index Scan using company_location__pkey on company_location (cost=0.29..0.40 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=3,638)

  • Index Cond: (id = position_description.company_location_id)
50. 21.828 21.828 ↑ 1.0 1 3,638

Index Scan using contact__pkey on contact (cost=0.42..1.01 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=3,638)

  • Index Cond: (id = position_description.contact_id)
51. 21.828 21.828 ↑ 1.0 1 3,638

Index Scan using candidate_extension_candidate_id__pkey on candidate_extension ce (cost=0.42..0.66 rows=1 width=479) (actual time=0.005..0.006 rows=1 loops=3,638)

  • Index Cond: (candidate_id = candidate.id)
52. 21.828 21.828 ↑ 1.0 1 3,638

Index Scan using common_location_pkey on common_location cl (cost=0.42..0.48 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=3,638)

  • Index Cond: (candidate.current_location_id = id)
53. 0.128 0.244 ↑ 1.0 253 1

Hash (cost=5.53..5.53 rows=253 width=13) (actual time=0.244..0.244 rows=253 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
54. 0.116 0.116 ↑ 1.0 253 1

Seq Scan on country ctr (cost=0.00..5.53 rows=253 width=13) (actual time=0.003..0.116 rows=253 loops=1)

55. 38.651 329.249 ↓ 14.3 75,936 1

Hash (cost=28,720.21..28,720.21 rows=5,307 width=45) (actual time=329.249..329.249 rows=75,936 loops=1)

  • Buckets: 131072 (originally 8192) Batches: 1 (originally 1) Memory Usage: 6956kB
56. 55.589 290.598 ↓ 14.3 75,936 1

Subquery Scan on cpv (cost=28,280.16..28,720.21 rows=5,307 width=45) (actual time=141.973..290.598 rows=75,936 loops=1)

57. 58.382 235.009 ↓ 14.3 75,936 1

Unique (cost=28,280.16..28,667.14 rows=5,307 width=53) (actual time=141.971..235.009 rows=75,936 loops=1)

58. 94.940 176.627 ↓ 1.0 77,459 1

Sort (cost=28,280.16..28,473.65 rows=77,395 width=53) (actual time=141.969..176.627 rows=77,459 loops=1)

  • Sort Key: cd.candidate_id, cd.created DESC
  • Sort Method: quicksort Memory: 13764kB
59. 81.687 81.687 ↓ 1.0 77,459 1

Index Scan using candidate_document_document_type_idx on candidate_document cd (cost=0.42..21,995.71 rows=77,395 width=53) (actual time=0.014..81.687 rows=77,459 loops=1)

  • Index Cond: ((document_type)::text = 'candidate_photo'::text)
60. 21.828 21.828 ↑ 1.0 1 3,638

Index Scan using position_extension__pkey on position_extension (cost=0.41..0.45 rows=1 width=53) (actual time=0.005..0.006 rows=1 loops=3,638)

  • Index Cond: (position_id = position_candidate.position_description_id)
61. 0.172 0.870 ↑ 1.4 359 1

Hash (cost=9.90..9.90 rows=495 width=422) (actual time=0.870..0.870 rows=359 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
62. 0.698 0.698 ↑ 1.4 359 1

CTE Scan on user_temp (cost=0.00..9.90 rows=495 width=422) (actual time=0.009..0.698 rows=359 loops=1)

63. 3.638 47.294 ↓ 0.0 0 3,638

GroupAggregate (cost=1.79..13.32 rows=2 width=8) (actual time=0.013..0.013 rows=0 loops=3,638)

  • Group Key: cd_1.candidate_id
64. 7.276 43.656 ↓ 0.0 0 3,638

Result (cost=1.79..13.28 rows=3 width=8) (actual time=0.012..0.012 rows=0 loops=3,638)

  • One-Time Filter: (position_candidate.status >= 102)
65. 14.522 36.380 ↓ 0.0 0 3,638

Hash Join (cost=1.79..13.28 rows=3 width=8) (actual time=0.010..0.010 rows=0 loops=3,638)

  • Hash Cond: (cd_1.document_types_id = odt.document_type_id)
66. 0.030 0.030 ↑ 2.5 6 2

Index Scan using candidate_document_candidate_id_idx on candidate_document cd_1 (cost=0.42..11.80 rows=15 width=8) (actual time=0.009..0.015 rows=6 loops=2)

  • Index Cond: (position_candidate.candidate_id = candidate_id)
67. 7.276 21.828 ↓ 0.0 0 3,638

Hash (cost=1.32..1.32 rows=3 width=4) (actual time=0.006..0.006 rows=0 loops=3,638)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
68. 14.552 14.552 ↓ 0.0 0 3,638

Seq Scan on onboarding_document_type odt (cost=0.00..1.32 rows=3 width=4) (actual time=0.004..0.004 rows=0 loops=3,638)

  • Filter: (position_candidate.position_description_id = job_id)
  • Rows Removed by Filter: 26
69. 7.276 18.190 ↓ 0.0 0 3,638

GroupAggregate (cost=0.00..1.36 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=3,638)

  • Group Key: onboarding_document_type.job_id
70. 10.914 10.914 ↓ 0.0 0 3,638

Seq Scan on onboarding_document_type (cost=0.00..1.32 rows=3 width=8) (actual time=0.003..0.003 rows=0 loops=3,638)

  • Filter: (job_id = position_candidate.position_description_id)
  • Rows Removed by Filter: 26
71. 8.463 47.526 ↓ 1.0 16,798 1

Hash (cost=1,416.06..1,416.06 rows=16,540 width=29) (actual time=47.526..47.526 rows=16,798 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1207kB
72. 14.178 39.063 ↓ 1.0 16,798 1

Hash Right Join (cost=664.15..1,416.06 rows=16,540 width=29) (actual time=16.009..39.063 rows=16,798 loops=1)

  • Hash Cond: (ih.interview_id = interview.id)
73. 9.046 9.046 ↑ 1.0 13,942 1

Seq Scan on interview_history ih (cost=0.00..558.75 rows=14,048 width=29) (actual time=0.008..9.046 rows=13,942 loops=1)

  • Filter: (interview_no = 1)
  • Rows Removed by Filter: 6110
74. 7.825 15.839 ↓ 1.0 16,761 1

Hash (cost=457.40..457.40 rows=16,540 width=8) (actual time=15.839..15.839 rows=16,761 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 911kB
75. 8.014 8.014 ↓ 1.0 16,761 1

Seq Scan on interview (cost=0.00..457.40 rows=16,540 width=8) (actual time=0.006..8.014 rows=16,761 loops=1)

76. 0.310 1.646 ↓ 1.0 599 1

Hash (cost=121.68..121.68 rows=595 width=30) (actual time=1.646..1.646 rows=599 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
77. 0.554 1.336 ↓ 1.0 599 1

Hash Left Join (cost=98.55..121.68 rows=595 width=30) (actual time=0.533..1.336 rows=599 loops=1)

  • Hash Cond: (offer_approval.user_account_id = approver.id)
78. 0.262 0.262 ↓ 1.0 599 1

Seq Scan on offer_approval (cost=0.00..14.95 rows=595 width=12) (actual time=0.006..0.262 rows=599 loops=1)

79. 0.258 0.520 ↑ 1.4 501 1

Hash (cost=89.91..89.91 rows=691 width=22) (actual time=0.520..0.520 rows=501 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
80. 0.262 0.262 ↑ 1.4 501 1

Seq Scan on user_account approver (cost=0.00..89.91 rows=691 width=22) (actual time=0.002..0.262 rows=501 loops=1)

81. 6.417 13.524 ↑ 1.0 13,767 1

Hash (cost=321.67..321.67 rows=13,767 width=6) (actual time=13.524..13.524 rows=13,767 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 629kB
82. 7.107 7.107 ↑ 1.0 13,767 1

Seq Scan on candidate_gdpr_compliance cgc (cost=0.00..321.67 rows=13,767 width=6) (actual time=0.004..7.107 rows=13,767 loops=1)

83. 7.278 10,443.930 ↓ 0.0 0 3,639

Limit (cost=609.31..609.32 rows=1 width=25) (actual time=2.870..2.870 rows=0 loops=3,639)

84. 14.556 10,436.652 ↓ 0.0 0 3,639

Sort (cost=609.31..609.32 rows=1 width=25) (actual time=2.868..2.868 rows=0 loops=3,639)

  • Sort Key: ih_1.interview_no DESC
  • Sort Method: quicksort Memory: 25kB
85. 10,422.096 10,422.096 ↑ 1.0 1 3,639

Seq Scan on interview_history ih_1 (cost=0.00..609.30 rows=1 width=25) (actual time=2.288..2.864 rows=1 loops=3,639)

  • Filter: ((interview_no > 1) AND (interview_id = interview.id))
  • Rows Removed by Filter: 20051
86.          

SubPlan (for Nested Loop Left Join)

87. 10.917 10.917 ↑ 1.0 1 3,639

Index Scan using candidate_source_pkey on candidate_source (cost=0.14..2.36 rows=1 width=516) (actual time=0.003..0.003 rows=1 loops=3,639)

  • Index Cond: (id = candidate.candidate_source_id)
Planning time : 15.429 ms