explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MN1 : Sft_r_grosspay_with_Index

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

Sort (cost=980,059.39..980,088.42 rows=11,610 width=320) (actual time=18,422.046..18,519.708 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. 401.356 17,842.216 ↓ 7.3 84,965 1

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

3. 270.779 957.650 ↓ 7.3 84,965 1

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

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

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

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

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

  • Hash Cond: (fenvy.ee_id = fee.ee_id)
6. 392.516 423.062 ↓ 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.751..423.062 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. 30.546 30.546 ↓ 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=30.546..30.546 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. 13.054 47.380 ↑ 1.0 34,526 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2559kB
9. 34.326 34.326 ↑ 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.022..34.326 rows=34,526 loops=1)

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

SubPlan (forSubquery Scan)

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

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

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

Seq Scan on ft_er_payments ferp (cost=0.00..7.39 rows=1 width=4) (actual time=0.039..0.040 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.051 0.051 ↑ 1.0 2 1

Seq Scan on ft_er_payments ferp_1 (cost=0.00..7.39 rows=2 width=4) (actual time=0.011..0.051 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.007..0.007 rows=1 loops=84,965)

20. 169.920 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.010 0.010 ↑ 271.0 1 1

Seq Scan on ft_er_payments ferp_2 (cost=0.00..6.71 rows=271 width=4) (actual time=0.010..0.010 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.040..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.48..4.49 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=84,965)

30. 169.916 339.860 ↓ 0.0 0 84,965

Hash Join (cost=1.05..4.48 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.014 0.014 ↑ 103.0 1 1

Seq Scan on ft_er_deductions fedp (cost=0.00..3.03 rows=103 width=4) (actual time=0.014..0.014 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