explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CUGj : Sft_r_grosspay

Settings
# exclusive inclusive rows x rows loops node
1. 680.816 18,348.138 ↓ 7.3 84,965 1

Sort (cost=979,899.75..979,928.78 rows=11,610 width=320) (actual time=18,252.094..18,348.138 rows=84,965 loops=1)

  • Sort Key: temp.ee_er_id, temp.ee_surname, temp.ee_id, temp.process_date
  • Sort Method: external merge Disk: 7680kB
2. 284.952 17,667.322 ↓ 7.3 84,965 1

Subquery Scan on temp (cost=105,623.04..979,115.90 rows=11,610 width=320) (actual time=586.445..17,667.322 rows=84,965 loops=1)

3. 269.128 899.160 ↓ 7.3 84,965 1

GroupAggregate (cost=105,623.04..106,203.54 rows=11,610 width=164) (actual time=586.025..899.160 rows=84,965 loops=1)

  • Group Key: fee.ee_id, fenvy.process_date
4. 141.792 630.032 ↓ 7.3 84,965 1

Sort (cost=105,623.04..105,652.06 rows=11,610 width=63) (actual time=585.935..630.032 rows=84,965 loops=1)

  • Sort Key: fee.ee_id, fenvy.process_date
  • Sort Method: external merge Disk: 6376kB
5. 63.062 488.240 ↓ 7.3 84,965 1

Hash Join (cost=10,102.32..104,839.18 rows=11,610 width=63) (actual time=87.004..488.240 rows=84,965 loops=1)

  • Hash Cond: (fenvy.ee_id = fee.ee_id)
6. 344.388 374.353 ↓ 5.4 84,965 1

Bitmap Heap Scan on ft_ee_net_values_ytd fenvy (cost=4,553.09..99,115.19 rows=15,643 width=43) (actual time=36.020..374.353 rows=84,965 loops=1)

  • Recheck Cond: ((ee_er_id = 30) AND ((process_year)::text = ANY ('{20172018,20182019}'::text[])) AND ((ee_pay_freq)::text = 'W'::text))
  • Filter: ((process_date >= '2017-04-06'::date) AND (process_date <= '2018-04-05'::date))
  • Rows Removed by Filter: 48883
  • Heap Blocks: exact=26744
7. 29.965 29.965 ↓ 1.0 133,848 1

Bitmap Index Scan on ft_enetvaly_u2 (cost=0.00..4,549.18 rows=130,267 width=0) (actual time=29.965..29.965 rows=133,848 loops=1)

  • Index Cond: ((ee_er_id = 30) AND ((process_year)::text = ANY ('{20172018,20182019}'::text[])) AND ((ee_pay_freq)::text = 'W'::text))
8. 12.614 50.825 ↑ 1.0 34,526 1

