explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CKbn : VTC without pay_date index

Settings
# exclusive inclusive rows x rows loops node
1. 2,293.411 121,179.070 ↑ 89.9 972,353 1

Hash Left Join (cost=1,076,205.48..60,254,361.67 rows=87,379,966 width=1,851) (actual time=114,391.001..121,179.070 rows=972,353 loops=1)

  • Hash Cond: ((earn.peo_id = erncd.peo_id) AND (earn.pay_code = erncd.pay_code))
2. 1,085.508 118,862.072 ↑ 86.3 972,353 1

Gather (cost=1,076,123.91..59,811,791.99 rows=83,956,183 width=1,795) (actual time=114,362.412..118,862.072 rows=972,353 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 6,142.464 117,776.564 ↑ 107.9 324,118 3 / 3

Parallel Hash Left Join (cost=1,075,123.91..51,415,173.69 rows=34,981,743 width=1,795) (actual time=114,358.886..117,776.564 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. 723.132 110,374.606 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=1,040,900.26..35,663,719.13 rows=34,981,743 width=1,777) (actual time=102,138.763..110,374.606 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employer_id = epr.employer_id))
5. 570.498 109,640.686 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=1,040,888.08..35,479,677.47 rows=34,981,743 width=1,675) (actual time=102,127.935..109,640.686 rows=324,118 loops=3)

  • Hash Cond: ((cli.peo_id = fed.peo_id) AND (cli.entity_id = fed.entity_id))
6. 816.649 109,069.769 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=1,040,872.83..35,296,008.07 rows=34,981,743 width=1,618) (actual time=102,127.475..109,069.769 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = cli.peo_id) AND (epv.client_id = cli.client_id))
7. 642.464 108,212.827 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=1,040,495.73..35,111,824.87 rows=34,981,743 width=1,344) (actual time=102,087.148..108,212.827 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = esc3.peo_id) AND (epv.employee_status = esc3.status_code))
8. 6,827.262 107,561.886 ↑ 107.9 324,118 3 / 3

Parallel Hash Left Join (cost=1,040,481.98..34,928,144.67 rows=34,981,743 width=1,312) (actual time=102,077.866..107,561.886 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = emp.peo_id) AND (epv.employee_id = emp.employee_id))
9. 9,112.035 99,884.690 ↑ 107.9 324,118 3 / 3

