explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8fqF : Optimization for: Optimization for: Optimization for: plan #7fni; plan #VJfQ; plan #GGkK

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.191 109.305 ↑ 107.5 1,805 1

Merge Left Join (cost=27,683.68..37,334.27 rows=194,088 width=130) (actual time=107.229..109.305 rows=1,805 loops=1)

  • Merge Cond: (g.org_id = o.org_id)
2. 0.545 107.558 ↑ 107.5 1,805 1

Merge Left Join (cost=27,507.65..32,015.95 rows=194,088 width=118) (actual time=106.802..107.558 rows=1,805 loops=1)

  • Merge Cond: ((g.org_id = ((bs.fc_mo)::integer)) AND ((p.code)::text = (bs.fc_opl)::text))
3. 1.479 9.015 ↑ 6.3 1,805 1

Sort (cost=10,645.26..10,673.52 rows=11,305 width=118) (actual time=8.799..9.015 rows=1,805 loops=1)

  • Sort Key: g.org_id, p.code
  • Sort Method: quicksort Memory: 528kB
4. 0.723 7.536 ↑ 6.3 1,805 1

Hash Left Join (cost=6.03..9,884.17 rows=11,305 width=118) (actual time=0.114..7.536 rows=1,805 loops=1)

  • Hash Cond: (g.care_regimen_id = c.id)
5. 0.697 6.805 ↑ 6.3 1,805 1

Hash Join (cost=4.85..9,727.55 rows=11,305 width=118) (actual time=0.100..6.805 rows=1,805 loops=1)

  • Hash Cond: (g.payment_method_id = p.id)
6. 0.741 6.078 ↑ 6.3 1,805 1

Hash Join (cost=1.88..9,569.14 rows=11,305 width=120) (actual time=0.065..6.078 rows=1,805 loops=1)

  • Hash Cond: (g.init_goal_id = i.id)
7. 5.317 5.317 ↑ 6.3 1,805 1

Index Scan using fin_bill_generate_bill_id_idx on fin_bill_generate g (cost=0.43..9,412.24 rows=11,305 width=116) (actual time=0.037..5.317 rows=1,805 loops=1)

  • Index Cond: (bill_id = 791)
  • Filter: ((NOT is_sifted) AND ((region_data ->> 'is_tariff_service'::text))::boolean)
  • Rows Removed by Filter: 2344
8. 0.012 0.020 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.020..0.020 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.008 0.008 ↑ 1.0 20 1

Seq Scan on mc_case_init_goal i (cost=0.00..1.20 rows=20 width=8) (actual time=0.003..0.008 rows=20 loops=1)

10. 0.017 0.030 ↑ 1.0 43 1

Hash (cost=2.43..2.43 rows=43 width=6) (actual time=0.030..0.030 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 0.013 0.013 ↑ 1.0 43 1

Seq Scan on mc_payment_method p (cost=0.00..2.43 rows=43 width=6) (actual time=0.003..0.013 rows=43 loops=1)

12. 0.002 0.008 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=8) (actual time=0.008..0.008 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.006 0.006 ↑ 1.0 8 1

Seq Scan on mc_care_regimen c (cost=0.00..1.08 rows=8 width=8) (actual time=0.003..0.006 rows=8 loops=1)

14. 52.844 97.998 ↑ 147,648.0 1 1

Sort (cost=16,862.39..17,231.51 rows=147,648 width=9) (actual time=97.998..97.998 rows=1 loops=1)

  • Sort Key: ((bs.fc_mo)::integer), bs.fc_opl
  • Sort Method: quicksort Memory: 12384kB
15. 45.154 45.154 ↓ 1.0 147,665 1

Seq Scan on billing_tariff_services bs (cost=0.00..4,185.48 rows=147,648 width=9) (actual time=0.005..45.154 rows=147,665 loops=1)

16. 0.270 0.556 ↓ 4.7 1,807 1

Sort (cost=176.03..176.98 rows=382 width=16) (actual time=0.411..0.556 rows=1,807 loops=1)

  • Sort Key: o.org_id
  • Sort Method: quicksort Memory: 57kB
17. 0.286 0.286 ↓ 1.8 688 1

Index Scan using fkc6856cb3ec5d17fa on pim_org_code o (cost=0.42..159.64 rows=382 width=16) (actual time=0.022..0.286 rows=688 loops=1)

  • Index Cond: (type_id = 7)
Planning time : 1.424 ms
Execution time : 109.534 ms