explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IZCr : first

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 40,295.076 ↓ 10.0 10 1

Limit (cost=136,520.76..136,520.76 rows=1 width=2,656) (actual time=40,295.069..40,295.076 rows=10 loops=1)

2. 32.165 40,295.069 ↓ 10.0 10 1

Sort (cost=136,520.76..136,520.76 rows=1 width=2,656) (actual time=40,295.067..40,295.069 rows=10 loops=1)

  • Sort Key: this_.createddate DESC NULLS LAST, this_.jobpostinghiringid DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 41kB
3. 387.135 40,262.904 ↓ 8,173.0 8,173 1

WindowAgg (cost=108,724.38..136,520.75 rows=1 width=2,656) (actual time=39,571.327..40,262.904 rows=8,173 loops=1)

4. 18.933 39,524.330 ↓ 8,173.0 8,173 1

Nested Loop (cost=108,724.38..136,490.68 rows=1 width=2,656) (actual time=375.863..39,524.330 rows=8,173 loops=1)

5. 20,594.233 39,440.013 ↓ 8,173.0 8,173 1

Nested Loop (cost=108,723.95..136,486.23 rows=1 width=2,643) (actual time=375.845..39,440.013 rows=8,173 loops=1)

  • Join Filter: (placements11_.pipelinestatusid = pipelinest12_.pipelinestatusid)
  • Rows Removed by Join Filter: 67271963
6. 14.296 4,256.975 ↓ 8,173.0 8,173 1

Nested Loop (cost=108,723.95..136,137.43 rows=1 width=2,621) (actual time=372.073..4,256.975 rows=8,173 loops=1)

7. 8.437 4,193.641 ↓ 8,173.0 8,173 1

Nested Loop Left Join (cost=108,723.52..136,135.45 rows=1 width=2,585) (actual time=372.059..4,193.641 rows=8,173 loops=1)

8. 1,237.024 4,177.031 ↓ 8,173.0 8,173 1

Nested Loop (cost=108,723.10..136,134.91 rows=1 width=2,585) (actual time=372.054..4,177.031 rows=8,173 loops=1)

  • Join Filter: (this_.jobpostingid = jobposting9_.jobpostingid)
  • Rows Removed by Join Filter: 3835527
9. 63.527 2,261.707 ↓ 53.0 56,525 1

Nested Loop (cost=108,708.29..133,950.40 rows=1,067 width=2,461) (actual time=369.002..2,261.707 rows=56,525 loops=1)

10. 119.695 2,028.605 ↓ 53.0 56,525 1

Nested Loop (cost=108,708.00..133,584.35 rows=1,067 width=2,433) (actual time=368.991..2,028.605 rows=56,525 loops=1)

  • Join Filter: (this_.employeeid = employeesk3_.employeeid)
11. 198.325 1,682.810 ↓ 53.0 56,525 1

Nested Loop (cost=108,707.57..132,846.60 rows=1,067 width=1,918) (actual time=368.976..1,682.810 rows=56,525 loops=1)

  • Join Filter: (employeejo5_.payratetypeid = payratetyp7_.ratetypeid)
  • Rows Removed by Join Filter: 339150
12. 147.696 1,427.960 ↓ 53.0 56,525 1

Nested Loop (cost=108,707.57..132,733.48 rows=1,067 width=1,761) (actual time=368.971..1,427.960 rows=56,525 loops=1)

  • Join Filter: (employeejo5_.loadedcosttypeid = loadedcost6_.ratetypeid)
  • Rows Removed by Join Filter: 339150
13. 108.213 1,167.214 ↓ 53.0 56,525 1

Nested Loop (cost=108,707.57..132,620.35 rows=1,067 width=1,604) (actual time=368.962..1,167.214 rows=56,525 loops=1)

14. 104.111 832.901 ↓ 53.0 56,525 1

Nested Loop (cost=108,707.14..129,130.24 rows=1,067 width=460) (actual time=368.948..832.901 rows=56,525 loops=1)

15. 88.654 502.690 ↓ 53.0 56,525 1

Hash Join (cost=108,706.86..128,626.84 rows=1,067 width=441) (actual time=368.922..502.690 rows=56,525 loops=1)

  • Hash Cond: (this_.employeeid = e13_.employeeid)
16. 45.770 54.343 ↓ 1.1 56,560 1

Bitmap Heap Scan on jobpostinghiring this_ (cost=1,086.25..20,794.04 rows=53,738 width=88) (actual time=9.157..54.343 rows=56,560 loops=1)

  • Recheck Cond: (companyid = '1943'::numeric)
  • Filter: (hired = '0'::numeric)
  • Rows Removed by Filter: 54
  • Heap Blocks: exact=3990
