explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lpb1 : New

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 7,524.027 ↑ 1.0 20 1

Limit (cost=4.10..33,316.87 rows=20 width=755) (actual time=5,770.675..7,524.027 rows=20 loops=1)

2. 15.283 7,523.998 ↑ 12.1 20 1

Nested Loop Left Join (cost=4.10..401,420.48 rows=241 width=755) (actual time=5,770.673..7,523.998 rows=20 loops=1)

3. 35.700 7,508.715 ↑ 12.1 20 1

Nested Loop Left Join (cost=3.67..399,735.73 rows=241 width=572) (actual time=5,769.516..7,508.715 rows=20 loops=1)

4. 0.091 7,473.015 ↑ 12.1 20 1

Nested Loop Left Join (cost=3.25..399,462.32 rows=241 width=564) (actual time=5,760.665..7,473.015 rows=20 loops=1)

  • Join Filter: (otn.op_type = pr.op_type)
  • Rows Removed by Join Filter: 43
5. 2.147 7,472.924 ↑ 12.1 20 1

Nested Loop Left Join (cost=3.25..399,445.59 rows=241 width=548) (actual time=5,760.651..7,472.924 rows=20 loops=1)

6. 3.058 7,470.777 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.96..399,371.29 rows=241 width=505) (actual time=5,760.598..7,470.777 rows=20 loops=1)

  • Join Filter: ((tpa.tpa_id)::text = (pip.sponsor_id)::text)
  • Rows Removed by Join Filter: 10957
7. 2.085 7,467.719 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.96..397,060.22 rows=241 width=481) (actual time=5,760.092..7,467.719 rows=20 loops=1)

  • Join Filter: ((icm.insurance_co_id)::text = (pip.insurance_co)::text)
  • Rows Removed by Join Filter: 8019
8. 50.227 7,465.634 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.96..394,454.56 rows=241 width=457) (actual time=5,759.885..7,465.634 rows=20 loops=1)

  • Join Filter: ((isb.is_resubmission)::text = CASE WHEN (ic.resubmission_count > 0) THEN 'Y'::text ELSE 'N'::text END)
9. 2.837 7,415.407 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.54..394,186.80 rows=241 width=335) (actual time=5,739.244..7,415.407 rows=20 loops=1)

10. 1.783 7,412.570 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.26..394,115.56 rows=241 width=321) (actual time=5,739.219..7,412.570 rows=20 loops=1)

11. 61.069 7,410.787 ↑ 12.1 20 1

Nested Loop Left Join (cost=1.98..394,042.65 rows=241 width=295) (actual time=5,739.191..7,410.787 rows=20 loops=1)

12. 7,349.718 7,349.718 ↑ 12.1 20 1

Nested Loop Left Join (cost=1.42..393,675.00 rows=241 width=273) (actual time=5,715.974..7,349.718 rows=20 loops=1)

  • Join Filter: ((sal.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 23
13. 0.079 7,349.596 ↑ 12.1 20 1

Nested Loop (cost=1.42..393,600.98 rows=241 width=277) (actual time=5,715.957..7,349.596 rows=20 loops=1)

14. 1.555 7,337.997 ↑ 12.1 20 1

Nested Loop (cost=0.99..393,335.12 rows=241 width=224) (actual time=5,714.998..7,337.997 rows=20 loops=1)

15. 107.900 6,869.612 ↑ 1.7 1,235 1

Nested Loop (cost=0.56..388,243.56 rows=2,089 width=177) (actual time=55.208..6,869.612 rows=1,235 loops=1)

16. 107.616 107.616 ↓ 4.1 123,224 1

Seq Scan on claim_reconciliation cr (cost=0.00..220,177.66 rows=30,031 width=73) (actual time=0.017..107.616 rows=123,224 loops=1)

  • Filter: (((last_bill_open_date)::date >= '2018-01-01'::date) AND ((last_bill_open_date)::date <= '2019-09-09'::date))
  • Rows Removed by Filter: 129573
17. 6,654.096 6,654.096 ↓ 0.0 0 123,224

Index Scan using insurance_claim_id_index on insurance_claim ic (cost=0.56..5.60 rows=1 width=104) (actual time=0.054..0.054 rows=0 loops=123,224)

  • Index Cond: ((claim_id)::text = (cr.claim_id)::text)
  • Filter: (status = ANY ('{D,R}'::bpchar[]))
  • Rows Removed by Filter: 1
18. 466.830 466.830 ↓ 0.0 0 1,235

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..2.44 rows=1 width=47) (actual time=0.378..0.378 rows=0 loops=1,235)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (center_id = 1)
  • Rows Removed by Filter: 1
19. 11.480 11.480 ↑ 1.0 1 20

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.10 rows=1 width=68) (actual time=0.574..0.574 rows=1 loops=20)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
20. 0.028 0.040 ↑ 10.5 2 20

