explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yikx

Settings
# exclusive inclusive rows x rows loops node
1. 1,127.524 83,834.857 ↑ 69.6 1,225,904 1

Hash Left Join (cost=1,150,671.50..119,224,596.73 rows=85,360,256 width=1,851) (actual time=31,626.865..83,834.857 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = erncd.peo_id) AND (earn.pay_code = erncd.pay_code))
2. 4,938.570 82,648.428 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,150,589.92..118,792,254.77 rows=82,015,610 width=1,795) (actual time=31,567.912..82,648.428 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = bat.peo_id) AND (epv.client_id = bat.client_id) AND (epv.batch_no = bat.batch_no))
3. 1,083.948 76,084.741 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,113,226.76..81,905,399.64 rows=82,015,610 width=1,777) (actual time=5,654.267..76,084.741 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employer_id = epr.employer_id))
4. 854.863 75,000.722 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,113,214.58..81,473,915.14 rows=82,015,610 width=1,675) (actual time=5,654.167..75,000.722 rows=1,225,904 loops=1)

  • Hash Cond: ((cli.peo_id = fed.peo_id) AND (cli.entity_id = fed.entity_id))
5. 1,257.993 74,145.857 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,113,199.33..81,043,317.94 rows=82,015,610 width=1,618) (actual time=5,654.130..74,145.857 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = cli.peo_id) AND (epv.client_id = cli.client_id))
6. 904.155 72,876.248 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,112,822.23..80,612,003.33 rows=82,015,610 width=1,344) (actual time=5,642.460..72,876.248 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = esc3.peo_id) AND (epv.employee_status = esc3.status_code))
7. 5,995.739 71,972.075 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,112,808.48..80,181,378.94 rows=82,015,610 width=1,312) (actual time=5,642.368..71,972.075 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = emp.peo_id) AND (epv.employee_id = emp.employee_id))
8. 8,978.763 64,839.148 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,084,848.23..55,049,241.54 rows=82,015,610 width=1,205) (actual time=4,477.597..64,839.148 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = com.peo_id) AND (epv.client_id = com.client_id) AND (epv.employee_id = com.employee_id))
9. 6,458.787 55,633.925 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,066,723.60..30,994,110.74 rows=82,015,610 width=1,137) (actual time=2,645.590..55,633.925 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = pay.peo_id) AND (epv.client_id = pay.client_id) AND (epv.employee_id = pay.employee_id))
10. 799.309 48,943.036 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,046,302.33..7,258,718.29 rows=82,015,610 width=1,123) (actual time=2,392.599..48,943.036 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = etc3.peo_id) AND (epv.employee_type = etc3.type_code))
11. 753.615 48,143.710 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,046,289.08..6,828,084.90 rows=82,015,610 width=1,091) (actual time=2,392.554..48,143.710 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = wc2.peo_id) AND (earn.workers_comp_code = wc2.class_code) AND (epv.wc_state = wc2.state))
12. 755.674 47,381.338 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,045,382.28..6,181,299.16 rows=82,015,610 width=1,050) (actual time=2,383.643..47,381.338 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = wc3.peo_id) AND (epv.home_wc_class = wc3.class_code) AND (epv.wc_state = wc3.state))
13. 760.358 46,616.938 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,044,475.48..5,534,512.69 rows=82,015,610 width=1,014) (actual time=2,374.761..46,616.938 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = pwc2.peo_id) AND (earn.client_id = pwc2.client_id) AND (earn.project = pwc2.work_code))
14. 780.552 45,846.645 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,043,561.67..4,887,725.50 rows=82,015,610 width=999) (actual time=2,364.672..45,846.645 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = pwc3.peo_id) AND (earn.client_id = pwc3.client_id) AND (epv.home_project = pwc3.work_code))
15. 731.913 45,054.903 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,042,647.86..4,240,938.42 rows=82,015,610 width=984) (actual time=2,353.340..45,054.903 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = dvc2.peo_id) AND (earn.client_id = dvc2.client_id) AND (earn.division = dvc2.division_code))
16. 729.148 44,322.776 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,042,627.31..3,595,038.32 rows=82,015,610 width=969) (actual time=2,353.104..44,322.776 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = dvc3.peo_id) AND (earn.client_id = dvc3.client_id) AND (epv.home_division = dvc3.division_code))
17. 872.002 43,593.428 ↑ 66.9 1,225,904 1

Hash Left Join (cost=1,042,606.76..2,949,139.27 rows=82,015,610 width=954) (actual time=2,352.878..43,593.428 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = lc3.peo_id) AND (earn.client_id = lc3.client_id) AND (epv.home_location = lc3.location_code))
18. 892.007 42,717.441 ↑ 14.4 1,225,904 1

Hash Left Join (cost=1,042,125.95..2,809,639.94 rows=17,638,569 width=934) (actual time=2,348.809..42,717.441 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = lc2.peo_id) AND (earn.client_id = lc2.client_id) AND (earn.location = lc2.location_code))
19. 802.967 41,821.392 ↑ 4.6 1,225,904 1

