explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9QPc

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 18,851.838 ↓ 18.0 18 1

Limit (cost=15.25..523,997.51 rows=1 width=843) (actual time=2,412.979..18,851.838 rows=18 loops=1)

  • Buffers: shared hit=278,920 read=22,279
2. 1.744 18,851.817 ↓ 18.0 18 1

Nested Loop Left Join (cost=15.25..523,997.48 rows=1 width=843) (actual time=2,412.977..18,851.817 rows=18 loops=1)

  • Buffers: shared hit=278,920 read=22,279
3. 0.115 18,831.497 ↓ 18.0 18 1

Nested Loop (cost=14.81..523,990.35 rows=1 width=602) (actual time=2,412.422..18,831.497 rows=18 loops=1)

  • Buffers: shared hit=278,872 read=22,237
4. 0.112 18,824.488 ↓ 18.0 18 1

Nested Loop Left Join (cost=14.39..523,985.88 rows=1 width=594) (actual time=2,412.393..18,824.488 rows=18 loops=1)

  • Join Filter: (otn.op_type = pr.op_type)
  • Rows Removed by Join Filter: 15
  • Buffers: shared hit=278,816 read=22,220
5. 0.127 18,824.340 ↓ 18.0 18 1

Nested Loop Left Join (cost=14.39..523,984.56 rows=1 width=578) (actual time=2,412.384..18,824.340 rows=18 loops=1)

  • Buffers: shared hit=278,798 read=22,220
6. 0.141 18,824.015 ↓ 18.0 18 1

Nested Loop Left Join (cost=14.10..523,984.24 rows=1 width=534) (actual time=2,412.361..18,824.015 rows=18 loops=1)

  • Buffers: shared hit=278,744 read=22,220
7. 1.175 18,823.676 ↓ 18.0 18 1

Nested Loop Left Join (cost=13.82..523,983.89 rows=1 width=509) (actual time=2,412.334..18,823.676 rows=18 loops=1)

  • Join Filter: ((icm.insurance_co_id)::text = (pip.insurance_co)::text)
  • Rows Removed by Join Filter: 7,344
  • Buffers: shared hit=278,690 read=22,220
8. 0.186 18,821.097 ↓ 18.0 18 1

Nested Loop Left Join (cost=13.82..523,917.14 rows=1 width=484) (actual time=2,412.237..18,821.097 rows=18 loops=1)

  • Join Filter: ((cr.claim_id)::text = (icrsub.claim_id)::text)
  • Rows Removed by Join Filter: 78
  • Buffers: shared hit=278,582 read=22,220
9. 0.164 18,785.847 ↓ 18.0 18 1

Nested Loop Left Join (cost=13.39..523,907.93 rows=1 width=478) (actual time=2,412.195..18,785.847 rows=18 loops=1)

  • Buffers: shared hit=278,500 read=22,150
10. 0.101 18,775.603 ↓ 18.0 18 1

Nested Loop Left Join (cost=12.97..523,903.47 rows=1 width=367) (actual time=2,412.169..18,775.603 rows=18 loops=1)

  • Join Filter: ((resub.claim_id)::text = (ic.claim_id)::text)
  • Buffers: shared hit=278,451 read=22,127
11. 3.497 18,772.154 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..523,892.39 rows=1 width=335) (actual time=2,408.840..18,772.154 rows=18 loops=1)

  • Join Filter: ((doc.doctor_id)::text = (pr.doctor)::text)
  • Rows Removed by Join Filter: 18,403
  • Buffers: shared hit=275,611 read=22,127
