explain.depesz.com

A tool for finding a real cause for slow queries.

Result: wHn : NEW_P32_V_REPORT

options
# exclusive inclusive rows x rows loops node
1. 0.037 5184.983 ↑ 1.0 12 1
→

HashAggregate (cost=6443.37..6443.49 rows=12 width=584) (actual time=5184.978..5184.983 rows=12 loops=1)

2. 0.016 5184.946 ↑ 1.0 12 1
→

Hash Left Join (cost=6441.60..6442.77 rows=12 width=584) (actual time=5184.940..5184.946 rows=12 loops=1)

  • Hash Cond: (a.month = b.month)
3. 0.016 0.016 ↑ 1.0 12 1
→

Seq Scan on ft_sys_tax_weeks a (cost=0.00..1.12 rows=12 width=4) (actual time=0.015..0.016 rows=12 loops=1)

4. 0.006 5184.914 ↓ 2.0 2 1
→

Hash (cost=6441.59..6441.59 rows=1 width=584) (actual time=5184.914..5184.914 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
5. 0.000 5184.908 ↓ 2.0 2 1
→

Subquery Scan on b (cost=6441.57..6441.59 rows=1 width=584) (actual time=5184.907..5184.908 rows=2 loops=1)

6. 3.574 5184.908 ↓ 2.0 2 1
→

HashAggregate (cost=6441.57..6441.58 rows=1 width=404) (actual time=5184.907..5184.908 rows=2 loops=1)

7. 0.715 5181.334 ↓ 1002.0 1002 1
→

Nested Loop (cost=2543.65..6441.51 rows=1 width=404) (actual time=3921.088..5181.334 rows=1002 loops=1)

8. 13.767 5178.615 ↓ 1002.0 1002 1
→

Hash Join (cost=2543.65..6441.18 rows=1 width=412) (actual time=3921.073..5178.615 rows=1002 loops=1)

  • Hash Cond: ((fenvy.process_year)::text = (fer.er_cur_taxyear)::text)
9. 785.380 5164.820 ↓ 5614.8 101066 1
→

Nested Loop (cost=2535.37..6432.82 rows=18 width=417) (actual time=3921.037..5164.820 rows=101066 loops=1)

  • Join Filter: (((fenvy.process_period = fstw.month) AND ((fenvy.ee_pay_freq)::text = 'M'::text)) OR ((fenvy.process_period >= fstw.wk_start) AND (fenvy.process_period <= fstw.wk_end) AND ((fenvy.ee_pay_freq)::text = 'W'::text)) OR ((fenvy.process_period >= fstw.tw_start) AND (fenvy.process_period <= fstw.tw_end) AND ((fenvy.ee_pay_freq)::text = 'N'::text)) OR ((fenvy.process_period >= fstw.fw_start) AND (fenvy.process_period <= fstw.fw_end) AND ((fenvy.ee_pay_freq)::text = 'F'::text)))
10. 0.036 3921.499 ↓ 5.5 11 1
→

Hash Join (cost=2535.37..2615.71 rows=2 width=393) (actual time=3920.975..3921.499 rows=11 loops=1)

  • Hash Cond: (fstw.month = fstw.month)
11. 517.696 3921.453 ↑ 32.6 11 1
→

HashAggregate (cost=2534.17..2609.56 rows=359 width=357) (actual time=3920.957..3921.453 rows=11 loops=1)

12. 45.680 3403.757 ↓ 253.5 91020 1
→

Hash Join (cost=9.34..2483.91 rows=359 width=357) (actual time=447.846..3403.757 rows=91020 loops=1)

  • Hash Cond: (fst.taxyear_id = spb.spp_bir_taxyear_id)
13. 27.199 3358.063 ↓ 253.5 91020 1
→

Merge Join (cost=8.09..2477.73 rows=359 width=329) (actual time=447.825..3358.063 rows=91020 loops=1)

  • Merge Cond: (sap.sap_taxyear_id = fst.taxyear_id)
14. 0.015 0.023 ↓ 1.1 12 1
→

Sort (cost=1.30..1.33 rows=11 width=60) (actual time=0.022..0.023 rows=12 loops=1)

  • Sort Key: sap.sap_taxyear_id
  • Sort Method: quicksort Memory: 25kB
15. 0.008 0.008 ↓ 1.1 12 1
→

Seq Scan on ft_sys_sap sap (cost=0.00..1.11 rows=11 width=60) (actual time=0.006..0.008 rows=12 loops=1)

16. 69.389 3330.841 ↓ 253.5 91020 1
→

Materialize (cost=6.79..2471.89 rows=359 width=269) (actual time=447.799..3330.841 rows=91020 loops=1)

17. 33.039 3261.452 ↓ 253.5 91020 1
→

Merge Join (cost=6.79..2470.99 rows=359 width=269) (actual time=447.795..3261.452 rows=91020 loops=1)

  • Merge Cond: (spa.spp_adp_taxyear_id = fst.taxyear_id)
18. 0.008 0.018 ↓ 1.1 12 1
→

Sort (cost=1.30..1.33 rows=11 width=60) (actual time=0.017..0.018 rows=12 loops=1)

  • Sort Key: spa.spp_adp_taxyear_id
  • Sort Method: quicksort Memory: 25kB
19. 0.010 0.010 ↓ 1.1 12 1
→

Seq Scan on ft_sys_spp_adp spa (cost=0.00..1.11 rows=11 width=60) (actual time=0.007..0.010 rows=12 loops=1)

20. 76.116 3228.395 ↓ 253.5 91020 1
→

Materialize (cost=5.49..2465.15 rows=359 width=209) (actual time=447.772..3228.395 rows=91020 loops=1)

21. 53.616 3152.279 ↓ 253.5 91020 1
→

Nested Loop (cost=5.49..2464.25 rows=359 width=209) (actual time=447.769..3152.279 rows=91020 loops=1)

22. 93.304 3098.663 ↓ 253.5 91020 1
→

Nested Loop (cost=5.49..2451.49 rows=359 width=211) (actual time=447.747..3098.663 rows=91020 loops=1)

23. 1177.753 2803.223 ↓ 104.1 101068 1
→

Nested Loop (cost=5.49..2142.57 rows=971 width=211) (actual time=447.730..2803.223 rows=101068 loops=1)

  • Join Filter: (((fenvy.ee_ssp <= 0::numeric) AND (((fenvy.process_period = fstw.month) AND ((fenvy.ee_pay_freq)::text = 'M'::text)) OR ((fenvy.process_period >= fstw.wk_start) AND (fenvy.process_period <= fstw.wk_end) AND ((fenvy.ee_pay_freq)::text = 'W'::text)) OR ((fenvy.process_period >= fstw.tw_start) AND (fenvy.process_period <= fstw.tw_end) AND ((fenvy.ee_pay_freq)::text = 'N'::text)) OR ((fenvy.process_period >= fstw.fw_start) AND (fenvy.process_period <= fstw.fw_end) AND ((fenvy.ee_pay_freq)::text = 'F'::text)))) OR ((fenvy.ee_ssp > 0::numeric) AND (fenvy.process_date >= fstd.month_start_date) AND (fenvy.process_date <= fstd.month_end_date)))
24. 0.760 3.742 ↓ 144.0 144 1
→

Nested Loop (cost=5.49..42.94 rows=1 width=216) (actual time=0.137..3.742 rows=144 loops=1)

25. 0.654 2.262 ↓ 144.0 144 1
→

Nested Loop (cost=5.49..42.66 rows=1 width=198) (actual time=0.116..2.262 rows=144 loops=1)

  • Join Filter: (smp.smp_taxyear_id = fst.taxyear_id)
26. 0.036 0.036 ↓ 1.1 12 1
→

Index Scan using ft_sty_p on ft_sys_taxyear fst (cost=0.00..12.42 rows=11 width=38) (actual time=0.018..0.036 rows=12 loops=1)

27. 0.376 1.572 ↓ 144.0 144 12
→

Materialize (cost=5.49..30.08 rows=1 width=160) (actual time=0.008..0.131 rows=144 loops=12)

28. 0.230 1.196 ↓ 144.0 144 1
→

Nested Loop (cost=5.49..30.07 rows=1 width=160) (actual time=0.095..1.196 rows=144 loops=1)

  • Join Filter: (fstd.month = fstw.month)
29. 0.018 0.018 ↓ 12.0 12 1
→

Seq Scan on ft_sys_tax_weeks fstw (cost=0.00..1.18 rows=1 width=28) (actual time=0.002..0.018 rows=12 loops=1)

  • Filter: ((month >= 1) AND (month <= 12))
30. 0.468 0.948 ↓ 10.3 144 12
→

Hash Join (cost=5.49..28.72 rows=14 width=136) (actual time=0.009..0.079 rows=144 loops=12)

  • Hash Cond: (aspb.aspp_bir_taxyear_id = smp.smp_taxyear_id)
31. 0.369 0.468 ↑ 1.8 144 12
→

Hash Join (cost=4.24..26.38 rows=252 width=76) (actual time=0.007..0.039 rows=144 loops=12)

  • Hash Cond: (aspb.aspp_bir_taxyear_id = fstd.taxyear_id)
32. 0.048 0.048 ↑ 29.2 12 12
→

Seq Scan on ft_sys_aspp_bir aspb (cost=0.00..13.50 rows=350 width=60) (actual time=0.002..0.004 rows=12 loops=12)

33. 0.025 0.051 ↑ 1.0 144 1
→

Hash (cost=2.44..2.44 rows=144 width=16) (actual time=0.051..0.051 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
34. 0.026 0.026 ↑ 1.0 144 1
→

Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.44 rows=144 width=16) (actual time=0.010..0.026 rows=144 loops=1)

35. 0.003 0.012 ↓ 1.1 12 1
→

Hash (cost=1.11..1.11 rows=11 width=60) (actual time=0.012..0.012 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
36. 0.009 0.009 ↓ 1.1 12 1
→

Seq Scan on ft_sys_smp smp (cost=0.00..1.11 rows=11 width=60) (actual time=0.007..0.009 rows=12 loops=1)

37. 0.720 0.720 ↑ 1.0 1 144
→

Index Scan using ft_sssp_u1 on ft_sys_ssp ssp (cost=0.00..0.27 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=144)

  • Index Cond: (ssp_taxyear_id = fst.taxyear_id)
38. 1621.728 1621.728 ↑ 1.3 8422 144
→

Index Scan using ft_enetvaly_u on ft_ee_net_values_ytd fenvy (cost=0.00..1538.18 rows=11229 width=71) (actual time=6.147..11.262 rows=8422 loops=144)

  • Index Cond: ((ee_er_id = 100) AND ((process_year)::text = (fst.taxyear)::text))
39. 202.136 202.136 ↑ 1.0 1 101068
→

Index Scan using ft_ee_p on ft_employees fee (cost=0.00..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=101068)

  • Index Cond: (ee_id = fenvy.ee_id)
  • Filter: (((ee_sys_status)::text <> 'D'::text) AND (ee_er_id = 100))
40. 0.000 0.000 ↑ 1.0 1 91020
→

Materialize (cost=0.00..8.27 rows=1 width=6) (actual time=0.000..0.000 rows=1 loops=91020)

41. 0.018 0.018 ↑ 1.0 1 1
→

Index Scan using ft_eropt_p on ft_er_options feo (cost=0.00..8.27 rows=1 width=6) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (er_id = 100)
42. 0.004 0.014 ↓ 1.1 12 1
→

Hash (cost=1.11..1.11 rows=11 width=60) (actual time=0.014..0.014 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
43. 0.010 0.010 ↓ 1.1 12 1
→

Seq Scan on ft_sys_spp_bir spb (cost=0.00..1.11 rows=11 width=60) (actual time=0.008..0.010 rows=12 loops=1)

44. 0.002 0.010 ↓ 12.0 12 1
→

Hash (cost=1.18..1.18 rows=1 width=28) (actual time=0.010..0.010 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.008 0.008 ↓ 12.0 12 1
→

Seq Scan on ft_sys_tax_weeks fstw (cost=0.00..1.18 rows=1 width=28) (actual time=0.003..0.008 rows=12 loops=1)

  • Filter: ((month >= 1) AND (month <= 12))
46. 457.941 457.941 ↓ 7.3 82054 11
→

Index Scan using ft_enetvaly_u on ft_ee_net_values_ytd fenvy (cost=0.00..1459.40 rows=11229 width=58) (actual time=0.051..41.631 rows=82054 loops=11)

  • Index Cond: ((ee_er_id = 100) AND ((process_year)::text = (fenvy.process_year)::text))
47. 0.006 0.028 ↑ 1.0 1 1
→

Hash (cost=8.27..8.27 rows=1 width=13) (actual time=0.028..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
48. 0.022 0.022 ↑ 1.0 1 1
→

Index Scan using ft_er_u1 on ft_employers fer (cost=0.00..8.27 rows=1 width=13) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (er_id = 100)
49. 2.004 2.004 ↑ 1.0 1 1002
→

Index Scan using ft_ee_p on ft_employees fee (cost=0.00..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1002)

  • Index Cond: (ee_id = fenvy.ee_id)
  • Filter: (((ee_sys_status)::text <> 'D'::text) AND (ee_er_id = 100))