explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u23n : Sft_r_grosspay_02

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 2,375.548 ↓ 0.0 0 1

Sort (cost=281,273.60..281,273.60 rows=1 width=377) (actual time=2,375.548..2,375.548 rows=0 loops=1)

  • Sort Key: fee.ee_surname, fee.ee_id
  • Sort Method: quicksort Memory: 25kB
2. 0.004 2,375.531 ↓ 0.0 0 1

Merge Join (cost=275,955.24..281,273.59 rows=1 width=377) (actual time=2,375.531..2,375.531 rows=0 loops=1)

  • Merge Cond: (fee.ee_id = fepy.ee_id)
  • Join Filter: ((fepy.ee_er_id = fee.ee_er_id) AND (fepy.ee_payslip_id = (min(fenvy.payslip_id))))
3. 0.055 584.353 ↑ 11,579.0 1 1

GroupAggregate (cost=105,728.88..106,249.94 rows=11,579 width=164) (actual time=584.353..584.353 rows=1 loops=1)

  • Group Key: fee.ee_id, fenvy.process_date
4. 101.037 584.298 ↑ 5,789.5 2 1

Sort (cost=105,728.88..105,757.83 rows=11,579 width=57) (actual time=584.297..584.298 rows=2 loops=1)

  • Sort Key: fee.ee_id, fenvy.process_date
  • Sort Method: external merge Disk: 5824kB
5. 60.579 483.261 ↓ 7.3 84,965 1

Hash Join (cost=10,102.63..104,947.34 rows=11,579 width=57) (actual time=82.739..483.261 rows=84,965 loops=1)

  • Hash Cond: (fenvy.ee_id = fee.ee_id)