Hash (cost=5,117.16..5,117.16 rows=34,566 width=28) (actual time=50.825..50.825 rows=34,526 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2559kB
9. 38.211 38.211 ↑ 1.0 34,526 1

Seq Scan on ft_employees fee (cost=0.00..5,117.16 rows=34,566 width=28) (actual time=0.108..38.211 rows=34,526 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 12050
10.          

SubPlan (forSubquery Scan)

11. 169.930 3,908.390 ↑ 1.0 1 84,965

Nested Loop (cost=0.43..15.88 rows=1 width=32) (actual time=0.044..0.046 rows=1 loops=84,965)

12. 3,313.635 3,313.635 ↑ 1.0 1 84,965

Seq Scan on ft_er_payments ferp (cost=0.00..7.39 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=84,965)

  • Filter: ((pay_code)::text = 'BAS'::text)
  • Rows Removed by Filter: 270
13. 424.825 424.825 ↑ 1.0 1 84,965

Index Scan using ft_epayy_p on ft_ee_payments_ytd fepy (cost=0.43..8.48 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=84,965)

  • Index Cond: ((ee_payslip_id = temp.payslip_id) AND (ee_pay_id = ferp.er_pay_id))
14. 84.965 509.790 ↑ 1.0 1 84,965

Aggregate (cost=21.27..21.28 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=84,965)

15. 254.895 424.825 ↑ 1.0 1 84,965

Nested Loop (cost=0.43..21.26 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=84,965)

  • Join Filter: (fepy_1.ee_pay_id = ferp_1.er_pay_id)
  • Rows Removed by Join Filter: 5
16. 169.930 169.930 ↑ 1.3 3 84,965

Index Scan using ft_epayy_p on ft_ee_payments_ytd fepy_1 (cost=0.43..13.75 rows=4 width=8) (actual time=0.002..0.002 rows=3 loops=84,965)

  • Index Cond: (ee_payslip_id = temp.payslip_id)
17. 0.000 0.000 ↑ 1.0 2 267,688

Materialize (cost=0.00..7.40 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=267,688)

18. 0.053 0.053 ↑ 1.0 2 1

Seq Scan on ft_er_payments ferp_1 (cost=0.00..7.39 rows=2 width=4) (actual time=0.010..0.053 rows=2 loops=1)

  • Filter: ((pay_code)::text = ANY ('{OVT,OVT1}'::text[]))
  • Rows Removed by Filter: 269
19. 169.930 594.755 ↑ 1.0 1 84,965

Aggregate (cost=8.94..8.95 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=84,965)

20. 169.926 424.825 ↓ 0.0 0 84,965

Hash Join (cost=1.20..8.94 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=84,965)

  • Hash Cond: (ferp_2.er_pay_id = feapy.ee_pay_id)
21. 0.004 0.004 ↑ 271.0 1 1

Seq Scan on ft_er_payments ferp_2 (cost=0.00..6.71 rows=271 width=4) (actual time=0.004..0.004 rows=1 loops=1)

22. 84.965 254.895 ↓ 0.0 0 84,965

Hash (cost=1.19..1.19 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=84,965)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 169.930 169.930 ↓ 0.0 0 84,965

Seq Scan on ft_ee_abs_payments_ytd feapy (cost=0.00..1.19 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=84,965)

  • Filter: (ee_payslip_id = temp.payslip_id)
  • Rows Removed by Filter: 19
24. 169.930 11,045.450 ↑ 1.0 1 84,965

Aggregate (cost=24.58..24.59 rows=1 width=32) (actual time=0.130..0.130 rows=1 loops=84,965)

25. 2,804.209 10,875.520 ↑ 4.0 1 84,965

Hash Join (cost=13.80..24.57 rows=4 width=4) (actual time=0.039..0.128 rows=1 loops=84,965)

  • Hash Cond: (ferp_3.er_pay_id = fepy_2.ee_pay_id)
26. 7,731.451 7,731.451 ↑ 1.0 265 84,961

Seq Scan on ft_er_payments ferp_3 (cost=0.00..8.74 rows=265 width=4) (actual time=0.002..0.091 rows=265 loops=84,961)

  • Filter: ((pay_code)::text <> ALL ('{BAS,OVT,OVT1,COM,BNS,ALW}'::text[]))
  • Rows Removed by Filter: 6
27. 84.965 339.860 ↑ 1.3 3 84,965

Hash (cost=13.75..13.75 rows=4 width=8) (actual time=0.004..0.004 rows=3 loops=84,965)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 254.895 254.895 ↑ 1.3 3 84,965

Index Scan using ft_epayy_p on ft_ee_payments_ytd fepy_2 (cost=0.43..13.75 rows=4 width=8) (actual time=0.002..0.003 rows=3 loops=84,965)

  • Index Cond: (ee_payslip_id = temp.payslip_id)
29. 84.965 424.825 ↑ 1.0 1 84,965

Aggregate (cost=4.47..4.48 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=84,965)

30. 169.915 339.860 ↓ 0.0 0 84,965

Hash Join (cost=1.05..4.46 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=84,965)

  • Hash Cond: (fedp.er_ded_id = fedy.ee_ded_id)
31. 0.015 0.015 ↑ 102.0 1 1

Seq Scan on ft_er_deductions fedp (cost=0.00..3.02 rows=102 width=4) (actual time=0.015..0.015 rows=1 loops=1)

32. 84.965 169.930 ↓ 0.0 0 84,965

Hash (cost=1.04..1.04 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=84,965)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
33. 84.965 84.965 ↓ 0.0 0 84,965

Seq Scan on ft_ee_abs_deductions_ytd fedy (cost=0.00..1.04 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=84,965)

  • Filter: (ee_payslip_id = temp.payslip_id)
  • Rows Removed by Filter: 6