Hash Left Join (cost=1,041,645.13..2,764,734.43 rows=5,638,095 width=914) (actual time=2,344.681..41,821.392 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = dc2.peo_id) AND (earn.client_id = dc2.client_id) AND (earn.department = dc2.dept_code))
20. 853.410 41,013.427 ↑ 4.6 1,225,904 1

Hash Left Join (cost=1,041,131.99..2,719,821.08 rows=5,638,095 width=902) (actual time=2,339.583..41,013.427 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = dc3.peo_id) AND (earn.client_id = dc3.client_id) AND (epv.home_department = dc3.dept_code))
21. 841.835 40,153.537 ↑ 4.6 1,225,904 1

Hash Left Join (cost=1,040,618.85..2,674,907.63 rows=5,638,095 width=890) (actual time=2,333.002..40,153.537 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = jc2.peo_id) AND (earn.client_id = jc2.client_id) AND (earn.position = jc2.job_code))
22. 906.853 39,303.976 ↑ 4.6 1,225,904 1

Hash Left Join (cost=1,039,855.00..2,629,743.73 rows=5,638,095 width=874) (actual time=2,324.573..39,303.976 rows=1,225,904 loops=1)

  • Hash Cond: ((epv.peo_id = jc3.peo_id) AND (earn.client_id = jc3.client_id) AND (epv.home_job = jc3.job_code))
23. 23,865.428 38,388.753 ↑ 4.6 1,225,904 1

Hash Right Join (cost=1,039,091.14..2,584,579.80 rows=5,638,095 width=858) (actual time=2,313.087..38,388.753 rows=1,225,904 loops=1)

  • Hash Cond: ((earn.peo_id = epv.peo_id) AND (earn.client_id = epv.client_id) AND (earn.voucher_id = epv.voucher_id))
24. 12,491.535 12,491.535 ↓ 1.0 16,016,823 1

Seq Scan on inf_epv_tsearn_rollup earn (cost=0.00..461,820.74 rows=16,007,974 width=208) (actual time=3.361..12,491.535 rows=16,016,823 loops=1)

25. 1,269.951 2,031.790 ↓ 1.0 620,997 1

Hash (cost=977,581.48..977,581.48 rows=606,095 width=657) (actual time=2,031.789..2,031.790 rows=620,997 loops=1)

  • Buckets: 8192 Batches: 128 Memory Usage: 1526kB
26. 52.540 761.839 ↓ 1.0 620,997 1

