explain.depesz.com

PostgreSQL's explain analyze made readable

Result: emdC6 : VTC with pay_date index

Settings
# exclusive inclusive rows x rows loops node
1. 2,332.213 88,818.821 ↑ 85.7 972,353 1

Hash Left Join (cost=1,041,916.53..58,140,029.62 rows=83,378,497 width=1,871) (actual time=82,218.691..88,818.821 rows=972,353 loops=1)

  • Hash Cond: ((earn.peo_id = erncd.peo_id) AND (earn.pay_code = erncd.pay_code))
2. 1,192.763 86,484.145 ↑ 82.4 972,353 1

Gather (cost=1,041,834.95..57,717,723.19 rows=80,111,502 width=1,815) (actual time=82,210.351..86,484.145 rows=972,353 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 5,636.552 85,291.382 ↑ 103.0 324,118 3 / 3

Parallel Hash Left Join (cost=1,040,834.95..49,705,572.99 rows=33,379,792 width=1,815) (actual time=82,193.627..85,291.382 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. 672.818 79,554.829 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=1,006,611.30..34,543,453.83 rows=33,379,792 width=1,797) (actual time=70,928.444..79,554.829 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employer_id = epr.employer_id))
5. 501.028 78,880.105 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=1,006,599.13..34,367,840.86 rows=33,379,792 width=1,695) (actual time=70,924.063..78,880.105 rows=324,118 loops=3)

  • Hash Cond: ((cli.peo_id = fed.peo_id) AND (cli.entity_id = fed.entity_id))
6. 794.741 78,376.311 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=1,006,583.88..34,192,581.70 rows=33,379,792 width=1,638) (actual time=70,913.258..78,376.311 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = cli.peo_id) AND (epv.client_id = cli.client_id))
7. 607.721 77,566.888 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=1,006,206.78..34,016,815.80 rows=33,379,792 width=1,364) (actual time=70,894.985..77,566.888 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = esc3.peo_id) AND (epv.employee_status = esc3.status_code))
8. 8,092.356 76,956.197 ↑ 103.0 324,118 3 / 3

Parallel Hash Left Join (cost=1,006,193.03..33,841,546.57 rows=33,379,792 width=1,332) (actual time=70,889.482..76,956.197 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = emp.peo_id) AND (epv.employee_id = emp.employee_id))
9. 8,184.316 68,617.524 ↑ 103.0 324,118 3 / 3

