explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 905 : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: 1; plan #DTLq; plan #j7pc; plan #N5av; plan #R6O; plan #pAxf; plan #27bx; plan #VEa0; plan #Nh3s; plan #IN2b; plan #XXES; plan #1xCS

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 983.699 226,154.787 ↓ 4,470.0 4,470 1

Merge Join (cost=1,111.35..22,031.08 rows=1 width=1,823) (actual time=204.743..226,154.787 rows=4,470 loops=1)

  • Merge Cond: (((b.fc_mo)::text = (t1.org_code_oms)::text) AND ((b.fc_opl)::text = (t1.payment_method_code)::text))
  • Join Filter: ((t1.det = (b.det)::text) AND (t1.care_regimen_code = (b.usl_ok)::text) AND (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: 740816
2.          

CTE t1

3. 4.872 44.684 ↓ 4.5 4,470 1

Hash Join (cost=919.66..1,041.02 rows=1,001 width=128) (actual time=34.650..44.684 rows=4,470 loops=1)

  • Hash Cond: (g.init_goal_id = i.id)
4. 1.462 39.799 ↓ 4.5 4,470 1

Hash Left Join (cost=918.21..982.02 rows=1,001 width=124) (actual time=34.620..39.799 rows=4,470 loops=1)

  • Hash Cond: (g.care_regimen_id = c.id)
5. 1.717 38.329 ↓ 4.5 4,470 1

Hash Left Join (cost=917.03..967.07 rows=1,001 width=124) (actual time=34.607..38.329 rows=4,470 loops=1)

  • Hash Cond: (g.payment_method_id = p.id)
6. 1.631 36.587 ↓ 4.5 4,470 1

Merge Right Join (cost=914.06..950.34 rows=1,001 width=126) (actual time=34.554..36.587 rows=4,470 loops=1)

  • Merge Cond: (o.org_id = g.org_id)
7. 0.029 0.029 ↑ 131.3 3 1

Index Scan using fk_pim_org_code_to_org on pim_org_code o (cost=0.42..12,256.31 rows=394 width=16) (actual time=0.010..0.029 rows=3 loops=1)

  • Filter: (type_id = 7)
  • Rows Removed by Filter: 10
8. 2.473 34.927 ↓ 4.5 4,470 1

Sort (cost=913.64..916.14 rows=1,001 width=114) (actual time=34.533..34.927 rows=4,470 loops=1)

  • Sort Key: g.org_id
  • Sort Method: quicksort Memory: 1380kB
9. 32.454 32.454 ↓ 4.5 4,470 1

Index Scan using fin_bill_generate_bill_id_idx on fin_bill_generate g (cost=0.43..863.76 rows=1,001 width=114) (actual time=0.037..32.454 rows=4,470 loops=1)

  • Index Cond: (bill_id = 842)
  • Filter: ((NOT is_sifted) AND ((region_data ->> 'is_tariff_service'::text))::boolean)
  • Rows Removed by Filter: 29552
10. 0.012 0.025 ↑ 1.0 43 1

Hash (cost=2.43..2.43 rows=43 width=6) (actual time=0.025..0.025 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.005..0.013 rows=43 loops=1)

12. 0.003 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.005 0.005 ↑ 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.005 rows=8 loops=1)

14. 0.006 0.013 ↑ 1.0 20 1

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

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

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

16. 49.769 49.769 ↑ 1.6 94,742 1

Index Scan using billing_tariff_services_fc_mo_fc_opl_idx on billing_tariff_services b (cost=0.42..17,405.65 rows=147,665 width=49) (actual time=0.016..49.769 rows=94,742 loops=1)

17. 60.827 110.383 ↓ 744.4 745,162 1

Sort (cost=69.91..72.41 rows=1,001 width=1,867) (actual time=52.592..110.383 rows=745,162 loops=1)

  • Sort Key: t1.org_code_oms, t1.payment_method_code
  • Sort Method: quicksort Memory: 821kB
18. 49.556 49.556 ↓ 4.5 4,470 1

CTE Scan on t1 (cost=0.00..20.02 rows=1,001 width=1,867) (actual time=34.655..49.556 rows=4,470 loops=1)

19.          

SubPlan (for Merge Join)

20. 225,010.936 225,010.936 ↓ 3.6 725 3,988

Seq Scan on billing_tariff_services (cost=0.00..5,293.14 rows=200 width=4) (actual time=26.435..56.422 rows=725 loops=3,988)

  • Filter: ((t1.case_open_date >= COALESCE(date_b, '-infinity'::date)) AND (t1.case_open_date <= COALESCE(date_e, '-infinity'::date)) AND ((fc_opl)::text = '28'::text))
  • Rows Removed by Filter: 146940
Planning time : 1.190 ms
Execution time : 226,156.998 ms