explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rEwu

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 211.203 ↑ 1.0 20 1

Limit (cost=20,928.41..20,928.46 rows=20 width=3,045) (actual time=211.183..211.203 rows=20 loops=1)

2. 3.192 211.180 ↑ 1.2 45 1

Sort (cost=20,928.35..20,928.48 rows=52 width=3,045) (actual time=211.161..211.180 rows=45 loops=1)

  • Sort Key: (CASE WHEN (offer.id IS NOT NULL) THEN offer.projected_profit WHEN (offer.id IS NULL) THEN compensation.projected_profit ELSE '0'::double precision END)
  • Sort Method: top-N heapsort Memory: 76kB
3. 10.874 207.988 ↓ 16.7 866 1

Nested Loop Left Join (cost=8,766.43..20,926.35 rows=52 width=2,444) (actual time=64.620..207.988 rows=866 loops=1)

4.          

CTE offer_invoice_item_sum

5. 0.014 0.017 ↑ 21.7 6 1

HashAggregate (cost=12.28..13.58 rows=130 width=24) (actual time=0.016..0.017 rows=6 loops=1)

  • Group Key: offer_invoice_item.offer_id
6. 0.003 0.003 ↑ 21.7 6 1

Seq Scan on offer_invoice_item (cost=0.00..11.30 rows=130 width=24) (actual time=0.002..0.003 rows=6 loops=1)

7.          

CTE user_temp

8. 0.376 0.376 ↑ 1.0 122 1

