explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RjVr

Settings
# exclusive inclusive rows x rows loops node
1. 2,228.765 92,387.470 ↑ 88.7 972,353 1

Hash Left Join (cost=1,039,768.08..60,299,731.69 rows=86,289,107 width=1,877) (actual time=86,115.922..92,387.470 rows=972,353 loops=1)

  • Hash Cond: ((earn.peo_id = erncd.peo_id) AND (earn.pay_code = erncd.pay_code))
2. 1,096.947 90,093.655 ↑ 85.3 972,353 1

Gather (cost=1,039,686.51..59,862,686.07 rows=82,908,066 width=1,821) (actual time=86,048.193..90,093.655 rows=972,353 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 5,588.872 88,996.708 ↑ 106.6 324,118 3 / 3

Parallel Hash Left Join (cost=1,038,686.51..51,570,879.47 rows=34,545,028 width=1,821) (actual time=86,014.443..88,996.708 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = bat.peo_id) AND (epv.client_id = bat.client_id) AND (epv.batch_no = bat.batch_no))
4. 733.235 81,869.463 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=1,004,462.86..35,813,220.01 rows=34,545,028 width=1,803) (actual time=73,305.785..81,869.463 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employer_id = epr.employer_id))
5. 606.537 81,120.772 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=1,004,450.68..35,631,484.06 rows=34,545,028 width=1,701) (actual time=73,287.771..81,120.772 rows=324,118 loops=3)

  • Hash Cond: ((cli.peo_id = fed.peo_id) AND (cli.entity_id = fed.entity_id))
6. 920.512 80,513.729 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=1,004,435.43..35,450,107.41 rows=34,545,028 width=1,644) (actual time=73,285.423..80,513.729 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = cli.peo_id) AND (epv.client_id = cli.client_id))
7. 591.368 79,496.588 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=1,004,058.33..35,268,218.60 rows=34,545,028 width=1,370) (actual time=73,185.822..79,496.588 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = esc3.peo_id) AND (epv.employee_status = esc3.status_code))
8. 7,443.077 78,896.291 ↑ 106.6 324,118 3 / 3

Parallel Hash Left Join (cost=1,004,044.58..35,086,831.36 rows=34,545,028 width=1,338) (actual time=73,173.866..78,896.291 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = emp.peo_id) AND (epv.employee_id = emp.employee_id))
9. 8,097.019 70,645.817 ↑ 106.6 324,118 3 / 3

Parallel Hash Left Join (cost=982,914.89..24,289,414.35 rows=34,545,028 width=1,231) (actual time=63,913.399..70,645.817 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = com.peo_id) AND (epv.client_id = com.client_id) AND (epv.employee_id = com.employee_id))
10. 8,244.818 62,076.278 ↑ 106.6 324,118 3 / 3

Parallel Hash Left Join (cost=971,585.96..13,951,287.22 rows=34,545,028 width=1,163) (actual time=55,026.764..62,076.278 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = pay.peo_id) AND (epv.client_id = pay.client_id) AND (epv.employee_id = pay.employee_id))
11. 392.023 53,273.284 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=956,979.86..3,745,516.91 rows=34,545,028 width=1,149) (actual time=32,045.807..53,273.284 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = etc3.peo_id) AND (epv.employee_type = etc3.type_code))
12. 322.999 52,880.976 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=956,966.61..3,564,126.18 rows=34,545,028 width=1,117) (actual time=32,045.479..52,880.976 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = wc2.peo_id) AND (earn.workers_comp_code = wc2.class_code) AND (epv.wc_state = wc2.state))
13. 346.069 52,533.835 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=956,059.81..3,291,174.76 rows=34,545,028 width=1,076) (actual time=32,021.151..52,533.835 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = wc3.peo_id) AND (epv.home_wc_class = wc3.class_code) AND (epv.wc_state = wc3.state))
14. 384.071 52,145.059 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=955,153.01..3,018,223.07 rows=34,545,028 width=1,040) (actual time=31,977.436..52,145.059 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = pwc2.peo_id) AND (earn.client_id = pwc2.client_id) AND (earn.project = pwc2.work_code))
15. 359.383 51,738.201 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=954,239.20..2,745,266.97 rows=34,545,028 width=1,025) (actual time=31,954.467..51,738.201 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = pwc3.peo_id) AND (earn.client_id = pwc3.client_id) AND (epv.home_project = pwc3.work_code))
16. 344.771 51,332.222 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=953,325.39..2,472,310.93 rows=34,545,028 width=1,010) (actual time=31,907.689..51,332.222 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = dvc2.peo_id) AND (earn.client_id = dvc2.client_id) AND (earn.division = dvc2.division_code))
17. 342.367 50,987.150 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=953,304.84..2,200,245.51 rows=34,545,028 width=995) (actual time=31,907.350..50,987.150 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = dvc3.peo_id) AND (earn.client_id = dvc3.client_id) AND (epv.home_division = dvc3.division_code))
18. 416.450 50,641.218 ↑ 106.6 324,118 3 / 3

