explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HDeQ : Loop on

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 15,248.894 ↓ 18.0 18 1

Limit (cost=4,952.70..529,485.67 rows=1 width=844) (actual time=1,986.138..15,248.894 rows=18 loops=1)

2. 2.003 15,248.878 ↓ 18.0 18 1

Nested Loop Left Join (cost=4,952.70..529,485.64 rows=1 width=844) (actual time=1,986.136..15,248.878 rows=18 loops=1)

3. 0.138 15,233.141 ↓ 18.0 18 1

Nested Loop (cost=4,952.27..529,478.51 rows=1 width=603) (actual time=1,985.158..15,233.141 rows=18 loops=1)

4. 0.118 15,228.755 ↓ 18.0 18 1

Nested Loop Left Join (cost=4,951.84..529,474.04 rows=1 width=595) (actual time=1,984.707..15,228.755 rows=18 loops=1)

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

Nested Loop Left Join (cost=4,951.84..529,472.73 rows=1 width=579) (actual time=1,984.688..15,228.601 rows=18 loops=1)

6. 0.217 15,228.237 ↓ 18.0 18 1

Nested Loop Left Join (cost=4,951.55..529,472.40 rows=1 width=535) (actual time=1,984.628..15,228.237 rows=18 loops=1)

7. 1.127 15,227.804 ↓ 18.0 18 1

Nested Loop Left Join (cost=4,951.28..529,472.05 rows=1 width=510) (actual time=1,984.558..15,227.804 rows=18 loops=1)

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

Nested Loop Left Join (cost=4,951.28..529,405.30 rows=1 width=485) (actual time=1,984.341..15,225.381 rows=18 loops=1)

  • Join Filter: ((icrsub.resubmission_batch_id)::text = (isb.submission_batch_id)::text)
  • Rows Removed by Join Filter: 1
9. 0.225 15,212.494 ↓ 18.0 18 1

Nested Loop Left Join (cost=4,950.85..529,396.25 rows=1 width=479) (actual time=1,983.628..15,212.494 rows=18 loops=1)

10. 0.114 15,204.691 ↓ 18.0 18 1

Nested Loop Left Join (cost=4,950.43..529,391.79 rows=1 width=367) (actual time=1,982.952..15,204.691 rows=18 loops=1)

  • Join Filter: ((resub.claim_id)::text = (ic.claim_id)::text)
11. 2.893 15,191.077 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..524,443.25 rows=1 width=335) (actual time=1,969.460..15,191.077 rows=18 loops=1)

  • Join Filter: ((doc.doctor_id)::text = (pr.doctor)::text)
  • Rows Removed by Join Filter: 18,403
12. 9.230 15,184.404 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..524,219.19 rows=1 width=321) (actual time=1,969.188..15,184.404 rows=18 loops=1)

  • Join Filter: (cat.category_id = pip.plan_type_id)
  • Rows Removed by Join Filter: 90,203
13. 0.150 15,161.818 ↓ 18.0 18 1

Nested Loop Left Join (cost=1.99..523,177.00 rows=1 width=295) (actual time=1,968.417..15,161.818 rows=18 loops=1)

  • Join Filter: ((sal.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 33
14. 0.187 15,161.596 ↓ 18.0 18 1

Nested Loop (cost=1.99..523,174.69 rows=1 width=299) (actual time=1,968.391..15,161.596 rows=18 loops=1)

15. 10.391 15,160.437 ↓ 18.0 18 1

Nested Loop (cost=1.56..523,173.62 rows=1 width=246) (actual time=1,968.282..15,160.437 rows=18 loops=1)

  • Join Filter: ((ic.patient_id)::text = (pr.patient_id)::text)
16. 12.692 12,560.270 ↓ 8,519.0 8,519 1

Nested Loop (cost=1.00..523,172.62 rows=1 width=215) (actual time=1,967.872..12,560.270 rows=8,519 loops=1)

17. 12.884 10,255.698 ↓ 4,260.0 8,520 1

Nested Loop (cost=0.56..523,170.27 rows=2 width=177) (actual time=1,967.452..10,255.698 rows=8,520 loops=1)

18. 7,166.405 7,166.405 ↓ 354.4 14,177 1

Seq Scan on claim_reconciliation cr (cost=0.00..522,986.27 rows=40 width=73) (actual time=1,966.608..7,166.405 rows=14,177 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,818,799
19. 3,076.409 3,076.409 ↑ 1.0 1 14,177

Index Scan using insurance_claim_id_index on insurance_claim ic (cost=0.56..4.60 rows=1 width=104) (actual time=0.217..0.217 rows=1 loops=14,177)

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

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.43..1.15 rows=1 width=42) (actual time=0.267..0.269 rows=1 loops=8,520)

  • 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. 2,589.776 2,589.776 ↓ 0.0 0 8,519

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

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

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.06 rows=1 width=68) (actual time=0.054..0.054 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.63 rows=21 width=14) (actual time=0.003..0.004 rows=3 loops=18)

24. 13.356 13.356 ↑ 2.6 5,012 18

Seq Scan on insurance_category_master cat (cost=0.00..613.09 rows=13,203 width=26) (actual time=0.007..0.742 rows=5,012 loops=18)

25. 3.780 3.780 ↑ 2.2 1,023 18

Seq Scan on doctors doc (cost=0.00..151.75 rows=2,225 width=30) (actual time=0.004..0.210 rows=1,023 loops=18)

26. 0.018 13.500 ↓ 0.0 0 18

GroupAggregate (cost=4,948.44..4,948.48 rows=1 width=46) (actual time=0.750..0.750 rows=0 loops=18)

  • Group Key: resub.claim_id
27. 0.026 13.482 ↓ 0.0 0 18

Sort (cost=4,948.44..4,948.45 rows=1 width=23) (actual time=0.749..0.749 rows=0 loops=18)

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

Nested Loop (cost=0.85..4,948.43 rows=1 width=23) (actual time=13.456..13.456 rows=0 loops=1)

29. 1.760 1.760 ↑ 1.9 694 1

Index Scan using idx_insurance_claim_resubmission_category_type on insurance_claim_resubmission resub (cost=0.43..170.37 rows=1,305 width=23) (actual time=0.882..1.760 rows=694 loops=1)

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

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb_1 (cost=0.42..3.66 rows=1 width=9) (actual time=0.017..0.017 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. 7.578 7.578 ↑ 1.0 1 18

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

  • Index Cond: ((submission_batch_id)::text = COALESCE((max((isb_1.submission_batch_id)::text)), (cr.latest_resubmission_id)::text))
32. 12.708 12.708 ↑ 12.0 1 18

Index Scan using insurance_claim_resubmission_claim_id_index on insurance_claim_resubmission icrsub (cost=0.43..8.66 rows=12 width=29) (actual time=0.705..0.706 rows=1 loops=18)

  • Index Cond: ((cr.claim_id)::text = (claim_id)::text)
33. 1.296 1.296 ↑ 2.1 409 18

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

34. 0.216 0.216 ↑ 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.012..0.012 rows=1 loops=18)

  • Index Cond: (((tpa_id)::text = (pip.sponsor_id)::text) AND ((tpa_id)::text = 'TPAID0048'::text))
35. 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)
36. 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)

37. 4.248 4.248 ↑ 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.236..0.236 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. 13.698 13.698 ↑ 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.757..0.761 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
39.          

SubPlan (for Nested Loop Left Join)

40. 0.036 0.036 ↑ 1.0 1 18

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

Planning time : 19.599 ms
Execution time : 15,249.527 ms