explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 8Yg : NEW_P32_V_REPORT

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.018 5221.571 ↑ 5.1 13 1

Sort (cost=35435.87..35436.03 rows=66 width=734) (actual time=5221.571..5221.571 rows=13 loops=1)

  • Sort Key: a.month
  • Sort Method: quicksort Memory: 27kB
2. 0.058 5221.553 ↑ 5.1 13 1

HashAggregate (cost=35433.21..35433.87 rows=66 width=734) (actual time=5221.548..5221.553 rows=13 loops=1)

3. 0.005 5221.495 ↑ 5.1 13 1

Append (cost=1405.02..35429.09 rows=66 width=734) (actual time=879.956..5221.495 rows=13 loops=1)

4. 0.030 2667.205 ↑ 5.4 12 1

Merge Right Join (cost=1405.02..17878.71 rows=65 width=744) (actual time=879.956..2667.205 rows=12 loops=1)

  • Merge Cond: (fstw.month = a.month)
5. 275.148 2667.151 ↑ 119.7 9 1

GroupAggregate (cost=1403.69..17862.93 rows=1077 width=75) (actual time=879.931..2667.151 rows=9 loops=1)

6. 377.601 2392.003 ↓ 24.3 90018 1

Nested Loop (cost=1403.69..17224.89 rows=3712 width=75) (actual time=609.136..2392.003 rows=90018 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)))
7. 0.056 0.214 ↓ 12.0 12 1

Nested Loop (cost=4.21..5.46 rows=1 width=70) (actual time=0.053..0.214 rows=12 loops=1)

8. 0.048 0.110 ↓ 12.0 12 1

Merge Join (cost=4.21..4.28 rows=1 width=40) (actual time=0.046..0.110 rows=12 loops=1)

  • Merge Cond: (fstw.month = fstd.month)
9. 0.017 0.027 ↓ 12.0 12 1

Sort (cost=1.19..1.19 rows=1 width=28) (actual time=0.021..0.027 rows=12 loops=1)

  • Sort Key: fstw.month
  • Sort Method: quicksort Memory: 25kB
10. 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.008..0.010 rows=12 loops=1)

  • Filter: ((month >= 1) AND (month <= 12))
11. 0.020 0.035 ↑ 1.0 12 1

Sort (cost=3.02..3.05 rows=12 width=16) (actual time=0.023..0.035 rows=12 loops=1)

  • Sort Key: fstd.month
  • Sort Method: quicksort Memory: 25kB
12. 0.015 0.015 ↑ 1.0 12 1

Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.80 rows=12 width=16) (actual time=0.013..0.015 rows=12 loops=1)

  • Filter: (taxyear_id = 12)
13. 0.048 0.048 ↑ 1.0 1 12

Seq Scan on ft_sys_taxyear fst (cost=0.00..1.17 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=12)

  • Filter: (((taxyear)::text = '20122013'::text) AND (taxyear_id = 12))
14. 578.452 2014.188 ↓ 2.6 90018 12

Hash Join (cost=1399.48..15551.14 rows=35122 width=81) (actual time=8.078..167.849 rows=90018 loops=12)

  • Hash Cond: (fenvy.ee_id = fee.ee_id)
15. 1426.956 1426.956 ↓ 1.1 100066 12

Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.39 rows=94870 width=85) (actual time=7.342..118.913 rows=100066 loops=12)

  • Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))
16. 1.623 8.780 ↑ 1.0 10002 1

Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.780..8.780 rows=10002 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 391kB
17. 6.526 7.157 ↑ 1.0 10002 1

Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.670..7.157 rows=10002 loops=1)

  • Recheck Cond: (ee_er_id = 100)
  • Filter: ((ee_sys_status)::text <> 'D'::text)
18. 0.631 0.631 ↑ 1.0 10002 1

Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.631..0.631 rows=10002 loops=1)

  • Index Cond: (ee_er_id = 100)
19. 0.019 0.024 ↑ 1.0 12 1

Sort (cost=1.34..1.37 rows=12 width=4) (actual time=0.019..0.024 rows=12 loops=1)

  • Sort Key: a.month
  • Sort Method: quicksort Memory: 25kB
20. 0.005 0.005 ↑ 1.0 12 1

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

21. 265.008 2554.285 ↑ 1.0 1 1

Aggregate (cost=17549.45..17549.72 rows=1 width=58) (actual time=2554.285..2554.285 rows=1 loops=1)

22. 381.701 2289.277 ↓ 24.3 90018 1

Nested Loop (cost=1400.67..17224.65 rows=3712 width=58) (actual time=585.089..2289.277 rows=90018 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)))
23. 0.064 0.188 ↓ 12.0 12 1

Nested Loop (cost=1.19..5.22 rows=1 width=70) (actual time=0.059..0.188 rows=12 loops=1)

24. 0.036 0.076 ↓ 12.0 12 1

Hash Join (cost=1.19..4.05 rows=1 width=40) (actual time=0.051..0.076 rows=12 loops=1)

  • Hash Cond: (fstd.month = fstw.month)
25. 0.028 0.028 ↑ 1.0 12 1

Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.80 rows=12 width=16) (actual time=0.016..0.028 rows=12 loops=1)

  • Filter: (taxyear_id = 12)
26. 0.004 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
27. 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.004..0.008 rows=12 loops=1)

  • Filter: ((month >= 1) AND (month <= 12))
28. 0.048 0.048 ↑ 1.0 1 12

Seq Scan on ft_sys_taxyear fst (cost=0.00..1.17 rows=1 width=38) (actual time=0.003..0.004 rows=1 loops=12)

  • Filter: ((taxyear_id = 12) AND ((taxyear)::text = '20122013'::text))
29. 609.977 1907.388 ↓ 2.6 90018 12

Hash Join (cost=1399.48..15551.14 rows=35122 width=77) (actual time=1.998..158.949 rows=90018 loops=12)

  • Hash Cond: (fenvy.ee_id = fee.ee_id)
30. 1289.268 1289.268 ↓ 1.1 100066 12

Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.39 rows=94870 width=85) (actual time=1.315..107.439 rows=100066 loops=12)

  • Filter: ((ee_er_id = 100) AND ((process_year)::text = '20122013'::text))
31. 1.622 8.143 ↑ 1.0 10002 1

Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.143..8.143 rows=10002 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 391kB
32. 5.949 6.521 ↑ 1.0 10002 1

Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.611..6.521 rows=10002 loops=1)

  • Recheck Cond: (ee_er_id = 100)
  • Filter: ((ee_sys_status)::text <> 'D'::text)
33. 0.572 0.572 ↑ 1.0 10002 1

Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.572..0.572 rows=10002 loops=1)

  • Index Cond: (ee_er_id = 100)