explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6fJW

Settings
# exclusive inclusive rows x rows loops node
1. 2,399.382 45,249.618 ↑ 1.0 613,107 1

Hash Left Join (cost=664,676.27..2,256,281.99 rows=621,021 width=3,534) (actual time=24,276.847..45,249.618 rows=613,107 loops=1)

  • Hash Cond: ((epv.peo_id = e4ldetail.peo_id) AND (epv.client_id = e4ldetail.client_id) AND (epv.voucher_id = e4ldetail.voucher_id))
2. 3,358.087 36,619.539 ↑ 1.0 613,107 1

Hash Left Join (cost=89,394.98..1,670,909.11 rows=621,021 width=3,050) (actual time=18,046.105..36,619.539 rows=613,107 loops=1)

  • Hash Cond: ((epv.peo_id = e4kd.peo_id) AND (epv.client_id = e4kd.client_id) AND (epv.employee_id = e4kd.employee_id) AND ((SubPlan 2) = e4kd.effective_date))
3. 0.000 25,795.906 ↑ 1.0 613,107 1

Gather (cost=89,188.01..1,405,836.68 rows=621,021 width=2,992) (actual time=18,041.681..25,795.906 rows=613,107 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 979.666 30,311.574 ↑ 1.3 204,369 3 / 3

Nested Loop Left Join (cost=88,188.01..1,342,734.58 rows=258,759 width=2,992) (actual time=18,004.114..30,311.574 rows=204,369 loops=3)

5. 585.435 24,631.421 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=88,187.58..1,087,495.77 rows=258,759 width=2,987) (actual time=18,004.042..24,631.421 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = e4kp.peo_id) AND (epv.client_id = e4kp.client_id) AND (epv.employee_id = e4kp.employee_id))
6. 487.751 24,041.204 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=88,028.50..1,085,298.96 rows=258,759 width=2,871) (actual time=17,999.214..24,041.204 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = pyxref.peo_id) AND (epv.retire_plan = pyxref.plan_id))
7. 598.298 23,553.392 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=88,017.00..1,083,334.88 rows=258,759 width=2,743) (actual time=17,999.119..23,553.392 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = jobc.peo_id) AND (epv.client_id = jobc.client_id) AND (epv.home_job = jobc.job_code))
8. 552.360 22,936.414 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=87,253.15..1,080,533.30 rows=258,759 width=2,671) (actual time=17,980.270..22,936.414 rows=204,369 loops=3)

  • Hash Cond: ((epv.employer_id = em.employer_id) AND (epv.peo_id = em.peo_id))
9. 433.575 22,383.931 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=87,240.97..1,079,158.40 rows=258,759 width=2,569) (actual time=17,980.115..22,383.931 rows=204,369 loops=3)

  • Hash Cond: ((cm.entity_id = cfe.entity_id) AND (cm.peo_id = cfe.peo_id))
10. 673.085 21,950.337 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=87,225.72..1,077,784.67 rows=258,759 width=2,512) (actual time=17,980.062..21,950.337 rows=204,369 loops=3)

  • Hash Cond: ((epv.client_id = cm.client_id) AND (epv.peo_id = cm.peo_id))
11. 420.353 21,261.879 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=86,841.50..1,076,041.51 rows=258,759 width=2,141) (actual time=17,964.653..21,261.879 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = unionc.peo_id) AND (epv.client_id = unionc.client_id) AND (epv.union_code = unionc.union_code))
12. 505.445 20,841.333 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=86,826.96..1,073,989.24 rows=258,759 width=2,123) (actual time=17,963.870..20,841.333 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = wcc.peo_id) AND (epv.home_wc_class = wcc.class_code) AND (epv.wc_state = wcc.state))
13. 476.658 20,299.706 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=85,920.16..1,071,044.69 rows=258,759 width=2,082) (actual time=17,927.527..20,299.706 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = etc.peo_id) AND (epv.employee_type = etc.type_code))
14. 589.959 19,823.010 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=85,906.91..1,069,672.87 rows=258,759 width=2,018) (actual time=17,927.459..19,823.010 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = loc.peo_id) AND (epv.client_id = loc.client_id) AND (epv.home_location = loc.location_code))
15. 2,524.561 19,207.825 ↑ 1.3 204,369 3 / 3

