explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UAE3 : Claim Recon Nested loop off

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 18,075.440 ↓ 18.0 18 1

Limit (cost=5,497.54..353,333.90 rows=1 width=844) (actual time=2,231.586..18,075.440 rows=18 loops=1)

2. 1.582 18,075.413 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,497.54..353,333.89 rows=1 width=844) (actual time=2,231.584..18,075.413 rows=18 loops=1)

3. 0.095 17,727.439 ↓ 18.0 18 1

Nested Loop (cost=5,486.65..353,318.41 rows=1 width=603) (actual time=2,164.741..17,727.439 rows=18 loops=1)

4. 0.159 17,725.184 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,486.23..353,309.95 rows=1 width=595) (actual time=2,164.727..17,725.184 rows=18 loops=1)

  • Join Filter: (otn.op_type = pr.op_type)
  • Rows Removed by Join Filter: 15
5. 70.853 17,724.989 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,486.23..353,308.84 rows=1 width=579) (actual time=2,164.719..17,724.989 rows=18 loops=1)

  • Join Filter: (ipm.plan_id = pip.plan_id)
  • Rows Removed by Join Filter: 358,540
6. 0.116 17,600.514 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,486.23..350,510.07 rows=1 width=535) (actual time=2,160.797..17,600.514 rows=18 loops=1)

7. 1.840 17,600.128 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,485.95..350,509.76 rows=1 width=510) (actual time=2,160.786..17,600.128 rows=18 loops=1)

  • Join Filter: ((icm.insurance_co_id)::text = (pip.insurance_co)::text)
  • Rows Removed by Join Filter: 7,344
8. 0.172 17,597.082 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,485.95..350,476.91 rows=1 width=485) (actual time=2,160.673..17,597.082 rows=18 loops=1)

  • Join Filter: ((cr.claim_id)::text = (icrsub.claim_id)::text)
  • Rows Removed by Join Filter: 78
9. 0.142 17,381.198 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,485.52..350,455.73 rows=1 width=479) (actual time=2,160.646..17,381.198 rows=18 loops=1)

10. 0.078 17,378.464 ↓ 18.0 18 1

Nested Loop Left Join (cost=5,485.10..350,447.29 rows=1 width=367) (actual time=2,160.633..17,378.464 rows=18 loops=1)

  • Join Filter: ((resub.claim_id)::text = (ic.claim_id)::text)
11. 4.092 17,375.434 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..344,964.13 rows=1 width=335) (actual time=2,157.706..17,375.434 rows=18 loops=1)

  • Join Filter: ((doc.doctor_id)::text = (pr.doctor)::text)
  • Rows Removed by Join Filter: 19,730
12. 13.310 17,366.860 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..344,758.14 rows=1 width=321) (actual time=2,157.376..17,366.860 rows=18 loops=1)

  • Join Filter: (cat.category_id = pip.plan_type_id)
  • Rows Removed by Join Filter: 89,465
13. 0.174 17,340.086 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..344,244.99 rows=1 width=295) (actual time=2,155.916..17,340.086 rows=18 loops=1)

  • Join Filter: ((sal.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 33
14. 0.133 17,339.840 ↓ 18.0 18 1

Nested Loop (cost=1.99..344,243.52 rows=1 width=299) (actual time=2,155.904..17,339.840 rows=18 loops=1)

15. 7.365 17,258.545 ↓ 18.0 18 1

Nested Loop (cost=1.56..344,240.65 rows=1 width=246) (actual time=2,155.827..17,258.545 rows=18 loops=1)

  • Join Filter: ((ic.patient_id)::text = (pr.patient_id)::text)
16. 17.157 12,831.636 ↓ 9,484.0 9,484 1

Nested Loop (cost=1.00..344,238.95 rows=1 width=215) (actual time=2,155.483..12,831.636 rows=9,484 loops=1)

17. 28.832 10,926.566 ↓ 9,487.0 9,487 1

Nested Loop (cost=0.56..344,237.06 rows=1 width=177) (actual time=2,155.182..10,926.566 rows=9,487 loops=1)

18. 6,893.334 6,893.334 ↓ 4,700.0 14,100 1

Seq Scan on claim_reconciliation cr (cost=0.00..344,211.32 rows=3 width=73) (actual time=2,154.654..6,893.334 rows=14,100 loops=1)

  • Filter: ((payment_ref_all ~~* '%290440%'::text) AND ((last_bill_open_date)::date >= '2019-01-01'::date) AND ((last_bill_open_date)::date <= '2020-08-27'::date))
  • Rows Removed by Filter: 8,778,981
19. 4,004.400 4,004.400 ↑ 1.0 1 14,100

Index Scan using insurance_claim_id_index on insurance_claim ic (cost=0.56..8.58 rows=1 width=104) (actual time=0.284..0.284 rows=1 loops=14,100)

  • Index Cond: ((claim_id)::text = (cr.claim_id)::text)
  • Filter: (status = ANY ('{D,R}'::bpchar[]))
  • Rows Removed by Filter: 0
20. 1,887.913 1,887.913 ↑ 1.0 1 9,487

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.43..1.88 rows=1 width=42) (actual time=0.198..0.199 rows=1 loops=9,487)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (((sponsor_id)::text = 'TPAID0048'::text) AND (ic.plan_id = plan_id))
  • Rows Removed by Filter: 0
21. 4,419.544 4,419.544 ↓ 0.0 0 9,484

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..1.68 rows=1 width=47) (actual time=0.466..0.466 rows=0 loops=9,484)

  • Index Cond: ((patient_id)::text = (pip.patient_id)::text)
  • Filter: (center_id = 2)
  • Rows Removed by Filter: 1
