explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 0nj : NEW_P32_V_REPORT

options
# exclusive inclusive rows x rows loops node
1. 487.641 4610.813 ↑ 34.3 9 1
→

HashAggregate (cost=15949.56..16014.45 rows=309 width=357) (actual time=4610.682..4610.813 rows=9 loops=1)

2. 78.587 4123.172 ↓ 291.3 90018 1
→

Nested Loop (cost=15570.68..15906.30 rows=309 width=357) (actual time=3483.773..4123.172 rows=90018 loops=1)

3. 47.758 3844.453 ↓ 119.7 100066 1
→

Hash Join (cost=15570.68..15638.68 rows=836 width=361) (actual time=3483.758..3844.453 rows=100066 loops=1)

  • Hash Cond: (fst.taxyear_id = spa.spp_adp_taxyear_id)
4. 54.983 3796.680 ↓ 119.7 100066 1
→

Nested Loop (cost=15569.44..15625.94 rows=836 width=333) (actual time=3483.733..3796.680 rows=100066 loops=1)

5. 25.964 3741.697 ↓ 119.7 100066 1
→

Merge Join (cost=15569.44..15607.22 rows=836 width=331) (actual time=3483.705..3741.697 rows=100066 loops=1)

  • Merge Cond: (ssp.ssp_taxyear_id = fst.taxyear_id)
6. 0.014 0.023 ↓ 1.1 12 1
→

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

  • Sort Key: ssp.ssp_taxyear_id
  • Sort Method: quicksort Memory: 25kB
7. 0.009 0.009 ↓ 1.1 12 1
→

Seq Scan on ft_sys_ssp ssp (cost=0.00..1.11 rows=11 width=18) (actual time=0.006..0.009 rows=12 loops=1)

8. 73.694 3715.710 ↓ 119.7 100066 1
→

Materialize (cost=15568.14..15595.42 rows=836 width=313) (actual time=3483.678..3715.710 rows=100066 loops=1)

9. 23.948 3642.016 ↓ 119.7 100066 1
→

Merge Join (cost=15568.14..15593.33 rows=836 width=313) (actual time=3483.674..3642.016 rows=100066 loops=1)

  • Merge Cond: (smp.smp_taxyear_id = fst.taxyear_id)
10. 0.010 0.016 ↓ 1.1 12 1
→

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

  • Sort Key: smp.smp_taxyear_id
  • Sort Method: quicksort Memory: 25kB
11. 0.006 0.006 ↓ 1.1 12 1
→

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

12. 74.524 3618.052 ↓ 119.7 100066 1
→

Materialize (cost=15566.83..15581.52 rows=836 width=253) (actual time=3483.654..3618.052 rows=100066 loops=1)

13. 47.866 3543.528 ↓ 119.7 100066 1
→

Merge Join (cost=15566.83..15579.43 rows=836 width=253) (actual time=3483.650..3543.528 rows=100066 loops=1)

  • Merge Cond: (spb.spp_bir_taxyear_id = fst.taxyear_id)
14. 0.006 0.012 ↓ 1.1 12 1
→

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

  • Sort Key: spb.spp_bir_taxyear_id
  • Sort Method: quicksort Memory: 25kB
15. 0.006 0.006 ↓ 1.1 12 1
→

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

16. 76.578 3495.650 ↓ 119.7 100066 1
→

Sort (cost=15565.53..15567.62 rows=836 width=193) (actual time=3483.629..3495.650 rows=100066 loops=1)

  • Sort Key: fst.taxyear_id
  • Sort Method: quicksort Memory: 29653kB
17. 578.063 3419.072 ↓ 119.7 100066 1
→

Hash Join (cost=6.73..15524.96 rows=836 width=193) (actual time=242.496..3419.072 rows=100066 loops=1)

  • Hash Cond: (fstd.month = fstw.month)
  • 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)))
18. 285.792 2840.997 ↓ 12.7 1200792 1
→

Merge Join (cost=5.54..14792.47 rows=94870 width=211) (actual time=7.574..2840.997 rows=1200792 loops=1)

  • Merge Cond: (sap.sap_taxyear_id = fst.taxyear_id)
19. 0.005 0.012 ↓ 1.1 12 1
→

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

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

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

21. 817.613 2555.193 ↓ 12.7 1200792 1
→

Materialize (cost=4.24..13605.24 rows=94870 width=151) (actual time=7.556..2555.193 rows=1200792 loops=1)

22. 284.668 1737.580 ↓ 12.7 1200792 1
→

Nested Loop (cost=4.24..13368.06 rows=94870 width=151) (actual time=7.551..1737.580 rows=1200792 loops=1)

23. 0.022 0.132 ↓ 12.0 12 1
→

Nested Loop (cost=4.24..41.98 rows=1 width=114) (actual time=0.108..0.132 rows=12 loops=1)

  • Join Filter: (aspb.aspp_bir_taxyear_id = fst.taxyear_id)
24. 0.013 0.013 ↑ 1.0 1 1
→

Index Scan using ft_sty_p on ft_sys_taxyear fst (cost=0.00..12.44 rows=1 width=38) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: ((taxyear)::text = '20122013'::text)
25. 0.044 0.097 ↑ 1.8 144 1
→

Hash Join (cost=4.24..26.38 rows=252 width=76) (actual time=0.060..0.097 rows=144 loops=1)

  • Hash Cond: (aspb.aspp_bir_taxyear_id = fstd.taxyear_id)
26. 0.005 0.005 ↑ 29.2 12 1
→

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

27. 0.025 0.048 ↑ 1.0 144 1
→

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

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
28. 0.023 0.023 ↑ 1.0 144 1
→

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

29. 1452.780 1452.780 ↓ 1.1 100066 12
→

Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.39 rows=94870 width=71) (actual time=7.348..121.065 rows=100066 loops=12)

  • Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))
30. 0.002 0.012 ↓ 12.0 12 1
→

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
31. 0.010 0.010 ↓ 12.0 12 1
→

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

  • Filter: ((month >= 1) AND (month <= 12))
32. 0.000 0.000 ↑ 1.0 1 100066
→

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

33. 0.024 0.024 ↑ 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.024..0.024 rows=1 loops=1)

  • Index Cond: (er_id = 100)
34. 0.003 0.015 ↓ 1.1 12 1
→

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
35. 0.012 0.012 ↓ 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.012 rows=12 loops=1)

36. 200.132 200.132 ↑ 1.0 1 100066
→

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=100066)

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