explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bzaa

Settings
# exclusive inclusive rows x rows loops node
1. 1,965.342 46,137.308 ↑ 10.8 94,577 1

Gather (cost=220,562.20..25,408,786.65 rows=1,019,496 width=2,698) (actual time=6,482.501..46,137.308 rows=94,577 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 70.358 7,381.513 ↑ 13.5 31,526 3 / 3

Hash Left Join (cost=219,562.20..1,092,807.05 rows=424,790 width=3,653) (actual time=6,418.871..7,381.513 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = cm.peo_id) AND (epv.client_id = cm.client_id))
3. 45.073 7,303.063 ↑ 13.5 31,526 3 / 3

Hash Left Join (cost=219,185.10..1,090,197.97 rows=424,790 width=2,573) (actual time=6,410.748..7,303.063 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = etc.peo_id) AND (epv.employee_type = etc.type_code))
4. 46.152 7,257.965 ↑ 13.5 31,526 3 / 3

Hash Left Join (cost=219,171.85..1,087,954.38 rows=424,790 width=2,509) (actual time=6,410.701..7,257.965 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = esc.peo_id) AND (epv.employee_status = esc.status_code))
5. 1,155.169 7,211.784 ↑ 13.5 31,526 3 / 3

Parallel Hash Left Join (cost=219,158.10..1,085,710.33 rows=424,790 width=2,445) (actual time=6,410.639..7,211.784 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = tav.peo_id) AND (epv.client_id = tav.client_id) AND (epv.voucher_id = tav.voucher_id))
6. 450.861 3,603.840 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=86,828.46..776,223.29 rows=262,020 width=2,447) (actual time=3,413.852..3,603.840 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = bc.peo_id) AND (epv.client_id = bc.client_id) AND (epv.batch_no = bc.batch_no))
7. 61.449 3,049.547 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=52,310.81..585,232.22 rows=262,020 width=2,370) (actual time=2,567.542..3,049.547 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = wcc.peo_id) AND (epv.home_wc_class = wcc.class_code) AND (epv.wc_state = wcc.state))
8. 57.004 2,963.727 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=51,404.01..582,261.98 rows=262,020 width=2,334) (actual time=2,542.987..2,963.727 rows=31,526 loops=3)

  • Hash Cond: ((ec.peo_id = pwc.peo_id) AND (ec.client_id = pwc.client_id) AND (ec.proj_workord = pwc.work_code))
9. 67.770 2,880.211 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=50,490.20..579,284.76 rows=262,020 width=2,341) (actual time=2,516.302..2,880.211 rows=31,526 loops=3)

  • Hash Cond: ((ec.peo_id = div.peo_id) AND (ec.client_id = div.client_id) AND (ec.division_code = div.division_code))
10. 66.039 2,812.105 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=50,469.65..577,200.78 rows=262,020 width=2,330) (actual time=2,515.944..2,812.105 rows=31,526 loops=3)

  • Hash Cond: ((ec.peo_id = dept.peo_id) AND (ec.client_id = dept.client_id) AND (ec.dept_code = dept.dept_code))
11. 499.041 2,732.894 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=49,956.51..574,624.21 rows=262,020 width=2,322) (actual time=2,502.728..2,732.894 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employee_id = epr.employee_id))
12. 483.384 2,002.513 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=27,706.64..409,688.72 rows=262,020 width=2,135) (actual time=1,744.633..2,002.513 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = ec.peo_id) AND (epv.client_id = ec.client_id) AND (epv.employee_id = ec.employee_id))
13. 409.936 1,263.106 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=16,491.20..260,830.87 rows=262,020 width=2,066) (actual time=1,048.685..1,263.106 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = ep.peo_id) AND (epv.client_id = ep.client_id) AND (epv.employee_id = ep.employee_id))
14. 75.089 684.688 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=1,879.86..110,178.12 rows=262,020 width=2,052) (actual time=55.192..684.688 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = lc.peo_id) AND (epv.client_id = lc.client_id) AND (epv.home_location = lc.location_code))
15. 54.726 595.362 ↑ 3.5 31,526 3 / 3