Seq Scan on user_account (cost=0.00..440.02 rows=122 width=26) (actual time=0.006..0.376 rows=122 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 80
9. 1.450 191.918 ↓ 16.7 866 1

Nested Loop Left Join (cost=8,312.56..20,260.20 rows=52 width=2,420) (actual time=64.549..191.918 rows=866 loops=1)

10. 1.428 189.602 ↓ 16.7 866 1

Nested Loop Left Join (cost=8,312.28..20,242.85 rows=52 width=2,396) (actual time=64.546..189.602 rows=866 loops=1)

11. 2.058 186.442 ↓ 16.7 866 1

Nested Loop Left Join (cost=8,312.01..20,227.12 rows=52 width=2,392) (actual time=64.539..186.442 rows=866 loops=1)

12. 1.239 184.384 ↓ 16.7 866 1

Hash Left Join (cost=8,311.74..20,159.20 rows=52 width=2,370) (actual time=64.535..184.384 rows=866 loops=1)

  • Hash Cond: (offer.id = offer_approval.offer_id)
13. 1.556 183.140 ↓ 16.7 866 1

Nested Loop Left Join (cost=8,298.81..20,146.05 rows=52 width=2,362) (actual time=64.524..183.140 rows=866 loops=1)

14. 1.256 178.986 ↓ 16.7 866 1

Nested Loop Left Join (cost=8,298.53..20,130.04 rows=52 width=2,368) (actual time=64.516..178.986 rows=866 loops=1)

15. 1.922 175.998 ↓ 33.3 866 1

Nested Loop Left Join (cost=8,298.53..19,568.83 rows=26 width=2,360) (actual time=64.510..175.998 rows=866 loops=1)

16. 2.094 168.014 ↓ 66.6 866 1

Nested Loop Left Join (cost=8,276.54..18,365.83 rows=13 width=2,352) (actual time=64.486..168.014 rows=866 loops=1)

17. 1.307 162.456 ↓ 66.6 866 1

Hash Left Join (cost=8,276.26..18,361.49 rows=13 width=2,313) (actual time=64.472..162.456 rows=866 loops=1)

  • Hash Cond: (position_candidate.candidate_id = cpv.candidate_id)
18. 1.280 114.810 ↓ 66.6 866 1

Hash Left Join (cost=1,423.07..11,508.25 rows=13 width=2,272) (actual time=18.118..114.810 rows=866 loops=1)

  • Hash Cond: (candidate.user_account_id = user_temp.user_id)
19. 2.038 112.973 ↓ 66.6 866 1

Nested Loop Left Join (cost=1,419.11..11,504.10 rows=13 width=1,858) (actual time=17.547..112.973 rows=866 loops=1)

20. 1.510 108.337 ↓ 66.6 866 1

Nested Loop Left Join (cost=1,418.96..11,501.86 rows=13 width=1,850) (actual time=17.536..108.337 rows=866 loops=1)

21. 1.933 102.497 ↓ 66.6 866 1

Nested Loop Left Join (cost=1,418.55..11,495.39 rows=13 width=1,824) (actual time=17.526..102.497 rows=866 loops=1)

22. 1.867 96.234 ↓ 66.6 866 1

Nested Loop Left Join (cost=1,418.13..11,486.44 rows=13 width=1,375) (actual time=17.515..96.234 rows=866 loops=1)

23. 2.149 90.903 ↓ 66.6 866 1

Nested Loop Left Join (cost=1,417.71..11,470.18 rows=13 width=1,359) (actual time=17.506..90.903 rows=866 loops=1)

24. 1.108 87.022 ↓ 66.6 866 1

Hash Left Join (cost=1,417.43..11,465.75 rows=13 width=1,325) (actual time=17.497..87.022 rows=866 loops=1)

  • Hash Cond: (offer.id = oiis.offer_id)
25. 2.144 85.879 ↓ 66.6 866 1

Nested Loop Left Join (cost=1,413.20..11,461.47 rows=13 width=1,317) (actual time=17.445..85.879 rows=866 loops=1)

26. 3.684 81.137 ↓ 66.6 866 1

Nested Loop (cost=1,412.92..11,456.01 rows=13 width=1,302) (actual time=17.438..81.137 rows=866 loops=1)

27. 2.149 73.989 ↓ 66.6 866 1

Hash Join (cost=1,412.50..11,444.03 rows=13 width=1,001) (actual time=17.425..73.989 rows=866 loops=1)

  • Hash Cond: (offer.id = offer_personal_info.offer_id)
28. 5.682 69.111 ↓ 1.1 2,929 1

Nested Loop Left Join (cost=990.28..11,011.79 rows=2,639 width=952) (actual time=14.680..69.111 rows=2,929 loops=1)

29. 3.911 63.429 ↓ 1.1 2,929 1

Hash Left Join (cost=989.85..5,265.99 rows=2,639 width=938) (actual time=14.676..63.429 rows=2,929 loops=1)

  • Hash Cond: (position_candidate.position_description_id = compensation.position_id)
30. 3.062 48.994 ↓ 1.1 2,929 1

Hash Left Join (cost=497.88..4,737.72 rows=2,639 width=910) (actual time=4.060..48.994 rows=2,929 loops=1)

  • Hash Cond: (position_candidate.sent_user_id = sent_user_account.id)
31. 3.000 45.664 ↓ 1.1 2,929 1

Hash Left Join (cost=304.75..4,532.15 rows=2,639 width=888) (actual time=3.777..45.664 rows=2,929 loops=1)

  • Hash Cond: (position_candidate.shortlisted_user_id = shortlisted_user_account.id)
32. 4.712 42.284 ↓ 1.1 2,929 1

Nested Loop (cost=111.62..4,322.29 rows=2,639 width=866) (actual time=3.381..42.284 rows=2,929 loops=1)

33. 3.935 25.856 ↓ 1.1 2,929 1

Nested Loop (cost=111.33..3,458.72 rows=2,641 width=279) (actual time=3.370..25.856 rows=2,929 loops=1)

34. 3.561 10.205 ↓ 1.1 2,929 1

Hash Join (cost=111.04..564.30 rows=2,641 width=182) (actual time=3.356..10.205 rows=2,929 loops=1)

  • Hash Cond: (offer.id = invoice.offer_id)
35. 3.322 3.322 ↓ 1.1 2,974 1

Seq Scan on offer (cost=0.00..416.80 rows=2,680 width=96) (actual time=0.005..3.322 rows=2,974 loops=1)

36. 1.584 3.322 ↓ 1.1 2,929 1

Hash (cost=78.03..78.03 rows=2,641 width=86) (actual time=3.322..3.322 rows=2,929 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
37. 1.738 1.738 ↓ 1.1 2,929 1

Seq Scan on invoice (cost=0.00..78.03 rows=2,641 width=86) (actual time=0.010..1.738 rows=2,929 loops=1)

  • Filter: (valid = 1)
  • Rows Removed by Filter: 4
38. 11.716 11.716 ↑ 1.0 1 2,929

Index Scan using position_candidate_pkey on position_candidate (cost=0.29..1.09 rows=1 width=101) (actual time=0.003..0.004 rows=1 loops=2,929)

  • Index Cond: (id = invoice.position_candidate_id)
39. 11.716 11.716 ↑ 1.0 1 2,929

Index Scan using position_description_pkey on position_description (cost=0.29..0.32 rows=1 width=595) (actual time=0.003..0.004 rows=1 loops=2,929)

  • Index Cond: (id = position_candidate.position_description_id)
  • Filter: (position_category = 1)
40. 0.105 0.380 ↑ 1.0 202 1

Hash (cost=190.61..190.61 rows=202 width=26) (actual time=0.380..0.380 rows=202 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
41. 0.275 0.275 ↑ 1.0 202 1

Index Scan using client_account_pkey on user_account shortlisted_user_account (cost=0.27..190.61 rows=202 width=26) (actual time=0.008..0.275 rows=202 loops=1)

42. 0.108 0.268 ↑ 1.0 202 1

Hash (cost=190.61..190.61 rows=202 width=26) (actual time=0.268..0.268 rows=202 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
43. 0.160 0.160 ↑ 1.0 202 1

Index Scan using client_account_pkey on user_account sent_user_account (cost=0.27..190.61 rows=202 width=26) (actual time=0.004..0.160 rows=202 loops=1)

44. 4.752 10.524 ↑ 1.1 9,475 1

Hash (cost=366.99..366.99 rows=9,999 width=32) (actual time=10.524..10.524 rows=9,475 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 623kB
45. 5.772 5.772 ↑ 1.1 9,475 1

Seq Scan on compensation (cost=0.00..366.99 rows=9,999 width=32) (actual time=0.005..5.772 rows=9,475 loops=1)

46. 0.000 0.000 ↓ 0.0 0 2,929

Index Scan using candidate_document_pkey on candidate_document (cost=0.42..2.17 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=2,929)

  • Index Cond: (id = invoice.document_id)
47. 0.515 2.729 ↓ 68.1 885 1

Hash (cost=422.06..422.06 rows=13 width=57) (actual time=2.729..2.729 rows=885 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 78kB
48. 2.214 2.214 ↓ 68.1 885 1

Seq Scan on offer_personal_info (cost=0.00..422.06 rows=13 width=57) (actual time=0.011..2.214 rows=885 loops=1)

  • Filter: (((placed_date)::date >= '2018-01-05'::date) AND ((placed_date)::date <= '2019-06-30'::date))
  • Rows Removed by Filter: 2062
49. 3.464 3.464 ↑ 1.0 1 866

Index Scan using candidate_pkey on candidate (cost=0.42..0.91 rows=1 width=301) (actual time=0.004..0.004 rows=1 loops=866)

  • Index Cond: (id = position_candidate.candidate_id)
50. 2.598 2.598 ↑ 1.0 1 866

Index Scan using company__pkey on company (cost=0.29..0.41 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=866)

  • Index Cond: (id = position_description.company_id)
51. 0.004 0.035 ↑ 21.7 6 1

Hash (cost=2.60..2.60 rows=130 width=16) (actual time=0.035..0.035 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.031 0.031 ↑ 21.7 6 1

CTE Scan on offer_invoice_item_sum oiis (cost=0.00..2.60 rows=130 width=16) (actual time=0.020..0.031 rows=6 loops=1)

53. 1.732 1.732 ↑ 1.0 1 866

Index Scan using company_location__pkey on company_location (cost=0.29..0.33 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=866)

  • Index Cond: (id = position_description.company_location_id)
54. 3.464 3.464 ↑ 1.0 1 866

Index Scan using contact__pkey on contact (cost=0.41..1.24 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=866)

  • Index Cond: (id = position_description.contact_id)
55. 4.330 4.330 ↑ 1.0 1 866

Index Scan using candidate_extension_candidate_id__pkey on candidate_extension ce (cost=0.42..0.68 rows=1 width=457) (actual time=0.004..0.005 rows=1 loops=866)

  • Index Cond: (candidate_id = candidate.id)
56. 4.330 4.330 ↑ 1.0 1 866

Index Scan using common_location_pkey on common_location cl (cost=0.42..0.49 rows=1 width=38) (actual time=0.004..0.005 rows=1 loops=866)

  • Index Cond: (candidate.current_location_id = id)
57. 2.598 2.598 ↑ 1.0 1 866

Index Scan using country_pkey on country ctr (cost=0.14..0.16 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=866)

  • Index Cond: ((code)::text = (cl.country_code)::text)
58. 0.063 0.557 ↑ 1.0 122 1

Hash (cost=2.44..2.44 rows=122 width=422) (actual time=0.557..0.557 rows=122 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
59. 0.494 0.494 ↑ 1.0 122 1

CTE Scan on user_temp (cost=0.00..2.44 rows=122 width=422) (actual time=0.007..0.494 rows=122 loops=1)

60. 5.527 46.339 ↓ 124.1 11,295 1

Hash (cost=6,852.04..6,852.04 rows=91 width=45) (actual time=46.339..46.339 rows=11,295 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1011kB
61. 8.249 40.812 ↓ 124.1 11,295 1

Subquery Scan on cpv (cost=6,796.26..6,852.04 rows=91 width=45) (actual time=19.304..40.812 rows=11,295 loops=1)

62. 8.608 32.563 ↓ 124.1 11,295 1

Unique (cost=6,796.26..6,851.13 rows=91 width=53) (actual time=19.304..32.563 rows=11,295 loops=1)

63. 13.057 23.955 ↓ 1.0 11,295 1

Sort (cost=6,796.26..6,823.70 rows=10,975 width=53) (actual time=19.302..23.955 rows=11,295 loops=1)

  • Sort Key: cd.candidate_id, cd.created DESC
  • Sort Method: quicksort Memory: 1973kB
64. 10.898 10.898 ↓ 1.0 11,295 1

Index Scan using candidate_document_document_type_idx on candidate_document cd (cost=0.42..6,059.73 rows=10,975 width=53) (actual time=0.016..10.898 rows=11,295 loops=1)

  • Index Cond: ((document_type)::text = 'candidate_photo'::text)
65. 3.464 3.464 ↑ 1.0 1 866

Index Scan using position_extension__pkey on position_extension (cost=0.29..0.32 rows=1 width=47) (actual time=0.003..0.004 rows=1 loops=866)

  • Index Cond: (position_id = position_candidate.position_description_id)
66. 0.866 6.062 ↓ 0.0 0 866

GroupAggregate (cost=21.99..92.50 rows=2 width=8) (actual time=0.007..0.007 rows=0 loops=866)

  • Group Key: cd_1.candidate_id
67. 0.866 5.196 ↓ 0.0 0 866

Result (cost=21.99..92.46 rows=3 width=8) (actual time=0.006..0.006 rows=0 loops=866)

  • One-Time Filter: (position_candidate.status >= 102)
68. 2.591 4.330 ↓ 0.0 0 866

Hash Join (cost=21.99..92.46 rows=3 width=8) (actual time=0.005..0.005 rows=0 loops=866)

  • Hash Cond: (cd_1.document_types_id = odt.document_type_id)
69. 0.007 0.007 ↑ 122.0 1 1

Index Scan using candidate_document_candidate_id_idx on candidate_document cd_1 (cost=0.42..70.41 rows=122 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (position_candidate.candidate_id = candidate_id)
70. 1.732 1.732 ↓ 0.0 0 866

Hash (cost=21.50..21.50 rows=5 width=4) (actual time=0.002..0.002 rows=0 loops=866)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
71. 0.000 0.000 ↓ 0.0 0 866

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=866)

  • Filter: (position_candidate.position_description_id = job_id)
72. 1.732 1.732 ↓ 0.0 0 866

GroupAggregate (cost=0.00..21.54 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=866)

  • Group Key: onboarding_document_type.job_id
73. 0.000 0.000 ↓ 0.0 0 866

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

  • Filter: (job_id = position_candidate.position_description_id)
74. 2.598 2.598 ↓ 0.0 0 866

Index Scan using gdpr_compliance_pkey on candidate_gdpr_compliance cgc (cost=0.28..0.30 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=866)

  • Index Cond: (candidate.id = candidate_id)
75. 0.001 0.005 ↑ 130.0 1 1

Hash (cost=11.30..11.30 rows=130 width=12) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 0.004 0.004 ↑ 130.0 1 1

Seq Scan on offer_approval (cost=0.00..11.30 rows=130 width=12) (actual time=0.003..0.004 rows=1 loops=1)

77. 0.000 0.000 ↓ 0.0 0 866

Index Scan using client_account_pkey on user_account approver (cost=0.27..1.30 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=866)

  • Index Cond: (id = offer_approval.user_account_id)
78. 1.732 1.732 ↓ 0.0 0 866

Index Scan using interview_position_candidate_id on interview (cost=0.28..0.29 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=866)

  • Index Cond: (position_candidate_id = position_candidate.id)
79. 0.866 0.866 ↓ 0.0 0 866

Index Scan using interview_history_interview_id_no_ukey on interview_history ih (cost=0.28..0.32 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=866)

  • Index Cond: ((interview_id = interview.id) AND (interview_no = 1))
80. 0.866 1.732 ↓ 0.0 0 866

Limit (cost=0.28..2.50 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=866)

81. 0.866 0.866 ↓ 0.0 0 866

Index Scan Backward using interview_history_interview_id_no_ukey on interview_history ih_1 (cost=0.28..2.50 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=866)

  • Index Cond: ((interview_id = interview.id) AND (interview_no > 1))
82.          

SubPlan (forNested Loop Left Join)

83. 3.464 3.464 ↑ 1.0 1 866

Seq Scan on candidate_source (cost=0.00..1.25 rows=1 width=13) (actual time=0.002..0.004 rows=1 loops=866)

  • Filter: (id = candidate.candidate_source_id)
  • Rows Removed by Filter: 20
Planning time : 18.741 ms