17. 8.573 8.573 ↓ 1.5 81,739 1

Bitmap Index Scan on jph_companyid_idx (cost=0.00..1,072.82 rows=54,786 width=0) (actual time=8.573..8.573 rows=81,739 loops=1)

  • Index Cond: (companyid = '1943'::numeric)
18. 49.014 359.693 ↓ 1.0 48,884 1

Hash (cost=107,032.63..107,032.63 rows=47,038 width=353) (actual time=359.693..359.693 rows=48,884 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 16961kB
19. 109.768 310.679 ↓ 1.0 48,884 1

Hash Right Join (cost=101,633.64..107,032.63 rows=47,038 width=353) (actual time=249.705..310.679 rows=48,884 loops=1)

  • Hash Cond: (employeesu1_.employeeid = e13_.employeeid)
20. 47.471 47.471 ↑ 1.0 216,239 1

Seq Scan on employeesubcontractordetails employeesu1_ (cost=0.00..4,541.25 rows=217,225 width=12) (actual time=0.005..47.471 rows=216,239 loops=1)

21. 56.884 153.440 ↓ 1.0 48,884 1

Hash (cost=101,045.67..101,045.67 rows=47,038 width=347) (actual time=153.440..153.440 rows=48,884 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 16955kB
22. 85.337 96.556 ↓ 1.0 48,884 1

Bitmap Heap Scan on employee e13_ (cost=1,658.57..101,045.67 rows=47,038 width=347) (actual time=15.140..96.556 rows=48,884 loops=1)

  • Recheck Cond: ((companyid = '1943'::numeric) AND (terminationflag = '0'::numeric))
  • Heap Blocks: exact=23267
23. 11.219 11.219 ↓ 1.1 49,654 1

Bitmap Index Scan on emp_cmpid_terflag_ht_indx (cost=0.00..1,646.81 rows=47,038 width=0) (actual time=11.219..11.219 rows=49,654 loops=1)

  • Index Cond: ((companyid = '1943'::numeric) AND (terminationflag = '0'::numeric))
24. 226.100 226.100 ↑ 1.0 1 56,525

Index Scan using emty_pk on employeetype employeety4_ (cost=0.28..0.46 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=56,525)

  • Index Cond: (employeetypeid = e13_.employeetypeid)
25. 226.100 226.100 ↑ 1.0 1 56,525

Index Scan using empjobpref_emp_indx on employeejobpreference employeejo5_ (cost=0.43..3.26 rows=1 width=1,144) (actual time=0.004..0.004 rows=1 loops=56,525)

  • Index Cond: (employeeid = this_.employeeid)
26. 113.043 113.050 ↑ 1.0 7 56,525

Materialize (cost=0.00..1.10 rows=7 width=162) (actual time=0.000..0.002 rows=7 loops=56,525)

27. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on ratetype loadedcost6_ (cost=0.00..1.07 rows=7 width=162) (actual time=0.004..0.007 rows=7 loops=1)

28. 56.522 56.525 ↑ 1.0 7 56,525

Materialize (cost=0.00..1.10 rows=7 width=162) (actual time=0.000..0.001 rows=7 loops=56,525)

29. 0.003 0.003 ↑ 1.0 7 1

Seq Scan on ratetype payratetyp7_ (cost=0.00..1.07 rows=7 width=162) (actual time=0.001..0.003 rows=7 loops=1)

30. 226.100 226.100 ↑ 1.0 1 56,525

Index Scan using eskills_empid_indx on employeeskills employeesk3_ (cost=0.43..0.68 rows=1 width=527) (actual time=0.004..0.004 rows=1 loops=56,525)

  • Index Cond: (employeeid = employeejo5_.employeeid)
31. 169.575 169.575 ↑ 1.0 1 56,525

Index Scan using pipelinesubstatus_pkey on pipelinesubstatus placements11_ (cost=0.29..0.33 rows=1 width=34) (actual time=0.002..0.003 rows=1 loops=56,525)

  • Index Cond: (pipelinesubstatusid = this_.placementstatus)
32. 675.743 678.300 ↓ 1.8 68 56,525

Materialize (cost=14.81..1,576.41 rows=38 width=124) (actual time=0.000..0.012 rows=68 loops=56,525)

33. 0.087 2.557 ↓ 1.8 68 1

Nested Loop (cost=14.81..1,576.22 rows=38 width=124) (actual time=0.090..2.557 rows=68 loops=1)

34. 0.034 0.034 ↓ 7.0 7 1

Index Scan using jst_companyid_indx on jobstatustype jobstatust10_ (cost=0.29..20.76 rows=1 width=6) (actual time=0.014..0.034 rows=7 loops=1)

  • Index Cond: (companyid = '1943'::numeric)
  • Filter: ((jobstatustypeid = '21114'::numeric) OR (jobstatustypeid = '21113'::numeric) OR (jobstatustypeid = '21112'::numeric) OR (jobstatustypeid = '21110'::numeric) OR (jobstatustypeid = '21109'::numeric) OR (jobstatustypeid = '21290'::numeric) OR (jobstatustypeid = '21339'::numeric))
  • Rows Removed by Filter: 10
35. 1.953 2.436 ↑ 46.5 10 7

Bitmap Heap Scan on jobposting jobposting9_ (cost=14.53..1,550.81 rows=465 width=129) (actual time=0.171..0.348 rows=10 loops=7)

  • Recheck Cond: (jobstatustypeid = jobstatust10_.jobstatustypeid)
  • Heap Blocks: exact=2242
36. 0.483 0.483 ↑ 1.2 390 7

Bitmap Index Scan on jpos_jobstatustypeid_indx (cost=0.00..14.41 rows=465 width=0) (actual time=0.069..0.069 rows=390 loops=7)

  • Index Cond: (jobstatustypeid = jobstatust10_.jobstatustypeid)
37. 8.173 8.173 ↓ 0.0 0 8,173

Index Only Scan using companydetails_pk on companydetails employeesu2_ (cost=0.42..0.53 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=8,173)

  • Index Cond: (companyid = employeesu1_.subcontractorcompanyid)
  • Heap Fetches: 386
38. 49.038 49.038 ↑ 1.0 1 8,173

Index Scan using contact_pk on contact contact8_ (cost=0.42..1.97 rows=1 width=42) (actual time=0.005..0.006 rows=1 loops=8,173)

  • Index Cond: (contactid = jobposting9_.businesscontactid)
39. 14,588.805 14,588.805 ↓ 1.0 8,232 8,173

Seq Scan on pipelinestatus pipelinest12_ (cost=0.00..246.13 rows=8,213 width=28) (actual time=0.002..1.785 rows=8,232 loops=8,173)

40. 65.384 65.384 ↑ 1.0 1 8,173

Index Scan using emp_pk on employee postedby14_ (cost=0.43..4.45 rows=1 width=20) (actual time=0.007..0.008 rows=1 loops=8,173)

  • Index Cond: (employeeid = this_.postedby)
41.          

SubPlan (forWindowAgg)

42. 24.519 65.384 ↑ 1.0 1 8,173

Aggregate (cost=7.44..7.45 rows=1 width=6) (actual time=0.007..0.008 rows=1 loops=8,173)

43. 40.865 40.865 ↓ 0.0 0 8,173

Index Only Scan using resumeview_pkey on resumeview rv (cost=0.42..7.44 rows=1 width=6) (actual time=0.005..0.005 rows=0 loops=8,173)

  • Index Cond: (employeeid = this_.employeeid)
  • Heap Fetches: 2140
44. 8.173 40.865 ↑ 1.0 1 8,173

Limit (cost=0.42..7.44 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=8,173)

45. 32.692 32.692 ↑ 1.0 1 8,173

Index Scan using cpjp_jpoid_indx on clientprojectjobposting cpjp (cost=0.42..7.44 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=8,173)

  • Index Cond: (jobpostingid = this_.jobpostingid)
46. 16.346 245.190 ↑ 1.0 1 8,173

Limit (cost=14.89..14.90 rows=1 width=17) (actual time=0.029..0.030 rows=1 loops=8,173)

47. 32.692 228.844 ↑ 1.0 1 8,173

HashAggregate (cost=14.89..14.90 rows=1 width=17) (actual time=0.028..0.028 rows=1 loops=8,173)

  • Group Key: cd.companyname
48. 30.103 196.152 ↓ 4.0 4 8,173

Nested Loop (cost=0.84..14.89 rows=1 width=17) (actual time=0.009..0.024 rows=4 loops=8,173)

49. 40.865 40.865 ↓ 4.0 4 8,173

Index Scan using cpjp_jpoid_indx on clientprojectjobposting scpjp (cost=0.42..7.44 rows=1 width=6) (actual time=0.003..0.005 rows=4 loops=8,173)

  • Index Cond: (jobpostingid = this_.jobpostingid)
50. 125.184 125.184 ↑ 1.0 1 31,296

Index Scan using companydetails_pk on companydetails cd (cost=0.42..7.44 rows=1 width=23) (actual time=0.003..0.004 rows=1 loops=31,296)

  • Index Cond: (companyid = scpjp.accountid)
Planning time : 21.289 ms
Execution time : 40,295.721 ms