Hash Left Join (cost=1,399.04..108,827.28 rows=110,434 width=1,927) (actual time=40.855..595.362 rows=31,526 loops=3)

  • Hash Cond: ((vt.peo_id = tdc.peo_id) AND (vt.tax_deduction_code = tdc.tax_deduction_code))
16. 52.889 536.237 ↑ 3.0 31,526 3 / 3

Hash Left Join (cost=1,247.52..108,186.99 rows=93,032 width=1,891) (actual time=36.412..536.237 rows=31,526 loops=3)

  • Hash Cond: ((vt.peo_id = tac.peo_id) AND (vt.tax_auth_code = tac.authority_id))
17. 45.938 478.032 ↑ 2.7 31,526 3 / 3

Hash Left Join (cost=1,107.09..107,592.34 rows=86,460 width=1,864) (actual time=31.047..478.032 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = jc.peo_id) AND (epv.client_id = jc.client_id) AND (epv.home_job = jc.job_code))
18. 52.998 404.111 ↑ 2.7 31,526 3 / 3

Hash Left Join (cost=343.24..106,147.62 rows=86,460 width=1,776) (actual time=2.888..404.111 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = em.peo_id) AND (epv.employer_id = em.employer_id))
19. 50.568 350.978 ↑ 2.7 31,526 3 / 3

Hash Left Join (cost=331.07..105,680.59 rows=86,460 width=981) (actual time=2.736..350.978 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = cfe.peo_id) AND (epv.entity_id = cfe.entity_id))
20. 90.690 300.398 ↑ 2.7 31,526 3 / 3

Nested Loop (cost=315.82..105,211.42 rows=86,460 width=885) (actual time=2.704..300.398 rows=31,526 loops=3)

21. 21.117 21.488 ↓ 1.1 6,490 3 / 3

Parallel Bitmap Heap Scan on epv (cost=315.25..53,226.83 rows=6,131 width=774) (actual time=2.321..21.488 rows=6,490 loops=3)

  • Recheck Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2019-01-07'::date))
  • Filter: (status = 'PRNT'::text)
  • Heap Blocks: exact=948
22. 0.371 0.371 ↓ 1.3 19,471 1 / 3

Bitmap Index Scan on idx_epv_pay_date (cost=0.00..311.57 rows=14,714 width=0) (actual time=1.113..1.113 rows=19,471 loops=1)

  • Index Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2019-01-07'::date))
23. 188.220 188.220 ↓ 5.0 5 19,471 / 3

Index Scan using pk_inf_emptax_comtax_rollup on inf_emptax_comtax_rollup vt (cost=0.56..8.47 rows=1 width=111) (actual time=0.025..0.029 rows=5 loops=19,471)

  • Index Cond: ((peo_id = epv.peo_id) AND (client_id = epv.client_id) AND (voucher_number = epv.voucher_id))
24. 0.001 0.012 ↓ 0.0 0 3 / 3

Hash (cost=12.10..12.10 rows=210 width=160) (actual time=0.012..0.012 rows=0 loops=3)

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

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

26. 0.066 0.135 ↓ 1.1 92 3 / 3