12. 0.000 18,764.193 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..523,668.45 rows=1 width=321) (actual time=2,408.525..18,764.193 rows=18 loops=1)

  • Join Filter: (cat.category_id = pip.plan_type_id)
  • Rows Removed by Join Filter: 90,167
  • Buffers: shared hit=274,940 read=22,127
  • -> Nested Loop Left Join (cost=1.99..522626.95 rows=1 width=295) (actual time=2,407.585..18738.213 rows=18 loop
  • Join Filter: ((sal.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 33
  • Buffers: shared hit=272,487 read=22,127
  • -> Nested Loop Left Join (cost=1.99..522626.95 rows=1 width=295) (actual time=2,407.585..18738.213 rows=18 loop
  • Join Filter: ((sal.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 33
  • Buffers: shared hit=272,487 read=22,127
13. 18,737.988 18,737.988 ↓ 18.0 18 1

Nested Loop (cost=1.99..522,624.64 rows=1 width=299) (actual time=2,407.572..18,737.988 rows=18 loops=1)

  • Buffers: shared hit=272,469 read=22,127
14. 6.880 18,737.988 ↓ 18.0 18 1

Nested Loop (cost=1.99..522,624.64 rows=1 width=299) (actual time=2,407.572..18,737.988 rows=18 loops=1)

  • Buffers: shared hit=272,469 read=22,127
  • -> Nested Loop (cost=1.56..522623.54 rows=1 width=246) (actual time=2,407.535..18737.399 rows=18 lo
  • Join Filter: ((ic.patient_id)::text = (pr.patient_id)::text)
  • Buffers: shared hit=272,397 read=22,127
15. 11.820 17,379.956 ↓ 8,442.0 8,442 1

Nested Loop (cost=1.00..522,622.54 rows=1 width=215) (actual time=2,407.488..17,379.956 rows=8,442 loops=1)

  • Buffers: shared hit=232,559 read=19,609
16. 14.681 12,108.147 ↓ 4,221.5 8,443 1

Nested Loop (cost=0.56..522,620.21 rows=2 width=177) (actual time=2,406.365..12,108.147 rows=8,443 loops=1)

  • Buffers: shared hit=209,072 read=9,242
17. 7,369.966 7,369.966 ↓ 427.3 14,100 1

Seq Scan on claim_reconciliation cr (cost=0.00..522,468.41 rows=33 width=73) (actual time=2,405.399..7,369.966 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,813,221
  • Buffers: shared hit=147,351
18. 4,723.500 4,723.500 ↑ 1.0 1 14,100

Index Scan using insurance_claim_id_index on insurance_claim ic (cost=0.56..4.60 rows=1 width=104) (actual time=0.335..0.335 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
  • Buffers: shared hit=61,721 read=9,242
19. 5,259.989 5,259.989 ↑ 1.0 1 8,443

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.43..1.14 rows=1 width=42) (actual time=0.622..0.623 rows=1 loops=8,443)

  • 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
  • Buffers: shared hit=23,487 read=10,367
20. 1,350.720 1,350.720 ↓ 0.0 0 8,442

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..0.97 rows=1 width=47) (actual time=0.160..0.160 rows=0 loops=8,442)

  • Index Cond: ((patient_id)::text = (pip.patient_id)::text)
  • Filter: (center_id = 2)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=39,838 read=2,518
21. 0.432 0.432 ↑ 1.0 1 18

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

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
  • Buffers: shared hit=72
22. 0.072 0.072 ↑ 7.0 3 18

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

  • Buffers: shared hit=18
23. 15.030 15.030 ↑ 2.6 5,010 18

Seq Scan on insurance_category_master cat (cost=0.00..612.76 rows=13,192 width=26) (actual time=0.009..0.835 rows=5,010 loops=18)

  • Buffers: shared hit=2,453
24. 4.464 4.464 ↑ 2.2 1,023 18

Seq Scan on doctors doc (cost=0.00..151.69 rows=2,223 width=30) (actual time=0.005..0.248 rows=1,023 loops=18)

  • Buffers: shared hit=671
25. 0.018 3.348 ↓ 0.0 0 18

GroupAggregate (cost=10.99..11.03 rows=1 width=46) (actual time=0.186..0.186 rows=0 loops=18)

  • Group Key: resub.claim_id
  • Buffers: shared hit=2,840
26. 0.040 3.330 ↓ 0.0 0 18

Sort (cost=10.99..10.99 rows=1 width=23) (actual time=0.185..0.185 rows=0 loops=18)

  • Sort Key: resub.claim_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,840
27. 0.225 3.290 ↓ 0.0 0 1

Nested Loop (cost=0.85..10.98 rows=1 width=23) (actual time=3.290..3.290 rows=0 loops=1)

  • Buffers: shared hit=2,840
28. 0.289 0.289 ↓ 694.0 694 1

Index Scan using idx_insurance_claim_resubmission_category_type on insurance_claim_resubmission resub (cost=0.43..2.47 rows=1 width=23) (actual time=0.031..0.289 rows=694 loops=1)

  • Index Cond: ((category_type)::text = 'RECON'::text)
  • Buffers: shared hit=64
29. 2.776 2.776 ↓ 0.0 0 694

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb_1 (cost=0.42..4.47 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
  • Buffers: shared hit=2,776
30. 10.080 10.080 ↑ 1.0 1 18

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb (cost=0.42..4.46 rows=1 width=120) (actual time=0.560..0.560 rows=1 loops=18)

  • Index Cond: ((submission_batch_id)::text = COALESCE((max((isb_1.submission_batch_id)::text)), (cr.latest_resubmission_id)::text))
  • Buffers: shared hit=49 read=23
31. 35.064 35.064 ↑ 19.4 5 18

Index Scan using insurance_claim_resubmission_batch_id_index on insurance_claim_resubmission icrsub (cost=0.43..6.05 rows=97 width=29) (actual time=0.747..1.948 rows=5 loops=18)

  • Index Cond: ((resubmission_batch_id)::text = (isb.submission_batch_id)::text)
  • Buffers: shared hit=82 read=70
32. 1.404 1.404 ↑ 2.1 409 18

Seq Scan on insurance_company_master icm (cost=0.00..39.32 rows=844 width=33) (actual time=0.010..0.078 rows=409 loops=18)

  • Buffers: shared hit=108
33. 0.198 0.198 ↑ 1.0 1 18

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

  • Index Cond: (((tpa_id)::text = (pip.sponsor_id)::text) AND ((tpa_id)::text = 'TPAID0048'::text))
  • Buffers: shared hit=54
34. 0.198 0.198 ↑ 1.0 1 18

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm (cost=0.29..0.33 rows=1 width=48) (actual time=0.011..0.011 rows=1 loops=18)

  • Index Cond: (plan_id = pip.plan_id)
  • Buffers: shared hit=54
35. 0.036 0.036 ↑ 2.5 2 18

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

  • Buffers: shared hit=18
36. 6.894 6.894 ↑ 1.0 1 18

Index Scan using insurance_remittance_pkey on insurance_remittance ir (cost=0.42..4.47 rows=1 width=12) (actual time=0.383..0.383 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))
  • Buffers: shared hit=56 read=17
37. 18.540 18.540 ↑ 1.0 1 18

Index Scan using insurance_claim_remittance_claim_id on insurance_claim_remittance icr (cost=0.43..6.51 rows=1 width=28) (actual time=0.717..1.030 rows=1 loops=18)

  • Index Cond: ((claim_id)::text = (cr.claim_id)::text)
  • Filter: (cr.latest_remittance_id = remittance_id)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=48 read=42
38.          

SubPlan (for Nested Loop Left Join)

39. 0.036 0.036 ↑ 1.0 1 18

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

Planning time : 30.915 ms
Execution time : 18,852.422 ms