Hash Left Join (cost=953,284.29..1,928,180.66 rows=34,545,028 width=980) (actual time=31,903.749..50,641.218 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = lc3.peo_id) AND (earn.client_id = lc3.client_id) AND (epv.home_location = lc3.location_code))
19. 414.026 50,214.949 ↑ 23.2 324,118 3 / 3

Hash Left Join (cost=952,803.47..1,868,525.79 rows=7,508,025 width=960) (actual time=31,893.820..50,214.949 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = lc2.peo_id) AND (earn.client_id = lc2.client_id) AND (earn.location = lc2.location_code))
20. 398.452 49,774.082 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=952,322.66..1,849,135.18 rows=2,399,909 width=940) (actual time=31,866.873..49,774.082 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = dc2.peo_id) AND (earn.client_id = dc2.client_id) AND (earn.department = dc2.dept_code))
21. 409.722 49,365.168 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=951,809.52..1,829,722.66 rows=2,399,909 width=928) (actual time=31,856.302..49,365.168 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = dc3.peo_id) AND (earn.client_id = dc3.client_id) AND (epv.home_department = dc3.dept_code))
22. 400.251 48,919.798 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=951,296.38..1,810,310.10 rows=2,399,909 width=916) (actual time=31,820.423..48,919.798 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = jc2.peo_id) AND (earn.client_id = jc2.client_id) AND (earn.position = jc2.job_code))
23. 413.602 48,487.777 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=950,532.52..1,790,646.94 rows=2,399,909 width=900) (actual time=31,788.478..48,487.777 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = jc3.peo_id) AND (earn.client_id = jc3.client_id) AND (epv.home_job = jc3.job_code))
24. 31,556.570 48,031.043 ↑ 7.4 324,118 3 / 3

Parallel Hash Join (cost=949,768.67..1,770,983.78 rows=2,399,909 width=884) (actual time=31,745.154..48,031.043 rows=324,118 loops=3)

  • Hash Cond: ((earn.peo_id = epv.peo_id) AND (earn.client_id = epv.client_id) AND (earn.voucher_id = epv.voucher_id))
25. 14,724.493 14,724.493 ↑ 1.2 5,338,941 3 / 3

Parallel Seq Scan on inf_epv_tsearn_rollup earn (cost=0.00..368,440.89 rows=6,669,989 width=208) (actual time=29.764..14,724.493 rows=5,338,941 loops=3)

26. 799.820 1,749.980 ↑ 1.2 206,999 3 / 3

Parallel Hash (cost=922,793.63..922,793.63 rows=258,345 width=683) (actual time=1,749.980..1,749.980 rows=206,999 loops=3)

  • Buckets: 8192 Batches: 128 Memory Usage: 1632kB
27. 935.222 950.160 ↑ 1.2 206,999 3 / 3

Parallel Bitmap Heap Scan on epv (cost=13,144.13..922,793.63 rows=258,345 width=683) (actual time=54.444..950.160 rows=206,999 loops=3)

  • Recheck Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2020-03-17'::date))
  • Rows Removed by Index Recheck: 350
  • Filter: (status = 'PRNT'::text)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=15048 lossy=8679
28. 14.938 14.938 ↓ 1.0 621,000 1 / 3

Bitmap Index Scan on idx_epv_pay_date (cost=0.00..12,989.12 rows=620,069 width=0) (actual time=44.815..44.815 rows=621,000 loops=1)

  • Index Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2020-03-17'::date))
29. 16.108 43.132 ↑ 1.0 18,031 3 / 3

