explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s1Sq : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #7fni; plan #VJfQ; plan #GGkK; plan #8fqF; plan #hZkA; plan #XO3s; plan #bDfM; plan #fPWl; plan #59LB; plan #ZX1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 122.469 2,676.125 ↓ 1,805.0 1,805 1

Merge Join (cost=701,085.86..703,072.83 rows=1 width=1,818) (actual time=2,567.744..2,676.125 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
2.          

CTE t1

3. 2.223 98.036 ↑ 6.3 1,805 1

Nested Loop (cost=16.57..279,633.53 rows=11,305 width=158) (actual time=0.254..98.036 rows=1,805 loops=1)

4. 0.672 92.203 ↑ 6.3 1,805 1

Nested Loop (cost=16.57..266,646.91 rows=11,305 width=130) (actual time=0.248..92.203 rows=1,805 loops=1)

5. 0.862 86.116 ↑ 6.3 1,805 1

Nested Loop (cost=16.57..237,734.37 rows=11,305 width=132) (actual time=0.238..86.116 rows=1,805 loops=1)

6. 1.371 83.449 ↑ 6.3 1,805 1

Nested Loop (cost=16.43..235,857.04 rows=11,305 width=128) (actual time=0.233..83.449 rows=1,805 loops=1)

7. 4.463 4.463 ↑ 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.022..4.463 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.000 77.615 ↑ 1.0 1 1,805

Limit (cost=16.00..20.01 rows=1 width=12) (actual time=0.043..0.043 rows=1 loops=1,805)

9. 1.805 77.615 ↑ 1.0 1 1,805

Bitmap Heap Scan on pim_org_code (cost=16.00..20.01 rows=1 width=12) (actual time=0.043..0.043 rows=1 loops=1,805)

  • Recheck Cond: ((org_id = g.org_id) AND (type_id = 7))
  • Heap Blocks: exact=1805
10. 3.610 75.810 ↓ 0.0 0 1,805

BitmapAnd (cost=16.00..16.00 rows=1 width=0) (actual time=0.042..0.042 rows=0 loops=1,805)

11. 3.610 3.610 ↓ 1.2 7 1,805

Bitmap Index Scan on fk_pim_org_code_to_org (cost=0.00..4.46 rows=6 width=0) (actual time=0.002..0.002 rows=7 loops=1,805)

  • Index Cond: (org_id = g.org_id)
12. 68.590 68.590 ↓ 1.8 688 1,805

Bitmap Index Scan on fkc6856cb3ec5d17fa (cost=0.00..11.28 rows=382 width=0) (actual time=0.038..0.038 rows=688 loops=1,805)

  • Index Cond: (type_id = 7)
13. 1.805 1.805 ↑ 1.0 1 1,805

Index Scan using mc_case_init_goal_pk on mc_case_init_goal i (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,805)

  • Index Cond: (id = g.init_goal_id)
14. 1.805 5.415 ↑ 1.0 1 1,805

Limit (cost=0.00..2.54 rows=1 width=2) (actual time=0.003..0.003 rows=1 loops=1,805)

15. 3.610 3.610 ↑ 1.0 1 1,805

Seq Scan on mc_payment_method (cost=0.00..2.54 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=1,805)

  • Filter: (id = g.payment_method_id)
  • Rows Removed by Filter: 29
16. 1.805 3.610 ↑ 1.0 1 1,805

Limit (cost=0.00..1.10 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,805)

17. 1.805 1.805 ↑ 1.0 1 1,805

Seq Scan on mc_care_regimen (cost=0.00..1.10 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,805)

  • Filter: (id = g.care_regimen_id)
18.          

CTE t1_1

19. 229.452 925.294 ↓ 20.0 180 1

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

  • Group Key: bs.c_serv
20. 515.022 695.842 ↑ 1.9 1,308,597 1

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

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

CTE Scan on t1 t1_1 (cost=0.00..226.10 rows=11,305 width=4) (actual time=0.000..0.320 rows=1,805 loops=1)

22. 158.653 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)

23. 21.847 21.847 ↑ 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.047..21.847 rows=1,830 loops=1)

  • Filter: ((fc_opl)::text = '28'::text)
  • Rows Removed by Filter: 145835
24. 1.676 101.164 ↑ 6.3 1,805 1

Sort (cost=10,108.19..10,136.45 rows=11,305 width=1,863) (actual time=100.968..101.164 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. 99.488 99.488 ↑ 6.3 1,805 1

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

26. 1,494.004 1,527.157 ↓ 1.6 243,175 1

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

  • Sort Key: b.fc_mo, b.fc_opl, b.det, b.usl_ok
  • Sort Method: external sort Disk: 10232kB
27. 33.153 33.153 ↓ 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.004..33.153 rows=147,665 loops=1)

28.          

SubPlan (for Merge Join)

29. 925.335 925.335 ↓ 20.0 180 1

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

Planning time : 0.733 ms
Execution time : 2,678.021 ms