explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Io3h : SFT_EE_R_WORKER_NONCATEGORY_MOD_02

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 347.756 ↑ 1.0 1 1

Limit (cost=163,409.23..163,409.24 rows=1 width=854) (actual time=347.755..347.756 rows=1 loops=1)

2.          

CTE fee

3. 4.892 5.065 ↓ 1.5 853 1

Bitmap Heap Scan on ft_employees (cost=20.97..2,171.32 rows=551 width=43) (actual time=0.257..5.065 rows=853 loops=1)

  • Recheck Cond: (ee_er_id = 23090)
  • Filter: ((ee_sys_status)::text <> 'D'::text)
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=570
4. 0.173 0.173 ↓ 1.5 858 1

Bitmap Index Scan on ft_ee_u1 (cost=0.00..20.83 rows=588 width=0) (actual time=0.173..0.173 rows=858 loops=1)

  • Index Cond: (ee_er_id = 23090)
5.          

CTE fep

6. 1.231 138.582 ↓ 5,646.0 5,646 1

Nested Loop (cost=0.71..46,555.14 rows=1 width=16) (actual time=33.128..138.582 rows=5,646 loops=1)

7. 0.049 0.049 ↓ 2.0 2 1

Index Scan using ft_erschme_u1 on ft_er_pschemes ferp (cost=0.29..8.30 rows=1 width=12) (actual time=0.028..0.049 rows=2 loops=1)

  • Index Cond: (er_id = 23090)
  • Filter: ((scheme_type)::text = 'WPP'::text)
8. 137.302 137.302 ↓ 2,823.0 2,823 2

Index Only Scan using ft_epschemey_p on ft_ee_pschemes_ytd feep (cost=0.43..46,546.83 rows=1 width=16) (actual time=24.453..68.651 rows=2,823 loops=2)

  • Index Cond: ((ee_er_id = 23090) AND (ee_pscheme_id = ferp.pscheme_id))
  • Heap Fetches: 5646
9.          

CTE fenvy

10. 38.232 107.604 ↑ 22.4 839 1

HashAggregate (cost=66,511.37..66,699.55 rows=18,818 width=16) (actual time=107.251..107.604 rows=839 loops=1)

  • Group Key: feenvy.ee_er_id, feenvy.ee_id
11. 69.372 69.372 ↓ 1.0 19,104 1

Index Scan using ft_enetvaly_i2 on ft_ee_net_values_ytd feenvy (cost=0.56..66,321.35 rows=19,002 width=16) (actual time=0.055..69.372 rows=19,104 loops=1)

  • Index Cond: (ee_er_id = 23090)
12.          

CTE feew

13. 87.159 87.159 ↑ 106.3 6 1

Index Scan using ft_ee_wppy_p on ft_ee_wpp_ytd (cost=0.43..47,359.94 rows=638 width=26) (actual time=84.187..87.159 rows=6 loops=1)

  • Index Cond: (ee_er_id = 23090)
  • Filter: (((ej_flag)::text = 'N'::text) AND ((wor_flag)::text = 'N'::text) AND ((jh_flag)::text = 'N'::text))
  • Rows Removed by Filter: 5640
14. 0.014 347.753 ↑ 1.0 1 1

Sort (cost=623.27..623.28 rows=1 width=854) (actual time=347.753..347.753 rows=1 loops=1)

  • Sort Key: feew.ee_payslip_id DESC
  • Sort Method: quicksort Memory: 25kB
15. 0.014 347.739 ↑ 1.0 1 1

WindowAgg (cost=35.18..623.26 rows=1 width=854) (actual time=347.737..347.739 rows=1 loops=1)

16. 0.215 347.725 ↑ 1.0 1 1

Hash Join (cost=35.18..623.25 rows=1 width=842) (actual time=347.584..347.725 rows=1 loops=1)

  • Hash Cond: ((fenvy.ee_er_id = fep.ee_er_id) AND (fenvy.ee_id = fep.ee_id) AND (fenvy.payslip_id = fep.ee_payslip_id))
17. 107.858 107.858 ↑ 22.4 839 1

CTE Scan on fenvy (cost=0.00..376.36 rows=18,818 width=16) (actual time=107.256..107.858 rows=839 loops=1)

18. 0.009 239.652 ↓ 6.0 6 1

Hash (cost=35.16..35.16 rows=1 width=862) (actual time=239.652..239.652 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.044 239.643 ↓ 6.0 6 1

Hash Join (cost=15.21..35.16 rows=1 width=862) (actual time=236.648..239.643 rows=6 loops=1)

  • Hash Cond: ((feew.ee_er_id = fep.ee_er_id) AND (feew.ee_id = fep.ee_id) AND (feew.ee_payslip_id = fep.ee_payslip_id))
20. 87.174 87.174 ↑ 106.3 6 1

CTE Scan on feew (cost=0.00..12.76 rows=638 width=44) (actual time=84.194..87.174 rows=6 loops=1)

21. 1.874 152.425 ↓ 5,631.0 5,631 1

Hash (cost=15.20..15.20 rows=1 width=818) (actual time=152.425..152.425 rows=5,631 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 597kB
22. 2.056 150.551 ↓ 5,631.0 5,631 1

Hash Join (cost=0.04..15.20 rows=1 width=818) (actual time=143.180..150.551 rows=5,631 loops=1)

  • Hash Cond: ((fee.ee_er_id = fep.ee_er_id) AND (fee.ee_id = fep.ee_id))
23. 5.640 5.640 ↓ 1.5 853 1

CTE Scan on fee (cost=0.00..11.02 rows=551 width=806) (actual time=0.261..5.640 rows=853 loops=1)

24. 1.577 142.855 ↓ 5,646.0 5,646 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=142.855..142.855 rows=5,646 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 307kB
25. 141.278 141.278 ↓ 5,646.0 5,646 1

CTE Scan on fep (cost=0.00..0.02 rows=1 width=12) (actual time=33.136..141.278 rows=5,646 loops=1)