explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sHh : SFT_EE_R_WORKER_NONCATEGORY_MOD_03

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

Limit (cost=163,410.15..163,410.16 rows=1 width=854) (actual time=305.130..305.131 rows=1 loops=1)

2. 0.012 305.128 ↑ 1.0 1 1

Sort (cost=163,410.15..163,410.16 rows=1 width=854) (actual time=305.128..305.128 rows=1 loops=1)

  • Sort Key: fenvy.payslip_id DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.015 305.116 ↑ 1.0 1 1

WindowAgg (cost=162,822.05..163,410.13 rows=1 width=854) (actual time=305.113..305.116 rows=1 loops=1)

4.          

CTE fee

5. 4.700 4.872 ↓ 1.5 853 1

Bitmap Heap Scan on ft_employees (cost=20.97..2,171.32 rows=551 width=43) (actual time=0.279..4.872 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
6. 0.172 0.172 ↓ 1.5 858 1

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

  • Index Cond: (ee_er_id = 23090)
7.          

CTE fep

8. 1.165 124.364 ↓ 5,646.0 5,646 1

Nested Loop (cost=0.71..46,555.66 rows=1 width=16) (actual time=29.398..124.364 rows=5,646 loops=1)

9. 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.029..0.049 rows=2 loops=1)

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

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

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

CTE fenvy

12. 22.373 89.018 ↑ 22.4 839 1

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

  • Group Key: feenvy.ee_er_id, feenvy.ee_id
13. 66.645 66.645 ↓ 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.060..66.645 rows=19,104 loops=1)

  • Index Cond: (ee_er_id = 23090)
14.          

CTE feew

15. 77.373 77.373 ↑ 106.3 6 1

Index Scan using ft_ee_wppy_p on ft_ee_wpp_ytd (cost=0.43..47,360.33 rows=638 width=26) (actual time=74.244..77.373 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
16. 0.204 305.101 ↑ 1.0 1 1

Hash Join (cost=35.18..623.25 rows=1 width=842) (actual time=304.897..305.101 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. 89.341 89.341 ↑ 22.4 839 1

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

18. 0.012 215.556 ↓ 6.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.046 215.544 ↓ 6.0 6 1

Hash Join (cost=15.21..35.16 rows=1 width=862) (actual time=212.393..215.544 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. 77.391 77.391 ↑ 106.3 6 1

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

21. 1.924 138.107 ↓ 5,631.0 5,631 1

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

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

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

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

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

24. 1.438 128.530 ↓ 5,646.0 5,646 1

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

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

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