explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6lVI

Settings
# exclusive inclusive rows x rows loops node
1. 2,757.207 34,229.415 ↑ 1.0 613,107 1

Gather (cost=89,188.01..1,407,130.47 rows=621,021 width=3,170) (actual time=19,789.519..34,229.415 rows=613,107 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 1,269.494 31,472.208 ↑ 1.3 204,369 3 / 3

Nested Loop Left Join (cost=88,188.01..1,344,028.37 rows=258,759 width=3,170) (actual time=19,747.489..31,472.208 rows=204,369 loops=3)

3. 549.820 25,910.965 ↑ 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=19,747.363..25,910.965 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))
4. 479.371 25,353.749 ↑ 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=19,739.925..25,353.749 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = pyxref.peo_id) AND (epv.retire_plan = pyxref.plan_id))
5. 544.134 24,874.327 ↑ 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=19,739.840..24,874.327 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))
6. 525.977 24,311.886 ↑ 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=19,721.393..24,311.886 rows=204,369 loops=3)

  • Hash Cond: ((epv.employer_id = em.employer_id) AND (epv.peo_id = em.peo_id))
7. 403.726 23,785.820 ↑ 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=19,721.274..23,785.820 rows=204,369 loops=3)

  • Hash Cond: ((cm.entity_id = cfe.entity_id) AND (cm.peo_id = cfe.peo_id))
8. 636.958 23,382.076 ↑ 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=19,721.222..23,382.076 rows=204,369 loops=3)

  • Hash Cond: ((epv.client_id = cm.client_id) AND (epv.peo_id = cm.peo_id))
9. 372.230 22,729.506 ↑ 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=19,705.578..22,729.506 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))
10. 499.944 22,357.075 ↑ 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=19,705.339..22,357.075 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))
11. 449.277 21,819.859 ↑ 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=19,667.895..21,819.859 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = etc.peo_id) AND (epv.employee_type = etc.type_code))
12. 512.862 21,370.543 ↑ 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=19,667.823..21,370.543 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))
13. 2,324.983 20,805.682 ↑ 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=19,615.786..20,805.682 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))
14. 384.897 18,359.437 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=50,789.24..914,756.48 rows=258,759 width=1,790) (actual time=15,665.229..18,359.437 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))
15. 362.979 17,948.469 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=49,872.42..911,801.94 rows=258,759 width=1,775) (actual time=15,639.006..17,948.469 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))
16. 398.041 17,585.138 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=49,848.45..909,740.24 rows=258,759 width=1,760) (actual time=15,638.634..17,585.138 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))
17. 434.448 17,173.656 ↑ 1.3 204,369 3 / 3

Hash Left Join (cost=49,335.31..907,189.38 rows=258,759 width=1,748) (actual time=15,625.162..17,173.656 rows=204,369 loops=3)

  • Hash Cond: ((epv.peo_id = ecs.peo_id) AND (epv.employee_status = ecs.status_code))
18. 2,157.696 16,739.178 ↑ 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=15,625.106..16,739.178 rows=204,369 loops=3)

  • Hash Cond: ((epv.employee_id = eper.employee_id) AND (epv.peo_id = eper.peo_id))
19. 2,127.278 14,320.481 ↑ 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=13,174.017..14,320.481 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))
20. 2,568.694 11,944.638 ↑ 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=10,934.250..11,944.638 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))
21. 9,208.751 9,208.751 ↑ 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,596.930..9,208.751 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
22. 65.430 167.193 ↑ 1.4 94,095 3 / 3

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,880kB
23. 101.763 101.763 ↑ 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.008..101.763 rows=94,095 loops=3)

24. 81.237 248.565 ↑ 1.3 94,105 3 / 3

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,456kB
25. 167.328 167.328 ↑ 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.012..167.328 rows=94,105 loops=3)

26. 119.461 261.001 ↑ 1.3 91,465 3 / 3

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

  • Buckets: 32,768 Batches: 32 Memory Usage: 1,472kB
27. 141.540 141.540 ↑ 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.009..141.540 rows=91,465 loops=3)

28. 0.009 0.030 ↑ 8.8 17 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
29. 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.016..0.021 rows=17 loops=3)

30. 6.033 13.441 ↓ 1.0 12,301 3 / 3

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 895kB
31. 7.408 7.408 ↓ 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.011..7.408 rows=12,301 loops=3)

32. 0.215 0.352 ↑ 1.2 533 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
33. 0.137 0.137 ↑ 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.137 rows=533 loops=3)

34. 20.278 26.071 ↑ 1.0 22,183 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,829kB
35. 5.793 5.793 ↑ 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..5.793 rows=22,183 loops=3)

36. 40.887 121.262 ↑ 1.2 49,310 3 / 3

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

  • Buckets: 32,768 Batches: 8 Memory Usage: 2,272kB
37. 80.375 80.375 ↑ 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=0.007..80.375 rows=49,310 loops=3)

38. 30.415 51.999 ↑ 1.0 9,562 3 / 3

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 919kB
39. 21.584 21.584 ↑ 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.016..21.584 rows=9,562 loops=3)

40. 0.017 0.039 ↑ 3.4 38 3 / 3

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

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

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

42. 24.154 37.272 ↓ 1.0 22,724 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,149kB
43. 13.118 13.118 ↓ 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.010..13.118 rows=22,724 loops=3)

44. 0.120 0.201 ↑ 1.1 243 3 / 3

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

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

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

46. 2.637 15.612 ↑ 1.0 1,807 3 / 3

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 468kB
47. 12.975 12.975 ↑ 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.591..12.975 rows=1,807 loops=3)

48. 0.005 0.018 ↑ 52.5 4 3 / 3

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

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

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

50. 0.037 0.089 ↓ 1.1 96 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
51. 0.052 0.052 ↓ 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.052 rows=96 loops=3)

52. 9.523 18.307 ↓ 1.0 18,036 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,518kB
53. 8.784 8.784 ↓ 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.007..8.784 rows=18,036 loops=3)

54. 0.018 0.051 ↑ 1.4 44 3 / 3

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

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

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

56. 4.279 7.396 ↓ 1.0 3,824 3 / 3

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 349kB
57. 3.117 3.117 ↓ 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.007..3.117 rows=3,824 loops=3)

58. 4,291.749 4,291.749 ↑ 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.021..0.021 rows=1 loops=613,107)

  • Index Cond: ((epv.peo_id = peo_id) AND (epv.client_id = client_id) AND (epv.voucher_id = voucher_id))
Planning time : 39.688 ms
Execution time : 34,295.505 ms