explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Fxq : Sft_r_grosspay_with_Query_Variant

Settings
# exclusive inclusive rows x rows loops node
1. 386.928 7,625.455 ↑ 2.5 267,688 1

Merge Join (cost=603,184.68..714,602.87 rows=658,046 width=160) (actual time=5,464.552..7,625.455 rows=267,688 loops=1)

  • Merge Cond: ((fepy.ee_id = fenvy.ee_id) AND (fepy.ee_payslip_id = (min(fenvy.payslip_id))))
2. 949.914 6,327.579 ↑ 1.0 2,449,727 1

GroupAggregate (cost=495,524.57..557,963.57 rows=2,497,560 width=66) (actual time=4,580.743..6,327.579 rows=2,449,727 loops=1)

  • Group Key: fepy.ee_er_id, fepy.ee_id, fepy.ee_payslip_id, ferp.er_pay_name, ferp.pay_code
3. 4,003.510 5,377.665 ↑ 1.0 2,449,728 1

Sort (cost=495,524.57..501,768.47 rows=2,497,560 width=34) (actual time=4,580.729..5,377.665 rows=2,449,728 loops=1)

  • Sort Key: fepy.ee_id, fepy.ee_payslip_id, ferp.er_pay_name, ferp.pay_code
  • Sort Method: external merge Disk: 87504kB
4. 776.911 1,374.155 ↑ 1.0 2,492,516 1

Hash Join (cost=10.10..93,541.75 rows=2,497,560 width=34) (actual time=0.180..1,374.155 rows=2,492,516 loops=1)

  • Hash Cond: (fepy.ee_pay_id = ferp.er_pay_id)
5. 597.100 597.100 ↑ 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=16) (actual time=0.028..597.100 rows=2,492,516 loops=1)

  • Filter: (ee_er_id = 30)
  • Rows Removed by Filter: 42261
6. 0.068 0.144 ↑ 1.0 271 1

Hash (cost=6.71..6.71 rows=271 width=26) (actual time=0.144..0.144 rows=271 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
7. 0.076 0.076 ↑ 1.0 271 1

Seq Scan on ft_er_payments ferp (cost=0.00..6.71 rows=271 width=26) (actual time=0.009..0.076 rows=271 loops=1)

8. 147.042 910.948 ↓ 25.4 267,690 1

Sort (cost=107,660.11..107,686.46 rows=10,539 width=164) (actual time=858.830..910.948 rows=267,690 loops=1)

  • Sort Key: fenvy.ee_id, (min(fenvy.payslip_id))
  • Sort Method: external sort Disk: 6640kB
9. 33.463 763.906 ↓ 8.1 84,965 1

Hash Join (cost=105,864.00..106,955.92 rows=10,539 width=164) (actual time=514.085..763.906 rows=84,965 loops=1)

  • Hash Cond: (fenvy.ee_id = fee.ee_id)
10. 196.363 684.375 ↓ 6.0 84,965 1

GroupAggregate (cost=100,316.33..101,106.88 rows=14,254 width=144) (actual time=467.926..684.375 rows=84,965 loops=1)

  • Group Key: fenvy.ee_er_id, fenvy.ee_id, fenvy.process_date
11. 126.090 488.012 ↓ 5.4 84,965 1

Sort (cost=100,316.33..100,355.49 rows=15,661 width=43) (actual time=467.876..488.012 rows=84,965 loops=1)

  • Sort Key: fenvy.ee_id, fenvy.process_date
  • Sort Method: external merge Disk: 4512kB
12. 332.493 361.922 ↓ 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=35.576..361.922 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
13. 29.429 29.429 ↓ 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.429..29.429 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))
14. 12.511 46.068 ↓ 1.0 34,526 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2609kB
15. 33.557 33.557 ↓ 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.028..33.557 rows=34,526 loops=1)

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