Hash (cost=448.31..448.31 rows=18,031 width=42) (actual time=43.132..43.132 rows=18,031 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1603kB
30. 27.024 27.024 ↑ 1.0 18,031 3 / 3

Seq Scan on job_codes jc3 (cost=0.00..448.31 rows=18,031 width=42) (actual time=0.716..27.024 rows=18,031 loops=3)

31. 25.484 31.770 ↑ 1.0 18,031 3 / 3

Hash (cost=448.31..448.31 rows=18,031 width=42) (actual time=31.769..31.770 rows=18,031 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1603kB
32. 6.286 6.286 ↑ 1.0 18,031 3 / 3

Seq Scan on job_codes jc2 (cost=0.00..448.31 rows=18,031 width=42) (actual time=0.013..6.286 rows=18,031 loops=3)

33. 7.045 35.648 ↑ 1.0 12,296 3 / 3

Hash (cost=297.96..297.96 rows=12,296 width=29) (actual time=35.648..35.648 rows=12,296 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 895kB
34. 28.603 28.603 ↑ 1.0 12,296 3 / 3

Seq Scan on dept_codes dc3 (cost=0.00..297.96 rows=12,296 width=29) (actual time=1.190..28.603 rows=12,296 loops=3)

35. 7.996 10.462 ↑ 1.0 12,296 3 / 3

Hash (cost=297.96..297.96 rows=12,296 width=29) (actual time=10.462..10.462 rows=12,296 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 895kB
36. 2.466 2.466 ↑ 1.0 12,296 3 / 3

Seq Scan on dept_codes dc2 (cost=0.00..297.96 rows=12,296 width=29) (actual time=0.007..2.466 rows=12,296 loops=3)

37. 4.353 26.841 ↑ 1.0 9,557 3 / 3

Hash (cost=313.57..313.57 rows=9,557 width=40) (actual time=26.841..26.841 rows=9,557 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 825kB
38. 22.488 22.488 ↑ 1.0 9,557 3 / 3

Seq Scan on location_codes lc2 (cost=0.00..313.57 rows=9,557 width=40) (actual time=1.283..22.488 rows=9,557 loops=3)

39. 4.794 9.819 ↑ 1.0 9,557 3 / 3

Hash (cost=313.57..313.57 rows=9,557 width=40) (actual time=9.819..9.819 rows=9,557 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 825kB
40. 5.025 5.025 ↑ 1.0 9,557 3 / 3

Seq Scan on location_codes lc3 (cost=0.00..313.57 rows=9,557 width=40) (actual time=0.006..5.025 rows=9,557 loops=3)

41. 0.180 3.565 ↑ 1.0 529 3 / 3

Hash (cost=11.29..11.29 rows=529 width=34) (actual time=3.565..3.565 rows=529 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
42. 3.385 3.385 ↑ 1.0 529 3 / 3

Seq Scan on division_codes dvc3 (cost=0.00..11.29 rows=529 width=34) (actual time=0.665..3.385 rows=529 loops=3)

43. 0.198 0.301 ↑ 1.0 529 3 / 3

Hash (cost=11.29..11.29 rows=529 width=34) (actual time=0.301..0.301 rows=529 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
44. 0.103 0.103 ↑ 1.0 529 3 / 3

Seq Scan on division_codes dvc2 (cost=0.00..11.29 rows=529 width=34) (actual time=0.005..0.103 rows=529 loops=3)

45. 14.810 46.596 ↑ 1.0 22,175 3 / 3

Hash (cost=525.75..525.75 rows=22,175 width=39) (actual time=46.596..46.596 rows=22,175 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1828kB
46. 31.786 31.786 ↑ 1.0 22,175 3 / 3

Seq Scan on proj_work_codes pwc3 (cost=0.00..525.75 rows=22,175 width=39) (actual time=0.012..31.786 rows=22,175 loops=3)

47. 14.501 22.787 ↑ 1.0 22,175 3 / 3

Hash (cost=525.75..525.75 rows=22,175 width=39) (actual time=22.786..22.787 rows=22,175 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1828kB
48. 8.286 8.286 ↑ 1.0 22,175 3 / 3

Seq Scan on proj_work_codes pwc2 (cost=0.00..525.75 rows=22,175 width=39) (actual time=0.007..8.286 rows=22,175 loops=3)

49. 14.406 42.707 ↑ 1.0 22,720 3 / 3

Hash (cost=509.20..509.20 rows=22,720 width=52) (actual time=42.707..42.707 rows=22,720 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2148kB
50. 28.301 28.301 ↑ 1.0 22,720 3 / 3

Seq Scan on wc_class_codes wc3 (cost=0.00..509.20 rows=22,720 width=52) (actual time=0.636..28.301 rows=22,720 loops=3)

51. 14.359 24.142 ↑ 1.0 22,720 3 / 3

Hash (cost=509.20..509.20 rows=22,720 width=52) (actual time=24.142..24.142 rows=22,720 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2148kB
52. 9.783 9.783 ↑ 1.0 22,720 3 / 3

Seq Scan on wc_class_codes wc2 (cost=0.00..509.20 rows=22,720 width=52) (actual time=0.008..9.783 rows=22,720 loops=3)

53. 0.016 0.285 ↑ 3.8 34 3 / 3

Hash (cost=11.30..11.30 rows=130 width=96) (actual time=0.285..0.285 rows=34 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
54. 0.269 0.269 ↑ 3.8 34 3 / 3

Seq Scan on employee_type_codes etc3 (cost=0.00..11.30 rows=130 width=96) (actual time=0.263..0.269 rows=34 loops=3)

55. 110.428 558.176 ↑ 1.3 94,094 3 / 3

Parallel Hash (cost=11,627.40..11,627.40 rows=117,640 width=33) (actual time=558.176..558.176 rows=94,094 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2880kB
56. 447.748 447.748 ↑ 1.3 94,094 3 / 3

Parallel Seq Scan on employee_pay pay (cost=0.00..11,627.40 rows=117,640 width=33) (actual time=0.995..447.748 rows=94,094 loops=3)

57. 162.176 472.520 ↑ 1.3 94,101 3 / 3

Parallel Hash (cost=7,653.88..7,653.88 rows=117,888 width=87) (actual time=472.519..472.520 rows=94,101 loops=3)

  • Buckets: 32768 Batches: 16 Memory Usage: 1792kB
58. 310.344 310.344 ↑ 1.3 94,101 3 / 3

Parallel Seq Scan on employee_com com (cost=0.00..7,653.88 rows=117,888 width=87) (actual time=0.009..310.344 rows=94,101 loops=3)

59. 148.653 807.397 ↑ 1.3 91,463 3 / 3

Parallel Hash (cost=17,395.88..17,395.88 rows=114,588 width=120) (actual time=807.397..807.397 rows=91,463 loops=3)

  • Buckets: 32768 Batches: 16 Memory Usage: 1920kB
60. 658.744 658.744 ↑ 1.3 91,463 3 / 3

Parallel Seq Scan on employee_per emp (cost=0.00..17,395.88 rows=114,588 width=120) (actual time=1.181..658.744 rows=91,463 loops=3)

61. 0.326 8.929 ↑ 11.5 13 3 / 3

Hash (cost=11.50..11.50 rows=150 width=96) (actual time=8.929..8.929 rows=13 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 8.603 8.603 ↑ 11.5 13 3 / 3

Seq Scan on employee_status_codes esc3 (cost=0.00..11.50 rows=150 width=96) (actual time=8.591..8.603 rows=13 loops=3)

63. 2.315 96.629 ↑ 1.0 1,804 3 / 3

Hash (cost=350.04..350.04 rows=1,804 width=279) (actual time=96.628..96.629 rows=1,804 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 452kB
64. 94.314 94.314 ↑ 1.0 1,804 3 / 3

Seq Scan on client_master cli (cost=0.00..350.04 rows=1,804 width=279) (actual time=3.393..94.314 rows=1,804 loops=3)

65. 0.001 0.506 ↓ 0.0 0 3 / 3

Hash (cost=12.10..12.10 rows=210 width=128) (actual time=0.506..0.506 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
66. 0.505 0.505 ↓ 0.0 0 3 / 3

Seq Scan on client_federal_entities fed (cost=0.00..12.10 rows=210 width=128) (actual time=0.505..0.505 rows=0 loops=3)

67. 0.356 15.456 ↓ 1.1 92 3 / 3

Hash (cost=10.87..10.87 rows=87 width=115) (actual time=15.456..15.456 rows=92 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
68. 15.100 15.100 ↓ 1.1 92 3 / 3

Seq Scan on employer_master epr (cost=0.00..10.87 rows=87 width=115) (actual time=2.020..15.100 rows=92 loops=3)

69. 67.793 1,538.373 ↑ 1.2 49,293 3 / 3

Parallel Hash (cost=32,582.69..32,582.69 rows=60,169 width=50) (actual time=1,538.373..1,538.373 rows=49,293 loops=3)

  • Buckets: 65536 Batches: 4 Memory Usage: 3680kB
70. 1,470.580 1,470.580 ↑ 1.2 49,293 3 / 3

Parallel Seq Scan on batch_control bat (cost=0.00..32,582.69 rows=60,169 width=50) (actual time=1.472..1,470.580 rows=49,293 loops=3)

71. 0.725 65.050 ↑ 1.0 623 1

Hash (cost=72.23..72.23 rows=623 width=70) (actual time=65.049..65.050 rows=623 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 74kB
72. 64.325 64.325 ↑ 1.0 623 1

Seq Scan on earning_codes erncd (cost=0.00..72.23 rows=623 width=70) (actual time=2.113..64.325 rows=623 loops=1)

Planning time : 139.347 ms
Execution time : 92,687.619 ms