explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sLq7 : pipe

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 39,055.303 ↓ 15.0 15 1

Limit (cost=127,660.97..127,660.98 rows=1 width=2,199) (actual time=39,055.297..39,055.303 rows=15 loops=1)

2. 0.164 39,055.299 ↓ 15.0 15 1

Sort (cost=127,660.97..127,660.98 rows=1 width=2,199) (actual time=39,055.296..39,055.299 rows=15 loops=1)

  • Sort Key: ((SubPlan 3)) DESC NULLS LAST, this_.jobpostinghiringid DESC NULLS LAST
  • Sort Method: quicksort Memory: 85kB
3. 3.286 39,055.135 ↓ 15.0 15 1

WindowAgg (cost=122,299.11..127,660.96 rows=1 width=2,199) (actual time=39,052.401..39,055.135 rows=15 loops=1)

4. 0.036 39,051.129 ↓ 15.0 15 1

Nested Loop (cost=122,299.11..127,634.40 rows=1 width=2,199) (actual time=11,403.783..39,051.129 rows=15 loops=1)

5. 0.039 39,050.958 ↓ 15.0 15 1

Nested Loop (cost=122,298.68..127,630.57 rows=1 width=2,186) (actual time=11,403.769..39,050.958 rows=15 loops=1)

6. 0.118 39,050.814 ↓ 15.0 15 1

Nested Loop (cost=122,298.39..127,623.26 rows=1 width=2,164) (actual time=11,403.754..39,050.814 rows=15 loops=1)

7. 0.318 39,049.920 ↓ 97.0 97 1

Nested Loop (cost=122,298.11..127,622.92 rows=1 width=2,169) (actual time=2,428.747..39,049.920 rows=97 loops=1)

8. 0.317 39,048.535 ↓ 97.0 97 1

Nested Loop (cost=122,297.68..127,620.77 rows=1 width=2,133) (actual time=2,428.730..39,048.535 rows=97 loops=1)

9. 0.230 39,047.442 ↓ 97.0 97 1

Nested Loop Left Join (cost=122,297.26..127,617.65 rows=1 width=2,003) (actual time=2,428.714..39,047.442 rows=97 loops=1)

10. 0.439 39,046.921 ↓ 97.0 97 1

Nested Loop (cost=122,296.84..127,617.10 rows=1 width=2,003) (actual time=2,428.710..39,046.921 rows=97 loops=1)

  • Join Filter: (this_.employeeid = employeejo9_.employeeid)
11. 319.616 39,045.221 ↓ 97.0 97 1

Nested Loop (cost=122,296.41..127,616.56 rows=1 width=871) (actual time=2,428.679..39,045.221 rows=97 loops=1)

  • Join Filter: (this_.placementstatus = placements13_.pipelinesubstatusid)
  • Rows Removed by Join Filter: 1048871
12. 0.285 0.285 ↓ 17.0 17 1

Index Scan using pss_cmpid_pstatus_indx on pipelinesubstatus placements13_ (cost=0.29..132.45 rows=1 width=34) (actual time=0.055..0.285 rows=17 loops=1)

  • Index Cond: (companyid = '1943'::numeric)
  • Filter: ((pipelinesubstatusid = '32016'::numeric) OR (pipelinesubstatusid = '31850'::numeric) OR (pipelinesubstatusid = '32520'::numeric) OR (pipelinesubstatusid = '32014'::numeric) OR (pipelinesubstatusid = '32015'::numeric) OR (pipelinesubstatusid = '31851'::numeric) OR (pipelinesubstatusid = '31373'::numeric) OR (pipelinesubstatusid = '32012'::numeric) OR (pipelinesubstatusid = '31372'::numeric) OR (pipelinesubstatusid = '32013'::numeric) OR (pipelinesubstatusid = '32011'::numeric) OR (pipelinesubstatusid = '31857'::numeric) OR (pipelinesubstatusid = '31849'::numeric) OR (pipelinesubstatusid = '31848'::numeric) OR (pipelinesubstatusid = '32724'::numeric) OR (pipelinesubstatusid = '31853'::numeric) OR (pipelinesubstatusid = '31855'::numeric))
  • Rows Removed by Filter: 35
13. 2,744.633 38,725.320 ↓ 62.0 61,704 17

Hash Right Join (cost=122,296.12..127,471.67 rows=995 width=843) (actual time=2,076.056..2,277.960 rows=61,704 loops=17)

  • Hash Cond: (employeesu1_.employeeid = e15_.employeeid)
14. 712.606 712.606 ↑ 1.1 182,689 17

Seq Scan on employeesubcontractordetails employeesu1_ (cost=0.00..4,428.88 rows=198,888 width=12) (actual time=0.005..41.918 rows=182,689 loops=17)