6. 346.509 376.437 ↓ 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=37) (actual time=35.998..376.437 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. 29.928 29.928 ↓ 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=29.928..29.928 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. 13.939 46.245 ↓ 1.0 34,526 1

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

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

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 12050
10. 0.002 1,791.174 ↓ 0.0 0 1

Materialize (cost=170,226.36..174,877.76 rows=1 width=292) (actual time=1,791.174..1,791.174 rows=0 loops=1)

11. 0.003 1,791.172 ↓ 0.0 0 1

Nested Loop (cost=170,226.36..174,877.75 rows=1 width=292) (actual time=1,791.172..1,791.172 rows=0 loops=1)

  • Join Filter: ((fepy.ee_er_id = fedy.ee_er_id) AND (fepy.ee_id = fedy.ee_id) AND (fepy.ee_payslip_id = fedy.ee_payslip_id))
12. 0.005 1,791.169 ↓ 0.0 0 1

Merge Join (cost=170,213.41..174,864.75 rows=1 width=248) (actual time=1,791.169..1,791.169 rows=0 loops=1)

  • Merge Cond: ((fepy.ee_id = fepy_1.ee_id) AND (fepy.ee_payslip_id = fepy_1.ee_payslip_id))
  • Join Filter: (fepy.ee_er_id = fepy_1.ee_er_id)
13. 0.051 1,791.077 ↑ 54,721.0 1 1

GroupAggregate (cost=170,196.00..174,026.47 rows=54,721 width=204) (actual time=1,791.077..1,791.077 rows=1 loops=1)

  • Group Key: fepy.ee_er_id, fepy.ee_id, fepy.ee_payslip_id
14. 212.890 1,791.026 ↑ 10,944.2 5 1

Sort (cost=170,196.00..170,332.80 rows=54,721 width=20) (actual time=1,791.025..1,791.026 rows=5 loops=1)

  • Sort Key: fepy.ee_id, fepy.ee_payslip_id
  • Sort Method: external merge Disk: 7920kB
15. 89.047 1,578.136 ↓ 4.9 267,688 1

Hash Join (cost=98,782.03..165,889.51 rows=54,721 width=20) (actual time=1,239.616..1,578.136 rows=267,688 loops=1)

  • Hash Cond: (fepy.ee_pay_id = ferp.er_pay_id)
16. 539.538 1,488.931 ↓ 4.9 267,688 1

Hash Semi Join (cost=98,771.93..165,127.00 rows=54,721 width=20) (actual time=1,239.440..1,488.931 rows=267,688 loops=1)

  • Hash Cond: (fepy.ee_payslip_id = fenvy_1.payslip_id)
17. 577.211 577.211 ↑ 1.0 2,492,516 1

Seq Scan on ft_ee_payments_ytd fepy (cost=0.00..59,190.20 rows=2,497,560 width=20) (actual time=0.022..577.211 rows=2,492,516 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 42261
18. 26.468 372.182 ↓ 5.4 84,965 1

Hash (cost=98,574.47..98,574.47 rows=15,797 width=4) (actual time=372.182..372.182 rows=84,965 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 4012kB
19. 322.307 345.714 ↓ 5.4 84,965 1

Bitmap Heap Scan on ft_ee_net_values_ytd fenvy_1 (cost=4,268.28..98,574.47 rows=15,797 width=4) (actual time=29.451..345.714 rows=84,965 loops=1)

  • Recheck Cond: ((ee_er_id = 30) AND ((process_year)::text = ANY ('{20172018,20182019}'::text[])))
  • Filter: ((process_date >= '2017-04-06'::date) AND (process_date <= '2018-04-05'::date))
  • Rows Removed by Filter: 49450
  • Heap Blocks: exact=26844
20. 23.407 23.407 ↓ 1.0 134,469 1

Bitmap Index Scan on ft_enetvaly_u2 (cost=0.00..4,264.33 rows=131,547 width=0) (actual time=23.407..23.407 rows=134,469 loops=1)

  • Index Cond: ((ee_er_id = 30) AND ((process_year)::text = ANY ('{20172018,20182019}'::text[])))
21. 0.053 0.158 ↑ 1.0 271 1

Hash (cost=6.71..6.71 rows=271 width=8) (actual time=0.158..0.158 rows=271 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
22. 0.105 0.105 ↑ 1.0 271 1

Seq Scan on ft_er_payments ferp (cost=0.00..6.71 rows=271 width=8) (actual time=0.028..0.105 rows=271 loops=1)

23. 0.002 0.087 ↓ 0.0 0 1

Materialize (cost=17.41..17.45 rows=1 width=44) (actual time=0.087..0.087 rows=0 loops=1)

24. 0.003 0.085 ↓ 0.0 0 1

GroupAggregate (cost=17.41..17.44 rows=1 width=44) (actual time=0.085..0.085 rows=0 loops=1)

  • Group Key: fepy_1.ee_er_id, fepy_1.ee_id, fepy_1.ee_payslip_id
25. 0.017 0.082 ↓ 0.0 0 1

Sort (cost=17.41..17.41 rows=1 width=32) (actual time=0.082..0.082 rows=0 loops=1)

  • Sort Key: fepy_1.ee_id, fepy_1.ee_payslip_id
  • Sort Method: quicksort Memory: 25kB
26. 0.001 0.065 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1.62..17.40 rows=1 width=32) (actual time=0.065..0.065 rows=0 loops=1)

27. 0.012 0.064 ↓ 0.0 0 1

Hash Join (cost=1.20..8.94 rows=1 width=32) (actual time=0.064..0.064 rows=0 loops=1)

  • Hash Cond: (ferp_1.er_pay_id = fepy_1.ee_pay_id)
28. 0.032 0.032 ↑ 271.0 1 1

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

29. 0.000 0.020 ↓ 0.0 0 1

Hash (cost=1.19..1.19 rows=1 width=36) (actual time=0.020..0.020 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
30. 0.020 0.020 ↓ 0.0 0 1

Seq Scan on ft_ee_abs_payments_ytd fepy_1 (cost=0.00..1.19 rows=1 width=36) (actual time=0.020..0.020 rows=0 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 19
31. 0.000 0.000 ↓ 0.0 0

Index Scan using ft_enetvaly_p on ft_ee_net_values_ytd fenvy_2 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: (payslip_id = fepy_1.ee_payslip_id)
  • Filter: (((process_year)::text = ANY ('{20172018,20182019}'::text[])) AND (process_date >= '2017-04-06'::date) AND (process_date <= '2018-04-05'::date) AN
32. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=12.95..12.98 rows=1 width=44) (never executed)

  • Group Key: fedy.ee_er_id, fedy.ee_id, fedy.ee_payslip_id
33. 0.000 0.000 ↓ 0.0 0

Sort (cost=12.95..12.95 rows=1 width=32) (never executed)

  • Sort Key: fedy.ee_id, fedy.ee_payslip_id
34. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=1.48..12.94 rows=1 width=32) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.05..4.48 rows=1 width=32) (never executed)

  • Hash Cond: (ferd.er_ded_id = fedy.ee_ded_id)
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on ft_er_deductions ferd (cost=0.00..3.03 rows=103 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=1 width=36) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on ft_ee_abs_deductions_ytd fedy (cost=0.00..1.04 rows=1 width=36) (never executed)

  • Filter: (ee_er_id = 30)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using ft_enetvaly_p on ft_ee_net_values_ytd fenvy_3 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: (payslip_id = fedy.ee_payslip_id)
  • Filter: (((process_year)::text = ANY ('{20172018,20182019}'::text[])) AND (process_date >= '2017-04-06'::date) AND (process_date <= '2018-04-05'::date) AND (ee_er_id
Planning time : 2.870 ms
Execution time : 2,380.270 ms