Parallel Hash Left Join (cost=985,063.33..23,407,555.07 rows=33,379,792 width=1,225) (actual time=61,680.268..68,617.524 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,510.787 60,237.078 ↑ 103.0 324,118 3 / 3

Parallel Hash Left Join (cost=973,734.41..13,482,900.19 rows=33,379,792 width=1,157) (actual time=53,035.145..60,237.078 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. 423.082 51,560.558 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=959,128.31..3,686,050.13 rows=33,379,792 width=1,143) (actual time=31,679.051..51,560.558 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = etc3.peo_id) AND (epv.employee_type = etc3.type_code))
12. 373.176 51,136.816 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=959,115.06..3,510,777.56 rows=33,379,792 width=1,111) (actual time=31,677.907..51,136.816 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. 410.871 50,742.983 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=958,208.26..3,247,002.45 rows=33,379,792 width=1,070) (actual time=31,656.676..50,742.983 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. 397.547 50,306.788 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=957,301.46..2,983,227.09 rows=33,379,792 width=1,034) (actual time=31,630.673..50,306.788 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. 447.522 49,878.510 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=956,387.64..2,719,447.23 rows=33,379,792 width=1,019) (actual time=31,599.600..49,878.510 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. 358.623 49,404.716 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=955,473.83..2,455,667.42 rows=33,379,792 width=1,004) (actual time=31,573.007..49,404.716 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. 380.866 49,045.253 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=955,453.28..2,192,778.32 rows=33,379,792 width=989) (actual time=31,571.890..49,045.253 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. 496.251 48,661.465 ↑ 103.0 324,118 3 / 3

Hash Left Join (cost=955,432.74..1,929,889.78 rows=33,379,792 width=974) (actual time=31,568.500..48,661.465 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. 472.929 48,156.314 ↑ 23.1 324,118 3 / 3

Hash Left Join (cost=954,951.92..1,870,381.90 rows=7,489,568 width=954) (actual time=31,558.745..48,156.314 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. 440.892 47,675.947 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=954,471.10..1,851,037.78 rows=2,394,009 width=934) (actual time=31,549.802..47,675.947 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. 451.961 47,224.467 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=953,957.96..1,831,671.72 rows=2,394,009 width=922) (actual time=31,537.100..47,224.467 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. 467.264 46,759.875 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=953,444.82..1,812,305.63 rows=2,394,009 width=910) (actual time=31,523.361..46,759.875 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. 536.569 46,263.021 ↑ 7.4 324,118 3 / 3

Hash Left Join (cost=952,680.97..1,792,688.93 rows=2,394,009 width=894) (actual time=31,490.232..46,263.021 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. 27,853.109 45,700.875 ↑ 7.4 324,118 3 / 3

Parallel Hash Join (cost=951,917.12..1,773,072.23 rows=2,394,009 width=878) (actual time=31,463.171..45,700.875 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. 16,261.617 16,261.617 ↑ 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=7.970..16,261.617 rows=5,338,941 loops=3)

26. 719.403 1,586.149 ↑ 1.3 206,999 3 / 3

Parallel Hash (cost=924,962.93..924,962.93 rows=260,582 width=677) (actual time=1,586.148..1,586.149 rows=206,999 loops=3)

  • Buckets: 8192 Batches: 128 Memory Usage: 1632kB
27. 852.396 866.746 ↑ 1.3 206,999 3 / 3

Parallel Bitmap Heap Scan on epv (cost=13,290.97..924,962.93 rows=260,582 width=677) (actual time=53.132..866.746 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=20293 lossy=13409
28. 14.350 14.350 ↑ 1.0 621,000 1 / 3

Bitmap Index Scan on idx_epv_pay_date (cost=0.00..13,134.62 rows=625,419 width=0) (actual time=43.048..43.049 rows=621,000 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1603kB
30. 9.729 9.729 ↑ 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.213..9.729 rows=18,031 loops=3)

31. 22.625 29.590 ↑ 1.0 18,031 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1603kB
32. 6.965 6.965 ↑ 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.009..6.965 rows=18,031 loops=3)

33. 7.812 12.631 ↑ 1.0 12,296 3 / 3

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

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

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

35. 6.303 10.588 ↑ 1.0 12,296 3 / 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 895kB
36. 4.285 4.285 ↑ 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.014..4.285 rows=12,296 loops=3)

37. 4.024 7.438 ↑ 1.0 9,557 3 / 3

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

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

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

39. 5.008 8.900 ↑ 1.0 9,557 3 / 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 825kB
40. 3.892 3.892 ↑ 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.091..3.892 rows=9,557 loops=3)

41. 1.853 2.922 ↑ 1.0 529 3 / 3

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

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

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

43. 0.484 0.840 ↑ 1.0 529 3 / 3

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

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

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

45. 17.197 26.272 ↑ 1.0 22,175 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1828kB
46. 9.075 9.075 ↑ 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=1.003..9.075 rows=22,175 loops=3)

47. 22.688 30.731 ↑ 1.0 22,175 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1828kB
48. 8.043 8.043 ↑ 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.467..8.043 rows=22,175 loops=3)

49. 15.529 25.324 ↑ 1.0 22,720 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2148kB
50. 9.795 9.795 ↑ 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.596..9.795 rows=22,720 loops=3)

51. 14.269 20.657 ↑ 1.0 22,720 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2148kB
52. 6.388 6.388 ↑ 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.107..6.388 rows=22,720 loops=3)

53. 0.283 0.660 ↑ 3.8 34 3 / 3

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

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

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

55. 73.356 165.733 ↑ 1.3 94,094 3 / 3

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2912kB
56. 92.377 92.377 ↑ 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=1.768..92.377 rows=94,094 loops=3)

57. 85.664 196.130 ↑ 1.3 94,101 3 / 3

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

  • Buckets: 32768 Batches: 16 Memory Usage: 1792kB
58. 110.466 110.466 ↑ 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=1.287..110.466 rows=94,101 loops=3)

59. 97.003 246.317 ↑ 1.3 91,463 3 / 3

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

  • Buckets: 32768 Batches: 16 Memory Usage: 1888kB
60. 149.314 149.314 ↑ 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.412..149.314 rows=91,463 loops=3)

61. 0.267 2.970 ↑ 11.5 13 3 / 3

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

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

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

63. 4.462 14.682 ↑ 1.0 1,804 3 / 3

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

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

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

65. 0.001 2.766 ↓ 0.0 0 3 / 3

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

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

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

67. 0.047 1.906 ↓ 1.1 92 3 / 3

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

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

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

69. 44.019 100.001 ↑ 1.2 49,293 3 / 3

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3680kB
70. 55.982 55.982 ↑ 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.468..55.982 rows=49,293 loops=3)

71. 1.526 2.463 ↑ 1.0 623 1

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

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

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

Planning time : 111.132 ms
Execution time : 89,144.930 ms