explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 26.370 350.318 ↑ 107.5 1,805 1

Merge Left Join (cost=28,505.05..37,622.93 rows=194,088 width=130) (actual time=341.523..350.318 rows=1,805 loops=1)

  • Merge Cond: (((p.code)::text = (bs.fc_opl)::text) AND (((g.org_id)::text) = (bs.fc_mo)::text))
2. 1.134 8.282 ↑ 6.3 1,805 1

Sort (cost=11,642.65..11,670.92 rows=11,305 width=130) (actual time=8.082..8.282 rows=1,805 loops=1)

  • Sort Key: p.code, ((g.org_id)::text)
  • Sort Method: quicksort Memory: 529kB
3. 0.536 7.148 ↑ 6.3 1,805 1

Hash Left Join (cost=10,354.96..10,881.56 rows=11,305 width=130) (actual time=4.970..7.148 rows=1,805 loops=1)

  • Hash Cond: (g.care_regimen_id = c.id)
4. 0.393 6.607 ↑ 6.3 1,805 1

Hash Join (cost=10,353.78..10,724.94 rows=11,305 width=130) (actual time=4.959..6.607 rows=1,805 loops=1)

  • Hash Cond: (g.payment_method_id = p.id)
5. 0.408 6.195 ↑ 6.3 1,805 1

Hash Join (cost=10,350.81..10,566.53 rows=11,305 width=132) (actual time=4.935..6.195 rows=1,805 loops=1)

  • Hash Cond: (g.init_goal_id = i.id)
6. 0.567 5.780 ↑ 6.3 1,805 1

Merge Left Join (cost=10,349.36..10,409.64 rows=11,305 width=128) (actual time=4.924..5.780 rows=1,805 loops=1)

  • Merge Cond: (g.org_id = o.org_id)
7. 0.604 4.773 ↑ 6.3 1,805 1

Sort (cost=10,173.33..10,201.60 rows=11,305 width=116) (actual time=4.594..4.773 rows=1,805 loops=1)

  • Sort Key: g.org_id
  • Sort Method: quicksort Memory: 528kB
8. 4.169 4.169 ↑ 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.020..4.169 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
9. 0.218 0.440 ↓ 4.7 1,807 1

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

  • Sort Key: o.org_id
  • Sort Method: quicksort Memory: 57kB
10. 0.222 0.222 ↓ 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.011..0.222 rows=688 loops=1)

  • Index Cond: (type_id = 7)
11. 0.003 0.007 ↑ 1.0 20 1

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

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

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

13. 0.008 0.019 ↑ 1.0 43 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.011 0.011 ↑ 1.0 43 1

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

15. 0.002 0.005 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.003 0.003 ↑ 1.0 8 1

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

17. 293.520 315.666 ↑ 1.0 146,950 1

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

  • Sort Key: bs.fc_opl, bs.fc_mo
  • Sort Method: quicksort Memory: 12384kB
18. 22.146 22.146 ↓ 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.003..22.146 rows=147,665 loops=1)

Planning time : 0.797 ms
Execution time : 350.472 ms