explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kudR : Sft_r_grosspay_03

Settings
# exclusive inclusive rows x rows loops node
1. 710.924 29,845.874 ↓ 7.3 84,965 1

Sort (cost=1,531,386.39..1,531,415.33 rows=11,579 width=380) (actual time=29,745.710..29,845.874 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. 738.277 29,134.950 ↓ 7.3 84,965 1

Subquery Scan on temp (cost=105,728.88..1,528,585.35 rows=11,579 width=380) (actual time=778.300..29,134.950 rows=84,965 loops=1)

3. 298.557 1,122.908 ↓ 7.3 84,965 1

GroupAggregate (cost=105,728.88..106,307.83 rows=11,579 width=164) (actual time=777.505..1,122.908 rows=84,965 loops=1)

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

Sort (cost=105,728.88..105,757.83 rows=11,579 width=63) (actual time=777.433..824.351 rows=84,965 loops=1)

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

Hash Join (cost=10,102.63..104,947.34 rows=11,579 width=63) (actual time=102.374..664.768 rows=84,965 loops=1)

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

Bitmap Heap Scan on ft_ee_net_values_ytd fenvy (cost=4,554.97..99,225.16 rows=15,661 width=43) (actual time=51.212..546.749 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: 49450
  • Heap Blocks: exact=26844
7. 44.544 44.544 ↓ 1.0 134,469 1

Bitmap Index Scan on ft_enetvaly_u2 (cost=0.00..4,551.05 rows=130,416 width=0) (actual time=44.544..44.544 rows=134,469 loops=1)

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

Hash (cost=5,117.20..5,117.20 rows=34,437 width=28) (actual time=50.582..50.582 rows=34,526 loops=1)

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

Seq Scan on ft_employees fee (cost=0.00..5,117.20 rows=34,437 width=28) (actual time=0.041..35.683 rows=34,526 loops=1)

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

SubPlan (forSubquery Scan)

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

Nested Loop (cost=0.43..15.88 rows=1 width=32) (actual time=0.046..0.047 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. 509.790 509.790 ↑ 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.006..0.006 rows=1 loops=84,965)

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

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

15. 254.895 509.790 ↑ 1.0 1 84,965

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

  • Join Filter: (fepy_1.ee_pay_id = ferp_1.er_pay_id)
  • Rows Removed by Join Filter: 5
16. 254.895 254.895 ↑ 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.003 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.057 0.057 ↑ 1.0 2 1

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

  • Filter: ((pay_code)::text = ANY ('{OVT,OVT1}'::text[]))
  • Rows Removed by Filter: 269
19. 0.000 3,483.565 ↓ 0.0 0 84,965

Nested Loop (cost=0.43..15.88 rows=1 width=4) (actual time=0.041..0.041 rows=0 loops=84,965)

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

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

  • Filter: ((pay_code)::text = 'COM'::text)
  • Rows Removed by Filter: 270
21. 169.930 169.930 ↓ 0.0 0 84,965

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

  • Index Cond: ((ee_payslip_id = temp.payslip_id) AND (ee_pay_id = ferp_2.er_pay_id))
22. 0.000 3,483.565 ↓ 0.0 0 84,965

Nested Loop (cost=0.43..15.88 rows=1 width=4) (actual time=0.041..0.041 rows=0 loops=84,965)

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

Seq Scan on ft_er_payments ferp_3 (cost=0.00..7.39 rows=1 width=4) (actual time=0.004..0.040 rows=1 loops=84,965)

  • Filter: ((pay_code)::text = 'BNS'::text)
  • Rows Removed by Filter: 270
24. 84.965 84.965 ↓ 0.0 0 84,965

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

  • Index Cond: ((ee_payslip_id = temp.payslip_id) AND (ee_pay_id = ferp_3.er_pay_id))
25. 84.965 3,483.565 ↓ 0.0 0 84,965

Nested Loop (cost=0.43..15.88 rows=1 width=4) (actual time=0.041..0.041 rows=0 loops=84,965)

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

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

  • Filter: ((pay_code)::text = 'ALW'::text)
  • Rows Removed by Filter: 270
27. 84.965 84.965 ↓ 0.0 0 84,965

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

  • Index Cond: ((ee_payslip_id = temp.payslip_id) AND (ee_pay_id = ferp_4.er_pay_id))
28. 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)

29. 169.927 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_5.er_pay_id = feapy.ee_pay_id)
30. 0.003 0.003 ↑ 271.0 1 1

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

31. 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
32. 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
33. 169.930 11,130.415 ↑ 1.0 1 84,965

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

34. 2,804.213 10,960.485 ↑ 4.0 1 84,965

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

  • Hash Cond: (ferp_6.er_pay_id = fepy_5.ee_pay_id)
35. 7,816.412 7,816.412 ↑ 1.0 265 84,961

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

  • Filter: ((pay_code)::text <> ALL ('{BAS,OVT,OVT1,COM,BNS,ALW}'::text[]))
  • Rows Removed by Filter: 6
36. 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
37. 254.895 254.895 ↑ 1.3 3 84,965

Index Scan using ft_epayy_p on ft_ee_payments_ytd fepy_5 (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)
38. 169.930 509.790 ↑ 1.0 1 84,965

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

39. 169.917 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)
40. 0.013 0.013 ↑ 103.0 1 1

Seq Scan on ft_er_deductions fedp (cost=0.00..3.03 rows=103 width=4) (actual time=0.013..0.013 rows=1 loops=1)

41. 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
42. 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