Gather (cost=13,889.11..977,581.48 rows=606,095 width=657) (actual time=75.929..761.839 rows=620,997 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
27. 691.211 709.299 ↑ 1.2 206,999 3 / 3

Parallel Bitmap Heap Scan on epv (cost=12,889.11..915,971.98 rows=252,540 width=657) (actual time=46.197..709.299 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=13146 lossy=8283
28. 18.088 18.088 ↓ 1.0 621,000 1 / 3

Bitmap Index Scan on idx_epv_pay_date (cost=0.00..12,737.58 rows=606,115 width=0) (actual time=54.263..54.263 rows=621,000 loops=1)

  • Index Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2020-03-17'::date))
29. 5.239 8.370 ↑ 1.0 18,031 1

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

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

Seq Scan on job_codes jc3 (cost=0.00..448.31 rows=18,031 width=42) (actual time=0.012..3.131 rows=18,031 loops=1)

31. 5.088 7.726 ↑ 1.0 18,031 1

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

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

Seq Scan on job_codes jc2 (cost=0.00..448.31 rows=18,031 width=42) (actual time=0.007..2.638 rows=18,031 loops=1)

33. 4.276 6.480 ↑ 1.0 12,296 1

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

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

Seq Scan on dept_codes dc3 (cost=0.00..297.96 rows=12,296 width=29) (actual time=0.008..2.204 rows=12,296 loops=1)

35. 3.239 4.998 ↑ 1.0 12,296 1

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

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

Seq Scan on dept_codes dc2 (cost=0.00..297.96 rows=12,296 width=29) (actual time=0.008..1.759 rows=12,296 loops=1)

37. 2.540 4.042 ↑ 1.0 9,557 1

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

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

Seq Scan on location_codes lc2 (cost=0.00..313.57 rows=9,557 width=40) (actual time=0.005..1.502 rows=9,557 loops=1)

39. 2.511 3.985 ↑ 1.0 9,557 1

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

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

Seq Scan on location_codes lc3 (cost=0.00..313.57 rows=9,557 width=40) (actual time=0.004..1.474 rows=9,557 loops=1)

41. 0.134 0.200 ↑ 1.0 529 1

Hash (cost=11.29..11.29 rows=529 width=34) (actual time=0.200..0.200 rows=529 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
42. 0.066 0.066 ↑ 1.0 529 1

Seq Scan on division_codes dvc3 (cost=0.00..11.29 rows=529 width=34) (actual time=0.004..0.066 rows=529 loops=1)

43. 0.154 0.214 ↑ 1.0 529 1

Hash (cost=11.29..11.29 rows=529 width=34) (actual time=0.214..0.214 rows=529 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
44. 0.060 0.060 ↑ 1.0 529 1

Seq Scan on division_codes dvc2 (cost=0.00..11.29 rows=529 width=34) (actual time=0.002..0.060 rows=529 loops=1)

45. 7.466 11.190 ↑ 1.0 22,175 1

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

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

Seq Scan on proj_work_codes pwc3 (cost=0.00..525.75 rows=22,175 width=39) (actual time=0.005..3.724 rows=22,175 loops=1)

47. 6.656 9.935 ↑ 1.0 22,175 1

Hash (cost=525.75..525.75 rows=22,175 width=39) (actual time=9.934..9.935 rows=22,175 loops=1)

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

Seq Scan on proj_work_codes pwc2 (cost=0.00..525.75 rows=22,175 width=39) (actual time=0.006..3.279 rows=22,175 loops=1)

49. 5.565 8.726 ↑ 1.0 22,720 1

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

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

Seq Scan on wc_class_codes wc3 (cost=0.00..509.20 rows=22,720 width=52) (actual time=0.006..3.161 rows=22,720 loops=1)

51. 5.619 8.757 ↑ 1.0 22,720 1

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

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

Seq Scan on wc_class_codes wc2 (cost=0.00..509.20 rows=22,720 width=52) (actual time=0.006..3.138 rows=22,720 loops=1)

53. 0.010 0.017 ↑ 3.8 34 1

Hash (cost=11.30..11.30 rows=130 width=96) (actual time=0.017..0.017 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
54. 0.007 0.007 ↑ 3.8 34 1

Seq Scan on employee_type_codes etc3 (cost=0.00..11.30 rows=130 width=96) (actual time=0.004..0.007 rows=34 loops=1)

55. 94.549 232.102 ↑ 1.0 282,283 1

Hash (cost=13,274.37..13,274.37 rows=282,337 width=33) (actual time=232.102..232.102 rows=282,283 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2725kB
56. 137.553 137.553 ↑ 1.0 282,283 1

Seq Scan on employee_pay pay (cost=0.00..13,274.37 rows=282,337 width=33) (actual time=1.098..137.553 rows=282,283 loops=1)

57. 101.556 226.460 ↑ 1.0 282,304 1

Hash (cost=9,304.32..9,304.32 rows=282,932 width=87) (actual time=226.460..226.460 rows=282,304 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 1671kB
58. 124.904 124.904 ↑ 1.0 282,304 1

Seq Scan on employee_com com (cost=0.00..9,304.32 rows=282,932 width=87) (actual time=0.015..124.904 rows=282,304 loops=1)

59. 244.943 1,137.188 ↑ 1.0 274,389 1

Hash (cost=19,000.10..19,000.10 rows=275,010 width=120) (actual time=1,137.188..1,137.188 rows=274,389 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 1792kB
60. 892.245 892.245 ↑ 1.0 274,389 1

Seq Scan on employee_per emp (cost=0.00..19,000.10 rows=275,010 width=120) (actual time=0.722..892.245 rows=274,389 loops=1)

61. 0.009 0.018 ↑ 11.5 13 1

Hash (cost=11.50..11.50 rows=150 width=96) (actual time=0.017..0.018 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.009 0.009 ↑ 11.5 13 1

Seq Scan on employee_status_codes esc3 (cost=0.00..11.50 rows=150 width=96) (actual time=0.006..0.009 rows=13 loops=1)

63. 1.125 11.616 ↑ 1.0 1,804 1

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

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

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

65. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=12.10..12.10 rows=210 width=128) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
66. 0.002 0.002 ↓ 0.0 0 1

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

67. 0.031 0.071 ↓ 1.1 92 1

Hash (cost=10.87..10.87 rows=87 width=115) (actual time=0.071..0.071 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
68. 0.040 0.040 ↓ 1.1 92 1

Seq Scan on employer_master epr (cost=0.00..10.87 rows=87 width=115) (actual time=0.004..0.040 rows=92 loops=1)

69. 149.415 1,625.117 ↓ 1.0 147,880 1

Hash (cost=33,425.06..33,425.06 rows=144,406 width=50) (actual time=1,625.117..1,625.117 rows=147,880 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3534kB
70. 1,475.702 1,475.702 ↓ 1.0 147,880 1

Seq Scan on batch_control bat (cost=0.00..33,425.06 rows=144,406 width=50) (actual time=0.973..1,475.702 rows=147,880 loops=1)

71. 0.401 58.905 ↑ 1.0 623 1

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

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

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

Planning time : 72.949 ms
Execution time : 83,950.831 ms