Hash (cost=10.87..10.87 rows=87 width=808) (actual time=0.135..0.135 rows=92 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 74kB
27. 0.069 0.069 ↓ 1.1 92 3 / 3

Seq Scan on employer_master em (cost=0.00..10.87 rows=87 width=808) (actual time=0.010..0.069 rows=92 loops=3)

28. 14.786 27.983 ↑ 1.0 18,031 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1814kB
29. 13.197 13.197 ↑ 1.0 18,031 3 / 3

Seq Scan on job_codes jc (cost=0.00..448.31 rows=18,031 width=114) (actual time=0.009..13.197 rows=18,031 loops=3)

30. 2.885 5.316 ↑ 1.0 3,737 3 / 3

Hash (cost=84.37..84.37 rows=3,737 width=42) (actual time=5.315..5.316 rows=3,737 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 288kB
31. 2.431 2.431 ↑ 1.0 3,737 3 / 3

Seq Scan on tax_auth_codes tac (cost=0.00..84.37 rows=3,737 width=42) (actual time=0.014..2.431 rows=3,737 loops=3)

32. 2.897 4.399 ↑ 1.0 3,981 3 / 3

Hash (cost=91.81..91.81 rows=3,981 width=55) (actual time=4.399..4.399 rows=3,981 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 328kB
33. 1.502 1.502 ↑ 1.0 3,981 3 / 3

Seq Scan on tax_dedn_codes tdc (cost=0.00..91.81 rows=3,981 width=55) (actual time=0.012..1.502 rows=3,981 loops=3)

34. 7.645 14.237 ↑ 1.0 9,557 3 / 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 905kB
35. 6.592 6.592 ↑ 1.0 9,557 3 / 3

Seq Scan on location_codes lc (cost=0.00..313.57 rows=9,557 width=145) (actual time=0.011..6.592 rows=9,557 loops=3)

36. 61.624 168.482 ↑ 1.3 94,094 3 / 3

Parallel Hash (cost=11,631.85..11,631.85 rows=117,685 width=33) (actual time=168.482..168.482 rows=94,094 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2976kB
37. 106.858 106.858 ↑ 1.3 94,094 3 / 3

Parallel Seq Scan on employee_pay ep (cost=0.00..11,631.85 rows=117,685 width=33) (actual time=0.008..106.858 rows=94,094 loops=3)

38. 120.607 256.023 ↑ 1.3 94,101 3 / 3

Parallel Hash (cost=7,655.07..7,655.07 rows=117,907 width=76) (actual time=256.023..256.023 rows=94,101 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 2336kB
39. 135.416 135.416 ↑ 1.3 94,101 3 / 3

Parallel Seq Scan on employee_com ec (cost=0.00..7,655.07 rows=117,907 width=76) (actual time=0.010..135.416 rows=94,101 loops=3)

40. 110.927 231.340 ↑ 1.3 91,463 3 / 3

Parallel Hash (cost=17,396.95..17,396.95 rows=114,595 width=200) (actual time=231.339..231.340 rows=91,463 loops=3)

  • Buckets: 32768 Batches: 32 Memory Usage: 1472kB
41. 120.413 120.413 ↑ 1.3 91,463 3 / 3

Parallel Seq Scan on employee_per epr (cost=0.00..17,396.95 rows=114,595 width=200) (actual time=0.009..120.413 rows=91,463 loops=3)

42. 7.343 13.172 ↑ 1.0 12,296 3 / 3

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

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

Seq Scan on dept_codes dept (cost=0.00..297.96 rows=12,296 width=29) (actual time=0.016..5.829 rows=12,296 loops=3)

44. 0.201 0.336 ↑ 1.0 529 3 / 3

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

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

Seq Scan on division_codes div (cost=0.00..11.29 rows=529 width=34) (actual time=0.013..0.135 rows=529 loops=3)

46. 18.104 26.512 ↓ 1.0 22,176 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1828kB
47. 8.408 8.408 ↓ 1.0 22,176 3 / 3

Seq Scan on proj_work_codes pwc (cost=0.00..525.75 rows=22,175 width=39) (actual time=0.013..8.408 rows=22,176 loops=3)

48. 14.773 24.371 ↑ 1.0 22,720 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2148kB
49. 9.598 9.598 ↑ 1.0 22,720 3 / 3

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

50. 45.974 103.432 ↑ 1.2 49,295 3 / 3

Parallel Hash (cost=32,582.69..32,582.69 rows=60,169 width=96) (actual time=103.432..103.432 rows=49,295 loops=3)

  • Buckets: 32768 Batches: 8 Memory Usage: 2112kB
51. 57.458 57.458 ↑ 1.2 49,295 3 / 3

Parallel Seq Scan on batch_control bc (cost=0.00..32,582.69 rows=60,169 width=96) (actual time=0.008..57.458 rows=49,295 loops=3)

52. 1,508.541 2,452.775 ↑ 1.3 1,984,226 3 / 3

Parallel Hash (cost=71,952.05..71,952.05 rows=2,481,005 width=25) (actual time=2,452.775..2,452.775 rows=1,984,226 loops=3)

  • Buckets: 65536 Batches: 128 Memory Usage: 3360kB
53. 944.234 944.234 ↑ 1.3 1,984,226 3 / 3

Parallel Seq Scan on inf_hours_worked_agg tav (cost=0.00..71,952.05 rows=2,481,005 width=25) (actual time=0.008..944.234 rows=1,984,226 loops=3)

54. 0.010 0.029 ↑ 11.5 13 3 / 3

Hash (cost=11.50..11.50 rows=150 width=128) (actual time=0.028..0.029 rows=13 loops=3)

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

Seq Scan on employee_status_codes esc (cost=0.00..11.50 rows=150 width=128) (actual time=0.017..0.019 rows=13 loops=3)

56. 0.012 0.025 ↑ 3.8 34 3 / 3

Hash (cost=11.30..11.30 rows=130 width=128) (actual time=0.025..0.025 rows=34 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
57. 0.013 0.013 ↑ 3.8 34 3 / 3

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

58. 3.348 8.092 ↑ 1.0 1,804 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 1744kB
59. 4.744 4.744 ↑ 1.0 1,804 3 / 3

Seq Scan on client_master cm (cost=0.00..350.04 rows=1,804 width=1,105) (actual time=0.012..4.744 rows=1,804 loops=3)

60.          

SubPlan (for Gather)

61. 0.000 0.000 ↓ 0.0 0 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.000..0.000 rows=0 loops=94,577)

62. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

63. 0.000 0.000 ↓ 0.0 0 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.000..0.000 rows=0 loops=94,577)

64. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

65. 945.770 7,282.429 ↑ 2.6 39 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.063..0.077 rows=39 loops=94,577)

66. 6,336.659 6,336.659 ↑ 2.6 39 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.062..0.067 rows=39 loops=94,577)

67. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

68. 1,040.347 7,187.852 ↑ 2.6 39 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.062..0.076 rows=39 loops=94,577)

69. 6,147.505 6,147.505 ↑ 2.6 39 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.061..0.065 rows=39 loops=94,577)

70. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

71. 945.770 6,998.698 ↑ 2.6 39 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.060..0.074 rows=39 loops=94,577)

72. 6,052.928 6,052.928 ↑ 2.6 39 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.060..0.064 rows=39 loops=94,577)

73. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

74. 378.308 2,459.002 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.021..0.026 rows=15 loops=94,577)

75. 2,080.694 2,080.694 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.020..0.022 rows=15 loops=94,577)

76. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

77. 283.731 2,269.848 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.019..0.024 rows=15 loops=94,577)

78. 1,986.117 1,986.117 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.019..0.021 rows=15 loops=94,577)

79. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

80. 283.731 2,175.271 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.018..0.023 rows=15 loops=94,577)

81. 1,891.540 1,891.540 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.018..0.020 rows=15 loops=94,577)

82. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

83. 378.308 2,175.271 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.018..0.023 rows=15 loops=94,577)

84. 1,796.963 1,796.963 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.017..0.019 rows=15 loops=94,577)

85. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

86. 283.731 2,080.694 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.018..0.022 rows=15 loops=94,577)

87. 1,796.963 1,796.963 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.017..0.019 rows=15 loops=94,577)

88. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

89. 283.731 2,080.694 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.017..0.022 rows=15 loops=94,577)

90. 1,796.963 1,796.963 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.017..0.019 rows=15 loops=94,577)

91. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

92. 378.308 2,080.694 ↑ 6.7 15 94,577

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.017..0.022 rows=15 loops=94,577)

93. 1,702.386 1,702.386 ↑ 6.7 15 94,577

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.017..0.018 rows=15 loops=94,577)

94. 0.000 0.000 ↑ 1.0 1 94,577

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=94,577)

Planning time : 36.066 ms
Execution time : 46,150.128 ms