Parallel Hash Left Join (cost=85,423.89..1,067,151.86 rows=258,759 width=1,876) (actual time=17,902.194..19,207.825 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = bc.peo_id) AND (epv.client_id = bc.client_id) AND (epv.batch_no = bc.batch_no))
16. 389.506 15,987.756 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=50,789.24..914,756.48 rows=258,759 width=1,790) (actual time=13,176.226..15,987.756 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = projc.peo_id) AND (epv.client_id = projc.client_id) AND (epv.home_project = projc.work_code))
17. 399.837 15,570.420 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=49,872.42..911,801.94 rows=258,759 width=1,775) (actual time=13,148.275..15,570.420 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = divc.peo_id) AND (epv.client_id = divc.client_id) AND (epv.home_division = divc.division_code))
18. 422.993 15,170.229 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=49,848.45..909,740.24 rows=258,759 width=1,760) (actual time=13,147.904..15,170.229 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = deptc.peo_id) AND (epv.client_id = deptc.client_id) AND (epv.home_department = deptc.dept_code))
19. 453.551 14,736.073 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=49,335.31..907,189.38 rows=258,759 width=1,748) (actual time=13,136.712..14,736.073 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = ecs.peo_id) AND (epv.employee_status = ecs.status_code))
20. 2,266.713 14,282.491 ↑ 1.3 204,369 3 / 3

Parallel Hash Left Join (cost=49,321.56..905,817.07 rows=258,759 width=1,652) (actual time=13,136.656..14,282.491 rows=204,369 loops=3)

  • Hash Cond: ((epv.employee_id = eper.employee_id) AND (epv.peo_id = eper.peo_id))
21. 2,214.970 11,681.913 ↑ 1.3 204,369 3 / 3

Parallel Hash Left Join (cost=26,950.46..784,836.48 rows=258,759 width=1,461) (actual time=10,521.879..11,681.913 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = ecom.peo_id) AND (epv.client_id = ecom.client_id) AND (epv.employee_id = ecom.employee_id))
22. 2,025.065 9,160.507 ↑ 1.3 204,369 3 / 3

Parallel Hash Left Join (cost=16,068.52..678,781.82 rows=258,759 width=1,430) (actual time=8,101.297..9,160.507 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = epay.peo_id) AND (epv.client_id = epay.client_id) AND (epv.employee_id = epay.employee_id))
23. 6,960.776 6,960.776 ↑ 1.3 204,369 3 / 3

Parallel Seq Scan on epv (cost=0.00..568,705.57 rows=258,759 width=1,416) (actual time=3,367.273..6,960.776 rows=204,369 loops=3)

  • Filter: ((modified > '2020-05-01 00:00:00'::timestamp without time zone) AND (status = 'PRNT'::text))
  • Rows Removed by Filter: 1,572,513
24. 84.339 174.666 ↑ 1.4 94,095 3 / 3

Parallel Hash (cost=12,830.10..12,830.10 rows=127,910 width=33) (actual time=174.665..174.666 rows=94,095 loops=3)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,880kB
25. 90.327 90.327 ↑ 1.4 94,095 3 / 3

Parallel Seq Scan on employee_pay epay (cost=0.00..12,830.10 rows=127,910 width=33) (actual time=0.007..90.327 rows=94,095 loops=3)

26. 136.485 306.436 ↑ 1.3 94,105 3 / 3

Parallel Hash (cost=7,663.34..7,663.34 rows=118,034 width=50) (actual time=306.436..306.436 rows=94,105 loops=3)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,488kB
27. 169.951 169.951 ↑ 1.3 94,105 3 / 3

Parallel Seq Scan on employee_com ecom (cost=0.00..7,663.34 rows=118,034 width=50) (actual time=0.010..169.951 rows=94,105 loops=3)

28. 173.463 333.865 ↑ 1.3 91,465 3 / 3

Parallel Hash (cost=17,404.44..17,404.44 rows=114,644 width=204) (actual time=333.865..333.865 rows=91,465 loops=3)

  • Buckets: 32,768 Batches: 32 Memory Usage: 1,472kB
29. 160.402 160.402 ↑ 1.3 91,465 3 / 3

Parallel Seq Scan on employee_per eper (cost=0.00..17,404.44 rows=114,644 width=204) (actual time=0.008..160.402 rows=91,465 loops=3)

30. 0.010 0.031 ↑ 8.8 17 3 / 3

