explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZXx3 : SFT_R_ERBRCOST_SUM

Settings
# exclusive inclusive rows x rows loops node
1. 396.941 2,068.738 ↓ 30.0 49,446 1

Sort (cost=169,925.82..169,929.94 rows=1,647 width=1,138) (actual time=1,972.612..2,068.738 rows=49,446 loops=1)

  • Sort Key: (ROW(a.ee_surname, a.ee_id))
  • Sort Method: external merge Disk: 10968kB
2. 22.516 1,671.797 ↓ 30.0 49,446 1

Subquery Scan on a (cost=128,675.18..169,837.82 rows=1,647 width=1,138) (actual time=1,175.031..1,671.797 rows=49,446 loops=1)

3. 415.328 1,649.281 ↓ 30.0 49,446 1

GroupAggregate (cost=128,675.18..169,821.35 rows=1,647 width=960) (actual time=1,175.022..1,649.281 rows=49,446 loops=1)

  • Group Key: fenvy.ee_id, ee.ee_wks_no, ee.ee_surname, ee.ee_fname, (COALESCE((SubPlan 1), ''::character varying)), (COALESCE((SubPlan 3), 'Head Office'::character varying)), ee.ee_dept_id, (COALESC
4. 259.672 1,233.953 ↓ 30.0 49,446 1

Sort (cost=128,675.18..128,679.29 rows=1,647 width=359) (actual time=1,174.939..1,233.953 rows=49,446 loops=1)

  • Sort Key: fenvy.ee_id, ee.ee_wks_no, ee.ee_surname, ee.ee_fname, (COALESCE((SubPlan 1), ''::character varying)), (COALESCE((SubPlan 3), 'Head Office'::character varying)), ee.ee_dept_id, (CO
  • Sort Method: external merge Disk: 10920kB
5. 130.558 974.281 ↓ 30.0 49,446 1

Nested Loop (cost=85,457.51..128,587.18 rows=1,647 width=359) (actual time=643.531..974.281 rows=49,446 loops=1)

6. 0.070 0.070 ↑ 1.0 1 1

Index Only Scan using ft_er_u1 on ft_employers er (cost=0.27..8.29 rows=1 width=4) (actual time=0.067..0.070 rows=1 loops=1)

  • Index Cond: (er_id = 30)
  • Heap Fetches: 1
7. 15.747 744.761 ↓ 30.0 49,446 1

Merge Left Join (cost=85,457.24..87,795.05 rows=1,647 width=283) (actual time=643.370..744.761 rows=49,446 loops=1)

  • Merge Cond: ((fenvy.payslip_id = fedy_2.ee_payslip_id) AND (fenvy.ee_id = fedy_2.ee_id))
  • Join Filter: (fenvy.ee_er_id = fedy_2.ee_er_id)
8. 23.519 728.912 ↓ 30.0 49,446 1

Merge Left Join (cost=85,452.68..87,782.20 rows=1,647 width=255) (actual time=643.265..728.912 rows=49,446 loops=1)

  • Merge Cond: ((fenvy.payslip_id = fedy_1.ee_payslip_id) AND (fenvy.ee_id = fedy_1.ee_id))
  • Join Filter: (fenvy.ee_er_id = fedy_1.ee_er_id)
9. 39.900 582.625 ↓ 30.0 49,446 1

Merge Left Join (cost=81,907.73..83,085.33 rows=1,647 width=223) (actual time=515.359..582.625 rows=49,446 loops=1)

  • Merge Cond: ((fenvy.payslip_id = fedy.ee_payslip_id) AND (fenvy.ee_id = fedy.ee_id))
  • Join Filter: (fenvy.ee_er_id = fedy.ee_er_id)
10. 142.041 426.349 ↓ 30.0 49,446 1

Sort (cost=78,319.70..78,323.82 rows=1,647 width=191) (actual time=393.829..426.349 rows=49,446 loops=1)

  • Sort Key: fenvy.payslip_id, fenvy.ee_id
  • Sort Method: external sort Disk: 10464kB
11. 220.332 284.308 ↓ 30.0 49,446 1

Hash Join (cost=5,717.70..78,231.71 rows=1,647 width=191) (actual time=64.100..284.308 rows=49,446 loops=1)

  • Hash Cond: (fenvy.ee_id = ee.ee_id)
  • -> Index Scan using ft_enetvaly_u2 on ft_ee_net_values_ytd fenvy (cost=0.42..72488.16 rows=2612 width=163) (actual time=0.055..154.917 rows=49491 loops=
  • Index Cond: ((ee_er_id = 30) AND ((process_year)::text = ANY ('{20182019,20192020}'::text[])) AND ((ee_pay_freq)::text = 'W'::text))
  • Filter: ((process_date >= '2018-04-06'::date) AND (process_date <= '2020-04-05'::date))
12. 12.029 63.976 ↓ 1.1 32,682 1

Hash (cost=5,350.08..5,350.08 rows=29,376 width=32) (actual time=63.976..63.976 rows=32,682 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2362kB
13. 51.947 51.947 ↓ 1.1 32,682 1

Seq Scan on ft_employees ee (cost=0.00..5,350.08 rows=29,376 width=32) (actual time=0.023..51.947 rows=32,682 loops=1)

  • Filter: (((ee_sys_status)::text <> 'D'::text) AND ((ee_sys_status)::text <> 'D'::text) AND (ee_er_id = 30))
  • Rows Removed by Filter: 13900
14. 13.913 116.376 ↓ 1.9 48,586 1

Materialize (cost=3,588.03..4,623.79 rows=25,894 width=44) (actual time=64.590..116.376 rows=48,586 loops=1)

15. 29.527 102.463 ↓ 1.9 48,586 1

GroupAggregate (cost=3,588.03..4,300.11 rows=25,894 width=44) (actual time=64.582..102.463 rows=48,586 loops=1)

  • Group Key: fedy.ee_payslip_id, fedy.ee_er_id, fedy.ee_id
16. 42.400 72.936 ↓ 1.9 49,331 1

Sort (cost=3,588.03..3,652.76 rows=25,894 width=22) (actual time=64.550..72.936 rows=49,331 loops=1)

  • Sort Key: fedy.ee_payslip_id, fedy.ee_id
  • Sort Method: external sort Disk: 1544kB
17. 16.182 30.536 ↓ 1.9 49,331 1

Hash Join (cost=3.96..1,689.96 rows=25,894 width=22) (actual time=0.099..30.536 rows=49,331 loops=1)

  • Hash Cond: (fedy.ee_ded_id = ferd.er_ded_id)
18. 14.309 14.309 ↑ 1.0 49,331 1

Seq Scan on ft_ee_deductions_ytd fedy (cost=0.00..1,241.84 rows=49,391 width=22) (actual time=0.041..14.309 rows=49,331 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 3724
19. 0.016 0.045 ↑ 1.2 46 1

Hash (cost=3.29..3.29 rows=54 width=8) (actual time=0.045..0.045 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.029 0.029 ↑ 1.2 46 1

Seq Scan on ft_er_deductions ferd (cost=0.00..3.29 rows=54 width=8) (actual time=0.013..0.029 rows=46 loops=1)

  • Filter: (er_id = ANY ('{0,30}'::integer[]))
  • Rows Removed by Filter: 57
21. 19.692 122.768 ↓ 1.9 48,586 1

Materialize (cost=3,544.95..4,561.55 rows=25,415 width=44) (actual time=62.952..122.768 rows=48,586 loops=1)

22. 31.722 103.076 ↓ 1.9 48,586 1

GroupAggregate (cost=3,544.95..4,243.86 rows=25,415 width=44) (actual time=62.945..103.076 rows=48,586 loops=1)

  • Group Key: fedy_1.ee_payslip_id, fedy_1.ee_er_id, fedy_1.ee_id
23. 42.146 71.354 ↓ 1.9 49,331 1

Sort (cost=3,544.95..3,608.49 rows=25,415 width=26) (actual time=62.911..71.354 rows=49,331 loops=1)

  • Sort Key: fedy_1.ee_payslip_id, fedy_1.ee_id
  • Sort Method: external sort Disk: 1496kB
24. 17.770 29.208 ↓ 1.9 49,331 1

Hash Join (cost=4.21..1,685.41 rows=25,415 width=26) (actual time=0.095..29.208 rows=49,331 loops=1)

  • Hash Cond: (fedy_1.ee_ded_id = ferd_1.er_ded_id)
25. 11.388 11.388 ↑ 1.0 49,331 1

Seq Scan on ft_ee_deductions_ytd fedy_1 (cost=0.00..1,241.84 rows=49,391 width=27) (actual time=0.027..11.388 rows=49,331 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 3724
26. 0.006 0.050 ↑ 1.2 44 1

Hash (cost=3.54..3.54 rows=53 width=7) (actual time=0.050..0.050 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.044 0.044 ↑ 1.2 44 1

Seq Scan on ft_er_deductions ferd_1 (cost=0.00..3.54 rows=53 width=7) (actual time=0.016..0.044 rows=44 loops=1)

  • Filter: ((er_id = ANY ('{0,30}'::integer[])) AND ((ded_code)::text <> ALL ('{PGR,PGN}'::text[])))
  • Rows Removed by Filter: 59
28. 0.005 0.102 ↓ 0.0 0 1

Materialize (cost=4.56..4.60 rows=1 width=44) (actual time=0.102..0.102 rows=0 loops=1)

29. 0.002 0.097 ↓ 0.0 0 1

GroupAggregate (cost=4.56..4.59 rows=1 width=44) (actual time=0.097..0.097 rows=0 loops=1)

  • Group Key: fedy_2.ee_payslip_id, fedy_2.ee_er_id, fedy_2.ee_id
30. 0.018 0.095 ↓ 0.0 0 1

Sort (cost=4.56..4.56 rows=1 width=32) (actual time=0.095..0.095 rows=0 loops=1)

  • Sort Key: fedy_2.ee_payslip_id, fedy_2.ee_id
  • Sort Method: quicksort Memory: 25kB
31. 0.019 0.077 ↓ 0.0 0 1

Hash Join (cost=1.05..4.55 rows=1 width=32) (actual time=0.077..0.077 rows=0 loops=1)

  • Hash Cond: (ferd_2.er_ded_id = fedy_2.ee_ded_id)
32. 0.030 0.030 ↑ 54.0 1 1

Seq Scan on ft_er_deductions ferd_2 (cost=0.00..3.29 rows=54 width=4) (actual time=0.030..0.030 rows=1 loops=1)

  • Filter: (er_id = ANY ('{0,30}'::integer[]))
  • Rows Removed by Filter: 3
33. 0.002 0.028 ↓ 0.0 0 1

Hash (cost=1.04..1.04 rows=1 width=36) (actual time=0.028..0.028 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 0.026 0.026 ↓ 0.0 0 1

Seq Scan on ft_ee_abs_deductions_ytd fedy_2 (cost=0.00..1.04 rows=1 width=36) (actual time=0.026..0.026 rows=0 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 6
35.          

SubPlan (forNested Loop)

36. 49.446 49.446 ↑ 1.0 1 49,446

Index Scan using ft_erdept_p on ft_er_depts fd (cost=0.28..8.30 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=49,446)

  • Index Cond: (dept_id = fenvy.ee_dept_id)
  • Filter: (er_id = fenvy.ee_er_id)
37. 49.446 49.446 ↑ 1.0 1 49,446

Index Scan using ft_erctr_p on ft_er_ccenter fcc (cost=0.28..8.29 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=49,446)

  • Index Cond: (ccenter_id = fenvy.ee_ccenter_id)
  • Filter: (er_id = fenvy.ee_er_id)
38. 0.000 0.000 ↓ 0.0 0 49,446

Index Scan using ft_br_u1 on ft_er_branches ferb (cost=0.14..8.16 rows=1 width=218) (actual time=0.000..0.000 rows=0 loops=49,446)

  • Index Cond: ((br_id = fenvy.ee_br_id) AND (br_er_id = fenvy.ee_er_id))