15. 2,797.452 35,268.081 ↓ 62.0 61,704 17

Hash (cost=122,283.69..122,283.69 rows=995 width=837) (actual time=2,074.593..2,074.593 rows=61,704 loops=17)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 32257kB
16. 914.753 32,470.629 ↓ 62.0 61,704 17

Nested Loop (cost=90,244.62..122,283.69 rows=995 width=837) (actual time=18.103..1,910.037 rows=61,704 loops=17)

17. 1,696.022 28,408.666 ↓ 62.0 61,710 17

Nested Loop (cost=90,244.33..121,972.38 rows=995 width=818) (actual time=18.092..1,671.098 rows=61,710 loops=17)

18. 989.502 22,516.364 ↓ 62.0 61,710 17

Nested Loop (cost=90,243.90..119,138.07 rows=995 width=800) (actual time=18.078..1,324.492 rows=61,710 loops=17)

19. 963.713 17,330.174 ↓ 62.0 61,716 17

Nested Loop (cost=90,243.47..115,148.05 rows=995 width=787) (actual time=18.070..1,019.422 rows=61,716 loops=17)

20. 1,272.807 12,169.773 ↓ 62.0 61,716 17

Nested Loop (cost=90,243.18..114,590.96 rows=995 width=768) (actual time=18.057..715.869 rows=61,716 loops=17)

21. 1,766.351 6,700.278 ↓ 62.0 61,716 17

Nested Loop (cost=90,242.75..110,600.94 rows=995 width=755) (actual time=18.042..394.134 rows=61,716 loops=17)

22. 1,659.880 2,835.583 ↓ 58.6 61,716 17

Hash Join (cost=90,242.47..110,277.16 rows=1,054 width=735) (actual time=18.030..166.799 rows=61,716 loops=17)

  • Hash Cond: (this_.employeeid = e15_.employeeid)
23. 884.595 1,020.051 ↓ 1.1 61,755 17

Bitmap Heap Scan on jobpostinghiring this_ (cost=1,241.86..21,059.14 rows=55,165 width=88) (actual time=8.864..60.003 rows=61,755 loops=17)

  • Recheck Cond: (companyid = '1943'::numeric)
  • Filter: (hired = '0'::numeric)
  • Rows Removed by Filter: 55
  • Heap Blocks: exact=99892
24. 135.456 135.456 ↓ 1.3 71,390 17

Bitmap Index Scan on jph_companyid_idx (cost=0.00..1,228.06 rows=56,352 width=0) (actual time=7.968..7.968 rows=71,390 loops=17)

  • Index Cond: (companyid = '1943'::numeric)
25. 61.649 155.652 ↓ 1.1 49,577 1

