explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 231.741 2,517.605 ↓ 36,100.0 324,900 1

Nested Loop (cost=432,602.39..434,589.91 rows=9 width=1,818) (actual time=2,149.991..2,517.605 rows=324,900 loops=1)

2.          

CTE t1

3. 1.146 9.461 ↑ 6.3 1,805 1

Hash Left Join (cost=10,354.96..11,150.06 rows=11,305 width=130) (actual time=6.149..9.461 rows=1,805 loops=1)

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

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

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

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

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

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

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

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

  • Sort Key: g.org_id
  • Sort Method: quicksort Memory: 528kB
8. 5.110 5.110 ↑ 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.026..5.110 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.244 0.513 ↓ 4.7 1,807 1

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

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

  • Index Cond: (type_id = 7)
11. 0.004 0.009 ↑ 1.0 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.005 0.005 ↑ 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.005 rows=20 loops=1)

13. 0.012 0.024 ↑ 1.0 43 1

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

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

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

15. 0.003 0.007 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.004 0.004 ↑ 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.004 rows=8 loops=1)

17.          

CTE t1_1

18. 221.466 909.241 ↓ 20.0 180 1

HashAggregate (cost=394,481.46..394,481.55 rows=9 width=4) (actual time=909.216..909.241 rows=180 loops=1)

  • Group Key: bs.c_serv
19. 506.978 687.775 ↑ 1.9 1,308,597 1

Nested Loop (cost=0.00..388,392.46 rows=2,435,599 width=4) (actual time=10.254..687.775 rows=1,308,597 loops=1)

  • Join Filter: ((t1_2.case_open_date >= COALESCE(bs.date_b, '-infinity'::date)) AND (t1_2.case_open_date <= COALESCE(bs.date_e, '-infinity'::date)))
  • Rows Removed by Join Filter: 1994553
20. 0.297 0.297 ↑ 6.3 1,805 1

CTE Scan on t1 t1_2 (cost=0.00..226.10 rows=11,305 width=4) (actual time=0.001..0.297 rows=1,805 loops=1)

21. 160.211 180.500 ↑ 1.1 1,830 1,805

Materialize (cost=0.00..4,564.30 rows=1,939 width=12) (actual time=0.000..0.100 rows=1,830 loops=1,805)

22. 20.289 20.289 ↑ 1.1 1,830 1

Seq Scan on billing_tariff_services bs (cost=0.00..4,554.60 rows=1,939 width=12) (actual time=0.044..20.289 rows=1,830 loops=1)

  • Filter: ((fc_opl)::text = '28'::text)
  • Rows Removed by Filter: 145835
23. 118.801 1,350.807 ↓ 1,805.0 1,805 1

Merge Join (cost=26,970.58..28,957.52 rows=1 width=1,818) (actual time=1,240.328..1,350.807 rows=1,805 loops=1)

  • Merge Cond: (((t1.org_code_oms)::text = (b.fc_mo)::text) AND ((t1.payment_method_code)::text = (b.fc_opl)::text) AND (t1.det = (b.det)::text) AND (t1.care_regimen_code = (b.usl_ok)::text))
  • Join Filter: ((daterange(b.date_b, b.date_e, '[]'::text) @> t1.case_close_date) AND CASE t1.init_goal_id WHEN 19 THEN ((b.c_serv)::text = '1021'::text) ELSE ((b.c_serv)::text = (t1.service_code)::text) END)
  • Rows Removed by Join Filter: 147107
24. 1.700 12.469 ↑ 6.3 1,805 1

Sort (cost=10,108.19..10,136.45 rows=11,305 width=1,863) (actual time=12.243..12.469 rows=1,805 loops=1)

  • Sort Key: t1.org_code_oms, t1.payment_method_code, t1.det, t1.care_regimen_code
  • Sort Method: quicksort Memory: 196kB
25. 10.769 10.769 ↑ 6.3 1,805 1

CTE Scan on t1 (cost=0.00..226.10 rows=11,305 width=1,863) (actual time=6.153..10.769 rows=1,805 loops=1)

26. 1,182.198 1,219.537 ↓ 1.6 243,175 1

Sort (cost=16,862.39..17,231.51 rows=147,648 width=48) (actual time=1,176.029..1,219.537 rows=243,175 loops=1)

  • Sort Key: b.fc_mo, b.fc_opl, b.det, b.usl_ok
  • Sort Method: external sort Disk: 10136kB
27. 37.339 37.339 ↓ 1.0 147,665 1

Seq Scan on billing_tariff_services b (cost=0.00..4,185.48 rows=147,648 width=48) (actual time=0.003..37.339 rows=147,665 loops=1)

28. 934.990 934.990 ↓ 20.0 180 1,805

CTE Scan on t1_1 (cost=0.00..0.18 rows=9 width=0) (actual time=0.504..0.518 rows=180 loops=1,805)

29.          

SubPlan (for Nested Loop)

30. 0.067 0.067 ↓ 20.0 180 1

CTE Scan on t1_1 t1_1_1 (cost=0.00..0.18 rows=9 width=82) (actual time=0.001..0.067 rows=180 loops=1)

Planning time : 1.482 ms
Execution time : 2,532.096 ms