explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mDxt

Settings
# exclusive inclusive rows x rows loops node
1. 876.607 8,288.449 ↑ 10.8 94,577 1

Gather (cost=220,563.52..968,179.88 rows=1,019,496 width=2,314) (actual time=6,322.621..8,288.449 rows=94,577 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 120.922 7,411.842 ↑ 13.5 31,526 3 / 3

Hash Left Join (cost=219,563.52..865,230.28 rows=424,790 width=2,314) (actual time=6,281.928..7,411.842 rows=31,526 loops=3)

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

Hash Left Join (cost=219,186.42..858,373.29 rows=424,790 width=1,848) (actual time=6,275.001..7,284.035 rows=31,526 loops=3)

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

Hash Left Join (cost=219,173.17..856,129.70 rows=424,790 width=1,784) (actual time=6,274.945..7,228.428 rows=31,526 loops=3)

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

Parallel Hash Left Join (cost=219,159.42..853,885.65 rows=424,790 width=1,720) (actual time=6,274.881..7,173.690 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. 410.400 3,550.444 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=86,829.78..590,456.61 rows=262,020 width=1,722) (actual time=3,370.222..3,550.444 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. 55.981 3,030.630 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=52,312.14..446,035.54 rows=262,020 width=1,645) (actual time=2,543.330..3,030.630 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. 83.191 2,948.752 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=51,405.34..443,065.30 rows=262,020 width=1,609) (actual time=2,517.245..2,948.752 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. 58.852 2,840.654 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=50,491.52..440,088.08 rows=262,020 width=1,616) (actual time=2,492.210..2,840.654 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. 64.409 2,781.466 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=50,470.98..438,004.10 rows=262,020 width=1,605) (actual time=2,491.852..2,781.466 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. 487.823 2,703.815 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=49,957.84..435,427.53 rows=262,020 width=1,597) (actual time=2,478.567..2,703.815 rows=31,526 loops=3)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employee_id = epr.employee_id))
12. 390.536 1,845.377 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=27,707.96..316,550.04 rows=262,020 width=1,410) (actual time=1,647.068..1,845.377 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. 370.166 1,260.174 ↑ 8.3 31,526 3 / 3

Parallel Hash Left Join (cost=16,492.53..214,262.19 rows=262,020 width=1,341) (actual time=1,104.922..1,260.174 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. 79.983 715.132 ↑ 8.3 31,526 3 / 3

Hash Left Join (cost=1,879.86..110,178.12 rows=262,020 width=1,327) (actual time=57.336..715.132 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. 62.057 617.465 ↑ 3.5 31,526 3 / 3

Hash Left Join (cost=1,399.04..108,827.28 rows=110,434 width=1,202) (actual time=39.548..617.465 rows=31,526 loops=3)

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

Hash Left Join (cost=1,247.52..108,186.99 rows=93,032 width=1,166) (actual time=36.891..552.793 rows=31,526 loops=3)

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

Hash Left Join (cost=1,107.09..107,592.34 rows=86,460 width=1,139) (actual time=31.058..487.995 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. 60.714 397.545 ↑ 2.7 31,526 3 / 3

Hash Left Join (cost=343.24..106,147.62 rows=86,460 width=1,051) (actual time=2.615..397.545 rows=31,526 loops=3)

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

Hash Left Join (cost=331.07..105,680.59 rows=86,460 width=949) (actual time=2.484..336.721 rows=31,526 loops=3)

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

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

21. 13.569 14.042 ↓ 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.113..14.042 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=1031
22. 0.473 0.473 ↓ 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.419..1.419 rows=19,471 loops=1)

  • Index Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2019-01-07'::date))
23. 227.162 227.162 ↓ 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.031..0.035 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.011 ↓ 0.0 0 3 / 3

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

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

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

26. 0.045 0.110 ↓ 1.1 92 3 / 3

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

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

Seq Scan on employer_master em (cost=0.00..10.87 rows=87 width=115) (actual time=0.009..0.065 rows=92 loops=3)

28. 15.543 28.307 ↑ 1.0 18,031 3 / 3

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

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

30. 1.569 5.783 ↑ 1.0 3,737 3 / 3

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

  • Buckets: 4096 Batches: 1 Memory Usage: 288kB
31. 4.214 4.214 ↑ 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.015..4.214 rows=3,737 loops=3)

32. 1.507 2.615 ↑ 1.0 3,981 3 / 3

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

  • Buckets: 4096 Batches: 1 Memory Usage: 328kB
33. 1.108 1.108 ↑ 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.016..1.108 rows=3,981 loops=3)

34. 5.891 17.684 ↑ 1.0 9,557 3 / 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 905kB
35. 11.793 11.793 ↑ 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.013..11.793 rows=9,557 loops=3)

36. 66.107 174.876 ↑ 1.3 94,094 3 / 3

Parallel Hash (cost=11,632.97..11,632.97 rows=117,697 width=33) (actual time=174.875..174.876 rows=94,094 loops=3)

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

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

38. 92.952 194.667 ↑ 1.3 94,101 3 / 3

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

  • Buckets: 65536 Batches: 16 Memory Usage: 2336kB
39. 101.715 101.715 ↑ 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.012..101.715 rows=94,101 loops=3)

40. 201.091 370.615 ↑ 1.3 91,463 3 / 3

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

  • Buckets: 32768 Batches: 32 Memory Usage: 1472kB
41. 169.524 169.524 ↑ 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..169.524 rows=91,463 loops=3)

42. 10.098 13.242 ↑ 1.0 12,296 3 / 3

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

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

44. 0.205 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.131 0.131 ↑ 1.0 529 3 / 3

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

46. 16.552 24.907 ↓ 1.0 22,176 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1828kB
47. 8.355 8.355 ↓ 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.011..8.355 rows=22,176 loops=3)

48. 16.362 25.897 ↑ 1.0 22,720 3 / 3

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

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

50. 47.720 109.414 ↑ 1.2 49,296 3 / 3

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

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

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

52. 1,431.931 2,402.893 ↑ 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,402.893..2,402.893 rows=1,984,226 loops=3)

  • Buckets: 65536 Batches: 128 Memory Usage: 3360kB
53. 970.962 970.962 ↑ 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.010..970.962 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.029..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.016..0.019 rows=13 loops=3)

56. 0.016 0.031 ↑ 3.8 34 3 / 3

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

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

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

58. 1.981 6.885 ↑ 1.0 1,804 3 / 3

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

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

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

Planning time : 33.714 ms
Execution time : 8,299.136 ms