Hash (cost=88,440.54..88,440.54 rows=44,806 width=647) (actual time=155.652..155.652 rows=49,577 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 18350kB
26. 82.316 94.003 ↓ 1.1 49,577 1

Bitmap Heap Scan on employee e15_ (cost=1,068.69..88,440.54 rows=44,806 width=647) (actual time=15.159..94.003 rows=49,577 loops=1)

  • Recheck Cond: ((companyid = '1943'::numeric) AND (terminationflag = '0'::numeric))
  • Heap Blocks: exact=20567
27. 11.687 11.687 ↓ 1.1 49,687 1

Bitmap Index Scan on emp_cmpid_terflag_ht_indx (cost=0.00..1,057.49 rows=44,806 width=0) (actual time=11.687..11.687 rows=49,687 loops=1)

  • Index Cond: ((companyid = '1943'::numeric) AND (terminationflag = '0'::numeric))
28. 2,098.344 2,098.344 ↑ 1.0 1 1,049,172

Index Scan using deptdet_deptid_pk on departmentdetails department3_ (cost=0.28..0.30 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=1,049,172)

  • Index Cond: (departmentid = e15_.departmentid)
29. 4,196.688 4,196.688 ↑ 1.0 1 1,049,172

Index Scan using emp_pk on employee employeeby8_ (cost=0.43..4.00 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1,049,172)

  • Index Cond: (employeeid = e15_.recruiterempid)
30. 4,196.688 4,196.688 ↑ 1.0 1 1,049,172

Index Scan using emty_pk on employeetype employeety5_ (cost=0.29..0.55 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=1,049,172)

  • Index Cond: (employeetypeid = e15_.employeetypeid)
31. 4,196.688 4,196.688 ↑ 1.0 1 1,049,172

Index Scan using emp_pk on employee reportingt4_ (cost=0.43..4.00 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=1,049,172)

  • Index Cond: (employeeid = e15_.reportingtoemployeeid)
32. 4,196.280 4,196.280 ↑ 1.0 1 1,049,070

Index Scan using visatr_employeeid_indx on visatracker visatracke6_ (cost=0.43..2.84 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=1,049,070)

  • Index Cond: (employeeid = this_.employeeid)
  • Filter: (visaholder = '1'::numeric)
33. 3,147.210 3,147.210 ↑ 1.0 1 1,049,070

Index Scan using restype_pk on residenttype residentty7_ (cost=0.29..0.30 rows=1 width=25) (actual time=0.002..0.003 rows=1 loops=1,049,070)

  • Index Cond: (residenttypeid = visatracke6_.residenttypeid)
34. 1.261 1.261 ↑ 1.0 1 97

Index Scan using empjobpref_emp_indx on employeejobpreference employeejo9_ (cost=0.43..0.53 rows=1 width=1,144) (actual time=0.012..0.013 rows=1 loops=97)

  • Index Cond: (employeeid = visatracke6_.employeeid)
35. 0.291 0.291 ↓ 0.0 0 97

Index Only Scan using companydetails_pk on companydetails employeesu2_ (cost=0.42..0.54 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=97)

  • Index Cond: (companyid = employeesu1_.subcontractorcompanyid)
  • Heap Fetches: 29
36. 0.776 0.776 ↑ 1.0 1 97

Index Scan using jobposting_pk on jobposting jobposting11_ (cost=0.42..3.11 rows=1 width=130) (actual time=0.007..0.008 rows=1 loops=97)

  • Index Cond: (jobpostingid = this_.jobpostingid)
37. 1.067 1.067 ↑ 1.0 1 97

Index Scan using contact_pk on contact contact10_ (cost=0.42..2.14 rows=1 width=42) (actual time=0.010..0.011 rows=1 loops=97)

  • Index Cond: (contactid = jobposting11_.businesscontactid)
38. 0.776 0.776 ↓ 0.0 0 97

Index Only Scan using jobstatus_pk on jobstatustype jobstatust12_ (cost=0.29..0.32 rows=1 width=6) (actual time=0.008..0.008 rows=0 loops=97)

  • Index Cond: (jobstatustypeid = jobposting11_.jobstatustypeid)
  • 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: 1
  • Heap Fetches: 97
39. 0.105 0.105 ↑ 1.0 1 15

Index Scan using pipelinestatus_pkey on pipelinestatus pipelinest14_ (cost=0.29..7.31 rows=1 width=28) (actual time=0.006..0.007 rows=1 loops=15)

  • Index Cond: (pipelinestatusid = placements13_.pipelinestatusid)
  • Filter: (companyid = '1943'::numeric)
40. 0.135 0.135 ↑ 1.0 1 15

Index Scan using emp_pk on employee postedby16_ (cost=0.43..3.82 rows=1 width=20) (actual time=0.008..0.009 rows=1 loops=15)

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

SubPlan (forWindowAgg)

42. 0.030 0.165 ↑ 1.0 1 15

Aggregate (cost=3.94..3.95 rows=1 width=6) (actual time=0.011..0.011 rows=1 loops=15)

43. 0.135 0.135 ↑ 1.0 1 15

Index Only Scan using resumeview_pkey on resumeview rv (cost=0.42..3.94 rows=1 width=6) (actual time=0.008..0.009 rows=1 loops=15)

  • Index Cond: (employeeid = this_.employeeid)
  • Heap Fetches: 15
44. 0.015 0.105 ↑ 1.0 1 15

Limit (cost=0.42..7.44 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=15)

45. 0.090 0.090 ↑ 1.0 1 15

Index Scan using cpjp_jpoid_indx on clientprojectjobposting cpjp (cost=0.42..7.44 rows=1 width=6) (actual time=0.006..0.006 rows=1 loops=15)

  • Index Cond: (jobpostingid = this_.jobpostingid)
46. 0.030 0.450 ↑ 1.0 1 15

Limit (cost=14.89..14.90 rows=1 width=17) (actual time=0.030..0.030 rows=1 loops=15)

47. 0.060 0.420 ↑ 1.0 1 15

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

  • Group Key: cd.companyname
48. 0.089 0.360 ↓ 3.0 3 15

Nested Loop (cost=0.84..14.89 rows=1 width=17) (actual time=0.010..0.024 rows=3 loops=15)

49. 0.075 0.075 ↓ 3.0 3 15

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=3 loops=15)

  • Index Cond: (jobpostingid = this_.jobpostingid)
50. 0.196 0.196 ↑ 1.0 1 49

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

  • Index Cond: (companyid = scpjp.accountid)
Planning time : 38.908 ms
Execution time : 39,056.252 ms