22. 81.162 81.162 ↑ 1.0 1 18

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..2.87 rows=1 width=68) (actual time=4.509..4.509 rows=1 loops=18)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
23. 0.072 0.072 ↑ 7.0 3 18

Seq Scan on salutation_master sal (cost=0.00..1.21 rows=21 width=14) (actual time=0.004..0.004 rows=3 loops=18)

24. 13.464 13.464 ↑ 2.6 4,971 18

Seq Scan on insurance_category_master cat (cost=0.00..348.62 rows=13,162 width=26) (actual time=0.005..0.748 rows=4,971 loops=18)

25. 4.482 4.482 ↑ 2.0 1,097 18

Seq Scan on doctors doc (cost=0.00..178.22 rows=2,222 width=30) (actual time=0.011..0.249 rows=1,097 loops=18)

26. 0.018 2.952 ↓ 0.0 0 18

GroupAggregate (cost=5,483.11..5,483.13 rows=1 width=46) (actual time=0.164..0.164 rows=0 loops=18)

  • Group Key: resub.claim_id
27. 0.022 2.934 ↓ 0.0 0 18

Sort (cost=5,483.11..5,483.12 rows=1 width=23) (actual time=0.163..0.163 rows=0 loops=18)

  • Sort Key: resub.claim_id
  • Sort Method: quicksort Memory: 25kB
28. 0.000 2.912 ↓ 0.0 0 1

Nested Loop (cost=0.85..5,483.10 rows=1 width=23) (actual time=2.912..2.912 rows=0 loops=1)

29. 0.200 0.200 ↑ 1.0 694 1

Index Scan using idx_insurance_claim_resubmission_category_type on insurance_claim_resubmission resub (cost=0.43..92.17 rows=728 width=23) (actual time=0.021..0.200 rows=694 loops=1)

  • Index Cond: ((category_type)::text = 'RECON'::text)
30. 2.776 2.776 ↓ 0.0 0 694

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb_1 (cost=0.42..7.40 rows=1 width=9) (actual time=0.004..0.004 rows=0 loops=694)

  • Index Cond: ((submission_batch_id)::text = (resub.resubmission_batch_id)::text)
  • Filter: ((is_reconciliation = 'Y'::bpchar) AND (status = 'O'::bpchar))
  • Rows Removed by Filter: 1
31. 2.592 2.592 ↑ 1.0 1 18

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb (cost=0.42..8.44 rows=1 width=121) (actual time=0.144..0.144 rows=1 loops=18)

  • Index Cond: ((submission_batch_id)::text = COALESCE((max((isb_1.submission_batch_id)::text)), (cr.latest_resubmission_id)::text))
32. 215.712 215.712 ↑ 20.2 5 18

Index Scan using insurance_claim_resubmission_batch_id_index on insurance_claim_resubmission icrsub (cost=0.43..19.92 rows=101 width=29) (actual time=11.365..11.984 rows=5 loops=18)

  • Index Cond: ((resubmission_batch_id)::text = (isb.submission_batch_id)::text)
33. 1.206 1.206 ↑ 2.0 409 18

Seq Scan on insurance_company_master icm (cost=0.00..22.38 rows=838 width=33) (actual time=0.005..0.067 rows=409 loops=18)

34. 0.270 0.270 ↑ 1.0 1 18

Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.28..0.30 rows=1 width=35) (actual time=0.015..0.015 rows=1 loops=18)

  • Index Cond: (((tpa_id)::text = (pip.sponsor_id)::text) AND ((tpa_id)::text = 'TPAID0048'::text))
35. 53.622 53.622 ↑ 2.8 19,920 18

Seq Scan on insurance_plan_main ipm (cost=0.00..2,097.23 rows=56,123 width=48) (actual time=0.003..2.979 rows=19,920 loops=18)

36. 0.036 0.036 ↑ 2.5 2 18

Seq Scan on op_type_names otn (cost=0.00..1.05 rows=5 width=20) (actual time=0.002..0.002 rows=2 loops=18)

37. 2.160 2.160 ↑ 1.0 1 18

Index Scan using insurance_remittance_pkey on insurance_remittance ir (cost=0.42..8.45 rows=1 width=12) (actual time=0.120..0.120 rows=1 loops=18)

  • Index Cond: (remittance_id = cr.latest_remittance_id)
  • Filter: (((transaction_date)::date >= '2020-07-23'::date) AND ((transaction_date)::date <= '2020-07-23'::date))
38. 71.514 346.356 ↑ 1.0 1 18

Bitmap Heap Scan on insurance_claim_remittance icr (cost=10.89..14.91 rows=1 width=28) (actual time=19.241..19.242 rows=1 loops=18)

  • Recheck Cond: (((claim_id)::text = (cr.claim_id)::text) AND (cr.latest_remittance_id = remittance_id))
  • Heap Blocks: exact=18
39. 0.126 274.842 ↓ 0.0 0 18

BitmapAnd (cost=10.89..10.89 rows=1 width=0) (actual time=15.269..15.269 rows=0 loops=18)

40. 273.186 273.186 ↑ 1.5 2 18

Bitmap Index Scan on insurance_claim_remittance_claim_id (cost=0.00..4.46 rows=3 width=0) (actual time=15.177..15.177 rows=2 loops=18)

  • Index Cond: ((claim_id)::text = (cr.claim_id)::text)
41. 1.530 1.530 ↑ 116.5 2 18

Bitmap Index Scan on insurance_claim_remittance_remittance_id (cost=0.00..6.18 rows=233 width=0) (actual time=0.085..0.085 rows=2 loops=18)

  • Index Cond: (cr.latest_remittance_id = remittance_id)
42.          

SubPlan (for Nested Loop Left Join)

43. 0.036 0.036 ↑ 1.0 1 18

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=18)

Planning time : 23.357 ms
Execution time : 18,075.806 ms