Hash (cost=11.50..11.50 rows=150 width=160) (actual time=0.031..0.031 rows=17 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
31. 0.021 0.021 ↑ 8.8 17 3 / 3

Seq Scan on employee_status_codes ecs (cost=0.00..11.50 rows=150 width=160) (actual time=0.017..0.021 rows=17 loops=3)

32. 7.783 11.163 ↓ 1.0 12,301 3 / 3

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 895kB
33. 3.380 3.380 ↓ 1.0 12,301 3 / 3

Seq Scan on dept_codes deptc (cost=0.00..297.96 rows=12,296 width=29) (actual time=0.013..3.380 rows=12,301 loops=3)

34. 0.221 0.354 ↑ 1.2 533 3 / 3

Hash (cost=13.17..13.17 rows=617 width=34) (actual time=0.350..0.354 rows=533 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
35. 0.133 0.133 ↑ 1.2 533 3 / 3

Seq Scan on division_codes divc (cost=0.00..13.17 rows=617 width=34) (actual time=0.011..0.133 rows=533 loops=3)

36. 16.922 27.830 ↑ 1.0 22,183 3 / 3

Hash (cost=527.48..527.48 rows=22,248 width=39) (actual time=27.830..27.830 rows=22,183 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,829kB
37. 10.908 10.908 ↑ 1.0 22,183 3 / 3

Seq Scan on proj_work_codes projc (cost=0.00..527.48 rows=22,248 width=39) (actual time=0.011..10.908 rows=22,183 loops=3)

38. 59.915 695.508 ↑ 1.2 49,310 3 / 3

Parallel Hash (cost=32,582.69..32,582.69 rows=60,169 width=105) (actual time=695.508..695.508 rows=49,310 loops=3)

  • Buckets: 32,768 Batches: 8 Memory Usage: 2,272kB
39. 635.593 635.593 ↑ 1.2 49,310 3 / 3

Parallel Seq Scan on batch_control bc (cost=0.00..32,582.69 rows=60,169 width=105) (actual time=3.245..635.593 rows=49,310 loops=3)

40. 13.894 25.226 ↑ 1.0 9,562 3 / 3

Hash (cost=315.01..315.01 rows=9,601 width=162) (actual time=25.226..25.226 rows=9,562 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 919kB
41. 11.332 11.332 ↑ 1.0 9,562 3 / 3

Seq Scan on location_codes loc (cost=0.00..315.01 rows=9,601 width=162) (actual time=0.019..11.332 rows=9,562 loops=3)

42. 0.018 0.038 ↑ 3.4 38 3 / 3

Hash (cost=11.30..11.30 rows=130 width=128) (actual time=0.038..0.038 rows=38 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
43. 0.020 0.020 ↑ 3.4 38 3 / 3

Seq Scan on employee_type_codes etc (cost=0.00..11.30 rows=130 width=128) (actual time=0.013..0.020 rows=38 loops=3)

44. 26.539 36.182 ↓ 1.0 22,724 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,149kB
45. 9.643 9.643 ↓ 1.0 22,724 3 / 3

Seq Scan on wc_class_codes wcc (cost=0.00..509.20 rows=22,720 width=52) (actual time=0.011..9.643 rows=22,724 loops=3)

46. 0.114 0.193 ↑ 1.1 243 3 / 3

Hash (cost=9.74..9.74 rows=274 width=42) (actual time=0.193..0.193 rows=243 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
47. 0.079 0.079 ↑ 1.1 243 3 / 3

Seq Scan on union_codes unionc (cost=0.00..9.74 rows=274 width=42) (actual time=0.013..0.079 rows=243 loops=3)

48. 1.999 15.373 ↑ 1.0 1,807 3 / 3

Hash (cost=357.09..357.09 rows=1,809 width=376) (actual time=15.373..15.373 rows=1,807 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 468kB
49. 13.374 13.374 ↑ 1.0 1,807 3 / 3

Seq Scan on client_master cm (cost=0.00..357.09 rows=1,809 width=376) (actual time=0.021..13.374 rows=1,807 loops=3)

50. 0.007 0.019 ↑ 52.5 4 3 / 3

Hash (cost=12.10..12.10 rows=210 width=128) (actual time=0.019..0.019 rows=4 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.012 0.012 ↑ 52.5 4 3 / 3

Seq Scan on client_federal_entities cfe (cost=0.00..12.10 rows=210 width=128) (actual time=0.011..0.012 rows=4 loops=3)

52. 0.053 0.123 ↓ 1.1 96 3 / 3

Hash (cost=10.87..10.87 rows=87 width=115) (actual time=0.123..0.123 rows=96 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
53. 0.070 0.070 ↓ 1.1 96 3 / 3

Seq Scan on employer_master em (cost=0.00..10.87 rows=87 width=115) (actual time=0.007..0.070 rows=96 loops=3)

54. 9.548 18.680 ↓ 1.0 18,036 3 / 3

Hash (cost=448.31..448.31 rows=18,031 width=98) (actual time=18.680..18.680 rows=18,036 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,518kB
55. 9.132 9.132 ↓ 1.0 18,036 3 / 3

Seq Scan on job_codes jobc (cost=0.00..448.31 rows=18,031 width=98) (actual time=0.008..9.132 rows=18,036 loops=3)

56. 0.023 0.061 ↑ 1.4 44 3 / 3

Hash (cost=10.60..10.60 rows=60 width=192) (actual time=0.060..0.061 rows=44 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
57. 0.038 0.038 ↑ 1.4 44 3 / 3

Seq Scan on plan_year_xref pyxref (cost=0.00..10.60 rows=60 width=192) (actual time=0.010..0.038 rows=44 loops=3)

58. 2.594 4.782 ↓ 1.0 3,824 3 / 3

Hash (cost=92.21..92.21 rows=3,821 width=136) (actual time=4.782..4.782 rows=3,824 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 349kB
59. 2.188 2.188 ↓ 1.0 3,824 3 / 3

Seq Scan on employee_401k_plans e4kp (cost=0.00..92.21 rows=3,821 width=136) (actual time=0.008..2.188 rows=3,824 loops=3)

60. 4,700.487 4,700.487 ↑ 1.0 1 613,107 / 3

Index Scan using pk_inf_hours_worked on inf_hours_worked_agg ihwa (cost=0.43..0.99 rows=1 width=25) (actual time=0.023..0.023 rows=1 loops=613,107)

  • Index Cond: ((epv.peo_id = peo_id) AND (epv.client_id = client_id) AND (epv.voucher_id = voucher_id))
61. 2.507 4.323 ↓ 1.0 5,002 1

Hash (cost=106.99..106.99 rows=4,999 width=78) (actual time=4.323..4.323 rows=5,002 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 444kB
62. 1.816 1.816 ↓ 1.0 5,002 1

Seq Scan on employee_401k_detail e4kd (cost=0.00..106.99 rows=4,999 width=78) (actual time=0.008..1.816 rows=5,002 loops=1)

63.          

SubPlan (for Hash Left Join)

64. 678.293 7,461.223 ↑ 1.0 1 678,293

Result (cost=8.31..8.32 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=678,293)

65.          

Initplan (for Result)

66. 678.293 6,782.930 ↓ 0.0 0 678,293

Limit (cost=0.28..8.31 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=678,293)

67. 6,104.637 6,104.637 ↓ 0.0 0 678,293

Index Only Scan Backward using pk_employee_401k_detail on employee_401k_detail e4kd1 (cost=0.28..8.31 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=678,293)

  • Index Cond: ((peo_id = epv.peo_id) AND (client_id = epv.client_id) AND (employee_id = epv.employee_id) AND (effective_date IS NOT NULL) AND (effective_date <= epv.pay_date))
  • Heap Fetches: 130,372
68. 1.637 6,230.697 ↓ 749.7 5,248 1

Hash (cost=575,281.16..575,281.16 rows=7 width=17) (actual time=6,230.697..6,230.697 rows=5,248 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 336kB
69. 0.655 6,229.060 ↓ 749.7 5,248 1

Subquery Scan on e4ldetail (cost=575,276.31..575,281.16 rows=7 width=17) (actual time=6,220.862..6,229.060 rows=5,248 loops=1)

70. 1.821 6,228.405 ↓ 749.7 5,248 1

Finalize GroupAggregate (cost=575,276.31..575,281.09 rows=7 width=57) (actual time=6,220.861..6,228.405 rows=5,248 loops=1)

  • Group Key: epv_1.peo_id, epv_1.client_id, epv_1.voucher_id
71. 21.028 6,226.584 ↓ 374.9 5,248 1

Gather Merge (cost=575,276.31..575,280.92 rows=14 width=25) (actual time=6,220.854..6,226.584 rows=5,248 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
72. 0.883 6,205.556 ↓ 249.9 1,749 3 / 3

Partial GroupAggregate (cost=574,276.29..574,279.28 rows=7 width=25) (actual time=6,204.509..6,205.556 rows=1,749 loops=3)

  • Group Key: epv_1.peo_id, epv_1.client_id, epv_1.voucher_id
73. 14.324 6,204.673 ↓ 6.7 1,944 3 / 3

Sort (cost=574,276.29..574,277.02 rows=292 width=25) (actual time=6,204.502..6,204.673 rows=1,944 loops=3)

  • Sort Key: epv_1.peo_id, epv_1.client_id, epv_1.voucher_id
  • Sort Method: quicksort Memory: 194kB
  • Worker 0: Sort Method: quicksort Memory: 169kB
  • Worker 1: Sort Method: quicksort Memory: 286kB
74. 1.653 6,190.349 ↓ 6.7 1,944 3 / 3

Nested Loop (cost=0.00..574,264.33 rows=292 width=25) (actual time=2,672.267..6,190.349 rows=1,944 loops=3)

75. 6,183.448 6,183.448 ↓ 583.0 1,749 3 / 3

Parallel Seq Scan on epv epv_1 (cost=0.00..574,258.33 rows=3 width=116) (actual time=2,672.224..6,183.448 rows=1,749 loops=3)

  • Filter: ((fourk_loans IS NOT NULL) AND (modified > '2020-05-01 00:00:00'::timestamp without time zone) AND (jsonb_typeof(fourk_loans) = 'array'::text))
  • Rows Removed by Filter: 1,775,133
76. 5.248 5.248 ↑ 100.0 1 5,248 / 3

Function Scan on jsonb_to_recordset fl (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=1 loops=5,248)

Planning time : 59.953 ms
Execution time : 45,571.682 ms