explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nqa1

Settings
# exclusive inclusive rows x rows loops node
1. 3,416.790 65,096.099 ↑ 11.9 737,463 1

Gather (cost=220,248.70..6,219,297.42 rows=8,766,205 width=2,314) (actual time=57,307.998..65,096.099 rows=737,463 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 876.280 61,679.309 ↑ 14.9 245,821 3 / 3

Hash Left Join (cost=219,248.70..5,341,676.92 rows=3,652,585 width=2,314) (actual time=57,155.511..61,679.309 rows=245,821 loops=3)

  • Hash Cond: ((epv.peo_id = cm.peo_id) AND (epv.client_id = cm.client_id))
3. 419.703 60,748.986 ↑ 14.9 245,821 3 / 3

Hash Left Join (cost=218,871.60..5,285,582.06 rows=3,652,585 width=1,848) (actual time=57,097.095..60,748.986 rows=245,821 loops=3)

  • Hash Cond: ((epv.peo_id = etc.peo_id) AND (epv.employee_type = etc.type_code))
4. 420.640 60,328.412 ↑ 14.9 245,821 3 / 3

Hash Left Join (cost=218,858.35..5,266,391.04 rows=3,652,585 width=1,784) (actual time=57,093.145..60,328.412 rows=245,821 loops=3)

  • Hash Cond: ((epv.peo_id = esc.peo_id) AND (epv.employee_status = esc.status_code))
5. 4,252.204 59,906.277 ↑ 14.9 245,821 3 / 3

Parallel Hash Left Join (cost=218,844.60..5,247,199.94 rows=3,652,585 width=1,720) (actual time=57,088.539..59,906.277 rows=245,821 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. 9,384.792 51,164.257 ↑ 9.2 245,821 3 / 3

Parallel Hash Left Join (cost=86,514.97..4,116,481.92 rows=2,253,000 width=1,722) (actual time=43,584.863..51,164.257 rows=245,821 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. 505.305 39,985.326 ↑ 9.2 245,821 3 / 3

Hash Left Join (cost=51,997.32..3,143,657.78 rows=2,253,000 width=1,645) (actual time=33,351.906..39,985.326 rows=245,821 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. 487.751 39,432.163 ↑ 9.2 245,821 3 / 3

Hash Left Join (cost=51,090.52..3,125,008.42 rows=2,253,000 width=1,609) (actual time=33,297.859..39,432.163 rows=245,821 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. 455.529 38,828.133 ↑ 9.2 245,821 3 / 3

Hash Left Join (cost=50,176.70..3,106,352.22 rows=2,253,000 width=1,616) (actual time=33,180.609..38,828.133 rows=245,821 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. 550.597 38,356.656 ↑ 9.2 245,821 3 / 3

Hash Left Join (cost=50,156.16..3,088,589.07 rows=2,253,000 width=1,605) (actual time=33,163.895..38,356.656 rows=245,821 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. 5,902.846 37,709.902 ↑ 9.2 245,821 3 / 3

Parallel Hash Left Join (cost=49,643.02..3,070,333.37 rows=2,253,000 width=1,597) (actual time=33,066.534..37,709.902 rows=245,821 loops=3)

  • Hash Cond: ((epv.peo_id = epr.peo_id) AND (epv.employee_id = epr.employee_id))
12. 7,945.264 30,756.545 ↑ 9.2 245,821 3 / 3

Parallel Hash Left Join (cost=27,393.15..2,241,049.19 rows=2,253,000 width=1,410) (actual time=24,207.108..30,756.545 rows=245,821 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. 4,754.878 22,326.951 ↑ 9.2 245,821 3 / 3

Parallel Hash Left Join (cost=16,177.71..1,458,126.36 rows=2,253,000 width=1,341) (actual time=18,354.021..22,326.951 rows=245,821 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. 263.346 16,870.034 ↑ 9.2 245,821 3 / 3

Hash Left Join (cost=1,565.04..681,183.31 rows=2,253,000 width=1,327) (actual time=272.092..16,870.034 rows=245,821 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. 249.663 16,535.028 ↑ 3.9 245,821 3 / 3

Hash Left Join (cost=1,084.22..673,221.46 rows=949,571 width=1,202) (actual time=200.324..16,535.028 rows=245,821 loops=3)

  • Hash Cond: ((vt.peo_id = tdc.peo_id) AND (vt.tax_deduction_code = tdc.tax_deduction_code))
16. 237.107 16,251.583 ↑ 3.3 245,821 3 / 3

Hash Left Join (cost=932.70..668,867.33 rows=799,938 width=1,166) (actual time=166.497..16,251.583 rows=245,821 loops=3)

  • Hash Cond: ((vt.peo_id = tac.peo_id) AND (vt.tax_auth_code = tac.authority_id))
17. 288.329 16,001.679 ↑ 3.0 245,821 3 / 3

Nested Loop (cost=792.28..664,821.25 rows=743,435 width=1,139) (actual time=153.646..16,001.679 rows=245,821 loops=3)

18. 54.330 2,249.353 ↑ 1.2 45,641 3 / 3

Hash Left Join (cost=791.71..245,501.95 rows=52,716 width=1,028) (actual time=121.484..2,249.353 rows=45,641 loops=3)

  • Hash Cond: ((epv.peo_id = jc.peo_id) AND (epv.client_id = jc.client_id) AND (epv.home_job = jc.job_code))
19. 43.040 2,090.990 ↑ 1.2 45,641 3 / 3

Hash Left Join (cost=27.86..244,322.96 rows=52,716 width=940) (actual time=17.252..2,090.990 rows=45,641 loops=3)

  • Hash Cond: ((epv.peo_id = em.peo_id) AND (epv.employer_id = em.employer_id))
20. 71.724 2,040.119 ↑ 1.2 45,641 3 / 3

Hash Left Join (cost=15.68..244,033.45 rows=52,716 width=838) (actual time=9.398..2,040.119 rows=45,641 loops=3)

  • Hash Cond: ((epv.peo_id = cfe.peo_id) AND (epv.entity_id = cfe.entity_id))
21. 1,968.387 1,968.387 ↑ 1.2 45,641 3 / 3

Parallel Index Scan using idx_epv_pay_date on epv (cost=0.43..243,741.44 rows=52,716 width=774) (actual time=9.360..1,968.387 rows=45,641 loops=3)

  • Index Cond: ((pay_date >= '2019-01-01'::date) AND (pay_date < '2019-03-01'::date))
  • Filter: (status = 'PRNT'::text)
22. 0.001 0.008 ↓ 0.0 0 3 / 3

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

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

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

24. 0.050 7.831 ↓ 1.1 92 3 / 3

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

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

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

26. 23.008 104.033 ↑ 1.0 18,031 3 / 3

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

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

28. 13,463.997 13,463.997 ↓ 5.0 5 136,922 / 3

Index Scan using pk_inf_emptax_comtax_rollup on inf_emptax_comtax_rollup vt (cost=0.56..7.94 rows=1 width=111) (actual time=0.085..0.295 rows=5 loops=136,922)

  • Index Cond: ((peo_id = epv.peo_id) AND (client_id = epv.client_id) AND (voucher_number = epv.voucher_id))
29. 1.383 12.797 ↑ 1.0 3,737 3 / 3

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

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

31. 1.598 33.782 ↑ 1.0 3,981 3 / 3

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

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

33. 5.076 71.660 ↑ 1.0 9,557 3 / 3

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

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

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

35. 162.636 702.039 ↑ 1.3 94,094 3 / 3

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2976kB
36. 539.403 539.403 ↑ 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.893..539.403 rows=94,094 loops=3)

37. 154.008 484.330 ↑ 1.3 94,101 3 / 3

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

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

39. 207.896 1,050.511 ↑ 1.3 91,463 3 / 3

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

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

41. 7.821 96.157 ↑ 1.0 12,296 3 / 3

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

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

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

43. 0.220 15.948 ↑ 1.0 529 3 / 3

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

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

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

45. 13.770 116.279 ↓ 1.0 22,176 3 / 3

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

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

47. 19.161 47.858 ↑ 1.0 22,720 3 / 3

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

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

49. 190.942 1,794.139 ↑ 1.2 49,296 3 / 3

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

  • Buckets: 32768 Batches: 8 Memory Usage: 2112kB
50. 1,603.197 1,603.197 ↑ 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=1.269..1,603.197 rows=49,296 loops=3)

51. 2,184.075 4,489.816 ↑ 1.3 1,984,226 3 / 3

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

  • Buckets: 65536 Batches: 128 Memory Usage: 3360kB
52. 2,305.741 2,305.741 ↑ 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=12.480..2,305.741 rows=1,984,226 loops=3)

53. 0.017 1.495 ↑ 11.5 13 3 / 3

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

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

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

55. 0.020 0.871 ↑ 3.8 34 3 / 3

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

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

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

57. 2.458 54.043 ↑ 1.0 1,804 3 / 3

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

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

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

Planning time : 63.071 ms
Execution time : 65,219.096 ms