explain.depesz.com

A tool for finding a real cause for slow queries.

Result: SoP : Old P32 V

options
# exclusive inclusive rows x rows loops node
1. 0.023 4240.505 ↑ 1.0 12 1
→

Hash Left Join (cost=3464.00..3465.20 rows=12 width=580) (actual time=4240.496..4240.505 rows=12 loops=1)

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

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

3. 0.011 4240.475 ↓ 11.0 11 1
→

Hash (cost=3463.99..3463.99 rows=1 width=580) (actual time=4240.475..4240.475 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
4. 0.002 4240.464 ↓ 11.0 11 1
→

Subquery Scan on b (cost=3463.97..3463.99 rows=1 width=580) (actual time=4240.451..4240.464 rows=11 loops=1)

5. 315.029 4240.462 ↓ 11.0 11 1
→

HashAggregate (cost=3463.97..3463.98 rows=1 width=404) (actual time=4240.451..4240.462 rows=11 loops=1)

6. 41.797 3925.433 ↓ 90020.0 90020 1
→

Nested Loop (cost=1554.59..3463.91 rows=1 width=404) (actual time=2356.348..3925.433 rows=90020 loops=1)

7. 41.128 3683.500 ↓ 100068.0 100068 1
→

Hash Join (cost=1554.59..3463.58 rows=1 width=412) (actual time=2356.342..3683.500 rows=100068 loops=1)

  • Hash Cond: ((fenvy.process_year)::text = (fer.er_cur_taxyear)::text)
8. 964.720 3642.348 ↓ 5615.0 101070 1
→

Nested Loop (cost=1546.31..3455.23 rows=18 width=417) (actual time=2354.649..3642.348 rows=101070 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)))
9. 0.036 2355.163 ↓ 6.5 13 1
→

Hash Join (cost=1546.31..1626.66 rows=2 width=393) (actual time=2354.622..2355.163 rows=13 loops=1)

  • Hash Cond: (fstw.month = fstw.month)
10. 482.775 2355.118 ↑ 27.6 13 1
→

HashAggregate (cost=1545.12..1620.51 rows=359 width=357) (actual time=2354.606..2355.118 rows=13 loops=1)

11. 62.292 1872.343 ↓ 253.5 91022 1
→

Nested Loop (cost=5.49..1494.86 rows=359 width=357) (actual time=0.352..1872.343 rows=91022 loops=1)

12. 44.584 1607.911 ↓ 104.0 101070 1
→

Hash Join (cost=5.49..1185.09 rows=972 width=361) (actual time=0.342..1607.911 rows=101070 loops=1)

  • Hash Cond: (fst.taxyear_id = smp.smp_taxyear_id)
13. 51.891 1563.315 ↓ 104.0 101070 1
→

Nested Loop (cost=4.24..1170.47 rows=972 width=333) (actual time=0.321..1563.315 rows=101070 loops=1)

14. 1143.946 1511.424 ↓ 104.0 101070 1
→

Nested Loop (cost=4.24..1150.05 rows=972 width=331) (actual time=0.302..1511.424 rows=101070 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)))
15. 0.523 3.014 ↓ 144.0 144 1
→

Nested Loop (cost=4.24..38.43 rows=1 width=336) (actual time=0.133..3.014 rows=144 loops=1)

  • Join Filter: (fst.taxyear_id = spb.spp_bir_taxyear_id)
16. 0.106 2.347 ↓ 144.0 144 1
→

Nested Loop (cost=4.24..37.18 rows=1 width=276) (actual time=0.126..2.347 rows=144 loops=1)

17. 0.054 2.097 ↓ 144.0 144 1
→

Nested Loop (cost=4.24..36.90 rows=1 width=258) (actual time=0.110..2.097 rows=144 loops=1)

18. 0.487 1.755 ↓ 144.0 144 1
→

Nested Loop (cost=4.24..36.56 rows=1 width=220) (actual time=0.090..1.755 rows=144 loops=1)

  • Join Filter: (sap.sap_taxyear_id = spa.spp_adp_taxyear_id)
19. 0.262 1.124 ↓ 144.0 144 1
→

Nested Loop (cost=4.24..35.32 rows=1 width=160) (actual time=0.083..1.124 rows=144 loops=1)

  • Join Filter: (aspb.aspp_bir_taxyear_id = sap.sap_taxyear_id)
20. 0.183 0.718 ↓ 6.9 144 1
→

Nested Loop (cost=4.24..30.71 rows=21 width=100) (actual time=0.071..0.718 rows=144 loops=1)

  • Join Filter: (fstd.month = fstw.month)
21. 0.019 0.019 ↓ 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.019 rows=12 loops=1)

  • Filter: ((month >= 1) AND (month <= 12))
22. 0.429 0.516 ↑ 1.8 144 12
→

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

  • Hash Cond: (aspb.aspp_bir_taxyear_id = fstd.taxyear_id)
23. 0.036 0.036 ↑ 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.003 rows=12 loops=12)

24. 0.024 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
25. 0.027 0.027 ↑ 1.0 144 1
→

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

26. 0.137 0.144 ↓ 1.1 12 144
→

Materialize (cost=0.00..1.17 rows=11 width=60) (actual time=0.000..0.001 rows=12 loops=144)

27. 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.007..0.007 rows=12 loops=1)

28. 0.144 0.144 ↓ 1.1 12 144
→

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

29. 0.288 0.288 ↑ 1.0 1 144
→

Index Scan using ft_sty_p on ft_sys_taxyear fst (cost=0.00..0.32 rows=1 width=38) (actual time=0.001..0.002 rows=1 loops=144)

  • Index Cond: (taxyear_id = sap.sap_taxyear_id)
30. 0.144 0.144 ↑ 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.001..0.001 rows=1 loops=144)

  • Index Cond: (ssp_taxyear_id = fst.taxyear_id)
31. 0.144 0.144 ↓ 1.1 12 144
→

Seq Scan on ft_sys_spp_bir spb (cost=0.00..1.11 rows=11 width=60) (actual time=0.000..0.001 rows=12 loops=144)

32. 364.464 364.464 ↑ 1.5 8422 144
→

Index Scan using ft_enetvaly_u2 on ft_ee_net_values_ytd fenvy (cost=0.00..479.03 rows=12652 width=71) (actual time=0.007..2.531 rows=8422 loops=144)

  • Index Cond: ((ee_er_id = 100) AND ((process_year)::text = (fst.taxyear)::text))
33. 0.000 0.000 ↑ 1.0 1 101070
→

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

34. 0.017 0.017 ↑ 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.017 rows=1 loops=1)

  • Index Cond: (er_id = 100)
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.006..0.009 rows=12 loops=1)

37. 202.140 202.140 ↑ 1.0 1 101070
→

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

  • Index Cond: (ee_id = fenvy.ee_id)
  • Filter: (((ee_sys_status)::text <> 'D'::text) AND (ee_er_id = 100))
38. 0.004 0.009 ↓ 12.0 12 1
→

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

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

  • Filter: ((month >= 1) AND (month <= 12))
40. 322.465 322.465 ↓ 6.7 84827 13
→

Index Scan using ft_enetvaly_u2 on ft_ee_net_values_ytd fenvy (cost=0.00..408.20 rows=12652 width=58) (actual time=0.033..24.805 rows=84827 loops=13)

  • Index Cond: ((ee_er_id = 100) AND ((process_year)::text = (fenvy.process_year)::text))
41. 0.002 0.024 ↑ 1.0 1 1
→

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
42. 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)
43. 200.136 200.136 ↑ 1.0 1 100068
→

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

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