Parallel Hash Left Join (cost=1,019,352.29..24,199,490.89 rows=34,981,743 width=1,205) (actual time=91,974.769..99,884.690 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,074.091 90,284.060 ↑ 107.9 324,118 3 / 3

Parallel Hash Left Join (cost=1,008,023.36..13,935,804.64 rows=34,981,743 width=1,137) (actual time=83,463.078..90,284.060 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. 388.242 81,672.377 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=993,417.26..3,806,181.20 rows=34,981,743 width=1,123) (actual time=61,207.560..81,672.377 rows=324,118 loops=3)

  • Hash Cond: ((epv.peo_id = etc3.peo_id) AND (epv.employee_type = etc3.type_code))
12. 354.658 81,283.651 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=993,404.01..3,622,497.42 rows=34,981,743 width=1,091) (actual time=61,207.036..81,283.651 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. 345.898 80,904.685 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=992,497.21..3,346,106.84 rows=34,981,743 width=1,050) (actual time=61,182.546..80,904.685 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. 382.392 80,519.316 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=991,590.41..3,069,715.95 rows=34,981,743 width=1,014) (actual time=61,142.903..80,519.316 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. 366.880 80,114.957 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=990,676.60..2,793,320.71 rows=34,981,743 width=999) (actual time=61,120.761..80,114.957 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.885 79,694.495 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=989,762.79..2,516,925.54 rows=34,981,743 width=984) (actual time=61,067.009..79,694.495 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. 349.918 79,349.301 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=989,742.24..2,241,420.95 rows=34,981,743 width=969) (actual time=61,066.662..79,349.301 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. 399.756 78,992.833 ↑ 107.9 324,118 3 / 3

Hash Left Join (cost=989,721.69..1,965,916.91 rows=34,981,743 width=954) (actual time=61,060.077..78,992.833 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. 435.066 78,584.150 ↑ 23.3 324,118 3 / 3

Hash Left Join (cost=989,240.87..1,905,835.70 rows=7,562,088 width=934) (actual time=61,051.045..78,584.150 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. 408.944 78,099.092 ↑ 7.5 324,118 3 / 3

Hash Left Join (cost=988,760.06..1,886,308.93 rows=2,417,190 width=914) (actual time=61,000.952..78,099.092 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. 425.686 77,679.830 ↑ 7.5 324,118 3 / 3

Hash Left Join (cost=988,246.92..1,866,760.33 rows=2,417,190 width=902) (actual time=60,990.534..77,679.830 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. 431.399 77,228.582 ↑ 7.5 324,118 3 / 3

Hash Left Join (cost=987,733.78..1,847,211.68 rows=2,417,190 width=890) (actual time=60,964.868..77,228.582 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. 429.204 76,778.522 ↑ 7.5 324,118 3 / 3

Hash Left Join (cost=986,969.92..1,827,412.43 rows=2,417,190 width=874) (actual time=60,946.034..76,778.522 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. 29,627.145 76,314.476 ↑ 7.5 324,118 3 / 3

Parallel Hash Join (cost=986,206.07..1,807,613.18 rows=2,417,190 width=858) (actual time=60,910.981..76,314.476 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,238.598 14,238.598 ↑ 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=1.329..14,238.598 rows=5,338,941 loops=3)

26. 631.127 32,448.733 ↑ 1.3 206,999 3 / 3

Parallel Hash (cost=958,841.46..958,841.46 rows=269,635 width=657) (actual time=32,448.732..32,448.733 rows=206,999 loops=3)

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

Parallel Seq Scan on epv (cost=0.00..958,841.46 rows=269,635 width=657) (actual time=2,108.604..31,817.606 rows=206,999 loops=3)

  • Filter: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2020-03-17'::date) AND (status = 'PRNT'::text))
  • Rows Removed by Filter: 2491745
28. 9.889 34.842 ↑ 1.0 18,031 3 / 3

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

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

30. 13.989 18.661 ↑ 1.0 18,031 3 / 3

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

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

32. 5.668 25.562 ↑ 1.0 12,296 3 / 3

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

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

34. 6.371 10.318 ↑ 1.0 12,296 3 / 3

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

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

36. 5.113 49.992 ↑ 1.0 9,557 3 / 3

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

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

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

38. 5.173 8.927 ↑ 1.0 9,557 3 / 3

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

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

40. 0.234 6.550 ↑ 1.0 529 3 / 3

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

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

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

42. 0.204 0.309 ↑ 1.0 529 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
43. 0.105 0.105 ↑ 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.105 rows=529 loops=3)

44. 26.727 53.582 ↑ 1.0 22,175 3 / 3

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

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

46. 17.228 21.967 ↑ 1.0 22,175 3 / 3

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

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

48. 12.323 39.471 ↑ 1.0 22,720 3 / 3

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

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

50. 15.833 24.308 ↑ 1.0 22,720 3 / 3

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

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

52. 0.015 0.484 ↑ 3.8 34 3 / 3

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

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

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

54. 113.996 537.592 ↑ 1.3 94,094 3 / 3

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

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

56. 116.452 488.595 ↑ 1.3 94,101 3 / 3

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

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

58. 138.211 849.934 ↑ 1.3 91,463 3 / 3

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

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

60. 0.014 8.477 ↑ 11.5 13 3 / 3

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

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

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

62. 2.527 40.293 ↑ 1.0 1,804 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 452kB
63. 37.766 37.766 ↑ 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.168..37.766 rows=1,804 loops=3)

64. 0.000 0.419 ↓ 0.0 0 3 / 3

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

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

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

66. 0.061 10.788 ↓ 1.1 92 3 / 3

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

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

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

68. 55.387 1,259.494 ↑ 1.2 49,293 3 / 3

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

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

70. 1.115 23.587 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 74kB
71. 22.472 22.472 ↑ 1.0 623 1

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

Planning time : 186.479 ms
Execution time : 121,475.119 ms