explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AEvw : EMP_PERSONAL_DETAILS_2

Settings
# exclusive inclusive rows x rows loops node
1. 276.898 1,532.808 ↓ 1.0 104,057 1

Sort (cost=1,810,503.67..1,810,763.53 rows=103,944 width=498) (actual time=1,484.013..1,532.808 rows=104,057 loops=1)

  • Sort Key: fee.ee_surname, fee.ee_id
  • Sort Method: external merge Disk: 27632kB
2. 386.342 1,255.910 ↓ 1.0 104,057 1

Hash Join (cost=7,102.81..1,754,942.31 rows=103,944 width=498) (actual time=189.429..1,255.910 rows=104,057 loops=1)

  • Hash Cond: (fee.ee_ni_cat_id = fnc.ni_cat_id)
3. 206.775 661.283 ↑ 1.0 104,057 1

Hash Left Join (cost=7,087.56..25,150.10 rows=104,530 width=196) (actual time=188.811..661.283 rows=104,057 loops=1)

  • Hash Cond: ((fee.ee_er_id = feh.ee_er_id) AND (fee.ee_id = feh.ee_id))
4. 265.932 265.932 ↑ 1.0 104,057 1

Seq Scan on ft_employees fee (cost=0.00..17,278.49 rows=104,530 width=151) (actual time=0.044..265.932 rows=104,057 loops=1)

  • Filter: ((ee_er_id = 3178) AND ((ee_sys_status)::text = 'A'::text))
  • Rows Removed by Filter: 173620
5. 64.440 188.576 ↓ 4,730.0 104,061 1

Hash (cost=7,087.23..7,087.23 rows=22 width=53) (actual time=188.576..188.576 rows=104,061 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 2934kB
6. 66.894 124.136 ↓ 4,730.0 104,061 1

Gather (cost=1,017.01..7,087.23 rows=22 width=53) (actual time=7.547..124.136 rows=104,061 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 25.325 57.242 ↓ 3,854.1 34,687 3

Hash Join (cost=17.01..6,085.03 rows=9 width=53) (actual time=2.447..57.242 rows=34,687 loops=3)

  • Hash Cond: (feh.ee_hol_schm_id = fehs.hol_schm_id)
8. 31.813 31.813 ↑ 1.3 34,687 3

Parallel Seq Scan on ft_ee_holiday feh (cost=0.00..5,953.37 rows=43,667 width=28) (actual time=2.169..31.813 rows=34,687 loops=3)

  • Filter: (ee_er_id = 3178)
  • Rows Removed by Filter: 57872
9. 0.013 0.104 ↑ 1.0 4 3

Hash (cost=16.96..16.96 rows=4 width=37) (actual time=0.104..0.104 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.091 0.091 ↑ 1.0 4 3

Index Scan using ft_erhschm_u1 on ft_er_hol_schemes fehs (cost=0.29..16.96 rows=4 width=37) (actual time=0.079..0.091 rows=4 loops=3)

  • Index Cond: (er_id = 3178)
11. 0.005 0.171 ↑ 1.2 8 1

Hash (cost=15.12..15.12 rows=10 width=16) (actual time=0.171..0.171 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.013 0.166 ↑ 1.2 8 1

Nested Loop (cost=1.53..15.12 rows=10 width=16) (actual time=0.160..0.166 rows=8 loops=1)

13. 0.039 0.039 ↑ 1.0 1 1

Index Scan using ft_er_u1 on ft_employers fer (cost=0.28..8.30 rows=1 width=19) (actual time=0.038..0.039 rows=1 loops=1)

  • Index Cond: (er_id = 3178)
  • Filter: ((er_cur_taxyear)::text = '20192020'::text)
14. 0.050 0.114 ↑ 1.2 8 1

Hash Join (cost=1.25..6.72 rows=10 width=40) (actual time=0.110..0.114 rows=8 loops=1)

  • Hash Cond: (fnc.ni_taxyear_id = fst.taxyear_id)
15. 0.040 0.040 ↑ 1.0 187 1

Seq Scan on ft_ni_cat fnc (cost=0.00..4.87 rows=187 width=10) (actual time=0.010..0.040 rows=187 loops=1)

16. 0.007 0.024 ↑ 1.0 1 1

Hash (cost=1.24..1.24 rows=1 width=38) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on ft_sys_taxyear fst (cost=0.00..1.24 rows=1 width=38) (actual time=0.017..0.017 rows=1 loops=1)

  • Filter: ((taxyear)::text = '20192020'::text)
  • Rows Removed by Filter: 18
18.          

SubPlan (for Hash Join)

19. 104.057 104.057 ↑ 1.0 1 104,057

Index Scan using ft_erdept_p on ft_er_depts ferd (cost=0.29..8.31 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=104,057)

  • Index Cond: (dept_id = fee.ee_dept_id)
  • Filter: (er_id = fee.ee_er_id)
20. 104.057 104.057 ↑ 1.0 1 104,057

Index Scan using ft_erctr_p on ft_er_ccenter fcc (cost=0.29..8.30 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=104,057)

  • Index Cond: (ccenter_id = fee.ee_ccenter_id)
  • Filter: (er_id = fee.ee_er_id)
Planning time : 5.678 ms
Execution time : 1,580.129 ms