Materialize (cost=0.00..1.31 rows=21 width=14) (actual time=0.001..0.002 rows=2 loops=20)

21. 0.012 0.012 ↑ 1.9 11 1

Seq Scan on salutation_master sal (cost=0.00..1.21 rows=21 width=14) (actual time=0.008..0.012 rows=11 loops=1)

22. 0.000 60.940 ↑ 1.0 1 20

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..1.52 rows=1 width=42) (actual time=3.047..3.047 rows=1 loops=20)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (plan_id = ic.plan_id)
23. 1.660 1.660 ↑ 1.0 1 20

Index Scan using insurance_category_master_pkey on insurance_category_master cat (cost=0.29..0.30 rows=1 width=26) (actual time=0.083..0.083 rows=1 loops=20)

  • Index Cond: (category_id = pip.plan_type_id)
24. 2.740 2.740 ↑ 1.0 1 20

Index Scan using doctors_pkey on doctors doc (cost=0.28..0.30 rows=1 width=30) (actual time=0.137..0.137 rows=1 loops=20)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
25. 50.060 50.060 ↑ 1.0 1 20

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb (cost=0.42..1.09 rows=1 width=131) (actual time=2.503..2.503 rows=1 loops=20)

  • Index Cond: ((submission_batch_id)::text = (cr.latest_resubmission_id)::text)
26. 0.745 0.860 ↑ 1.8 402 20

Materialize (cost=0.00..22.72 rows=715 width=32) (actual time=0.002..0.043 rows=402 loops=20)

27. 0.115 0.115 ↑ 1.6 442 1

Seq Scan on insurance_company_master icm (cost=0.00..19.15 rows=715 width=32) (actual time=0.017..0.115 rows=442 loops=1)

28. 1.024 1.320 ↑ 1.1 549 20

Materialize (cost=0.00..42.42 rows=628 width=34) (actual time=0.001..0.066 rows=549 loops=20)

29. 0.296 0.296 ↑ 1.0 626 1

Seq Scan on tpa_master tpa (cost=0.00..39.28 rows=628 width=34) (actual time=0.010..0.296 rows=626 loops=1)

30. 2.060 2.060 ↑ 1.0 1 20

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm (cost=0.29..0.31 rows=1 width=47) (actual time=0.103..0.103 rows=1 loops=20)

  • Index Cond: (plan_id = pip.plan_id)
31. 0.014 0.020 ↑ 1.7 3 20

Materialize (cost=0.00..1.07 rows=5 width=20) (actual time=0.001..0.001 rows=3 loops=20)

32. 0.006 0.006 ↑ 1.2 4 1

Seq Scan on op_type_names otn (cost=0.00..1.05 rows=5 width=20) (actual time=0.006..0.006 rows=4 loops=1)

33. 35.600 35.600 ↑ 1.0 1 20

Index Scan using insurance_remittance_pkey on insurance_remittance ir (cost=0.42..1.13 rows=1 width=12) (actual time=1.780..1.780 rows=1 loops=20)

  • Index Cond: (remittance_id = cr.latest_remittance_id)
34. 13.800 13.800 ↑ 1.0 1 20

Index Scan using insurance_claim_remittance_claim_id on insurance_claim_remittance icr (cost=0.43..6.42 rows=1 width=28) (actual time=0.464..0.690 rows=1 loops=20)

  • Index Cond: ((claim_id)::text = (cr.claim_id)::text)
  • Filter: (cr.latest_remittance_id = remittance_id)
35.          

SubPlan (forIndex Scan)

36. 0.020 0.020 ↑ 1.0 1 20

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=20)