explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jery : Old

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 31,369.562 ↑ 1.0 20 1

Limit (cost=4.10..33,288.60 rows=20 width=755) (actual time=24,980.147..31,369.562 rows=20 loops=1)

2. 24.345 31,369.526 ↑ 12.1 20 1

Nested Loop Left Join (cost=4.10..401,079.89 rows=241 width=755) (actual time=24,980.146..31,369.526 rows=20 loops=1)

3. 51.137 31,345.181 ↑ 12.1 20 1

Nested Loop Left Join (cost=3.67..399,394.53 rows=241 width=572) (actual time=24,975.349..31,345.181 rows=20 loops=1)

4. 0.120 31,294.044 ↑ 12.1 20 1

Nested Loop Left Join (cost=3.25..399,120.91 rows=241 width=564) (actual time=24,968.699..31,294.044 rows=20 loops=1)

  • Join Filter: (otn.op_type = pr.op_type)
  • Rows Removed by Join Filter: 43
5. 18.172 31,293.924 ↑ 12.1 20 1

Nested Loop Left Join (cost=3.25..399,104.17 rows=241 width=548) (actual time=24,968.672..31,293.924 rows=20 loops=1)

6. 6.210 31,275.752 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.96..399,029.88 rows=241 width=505) (actual time=24,968.124..31,275.752 rows=20 loops=1)

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

Nested Loop Left Join (cost=2.96..396,718.81 rows=241 width=481) (actual time=24,965.546..31,269.542 rows=20 loops=1)

  • Join Filter: ((icm.insurance_co_id)::text = (pip.insurance_co)::text)
  • Rows Removed by Join Filter: 8019
8. 120.712 31,265.811 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.96..394,113.15 rows=241 width=457) (actual time=24,964.514..31,265.811 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.035 31,145.099 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.54..393,845.19 rows=241 width=335) (actual time=24,950.547..31,145.099 rows=20 loops=1)

10. 4.205 31,143.064 ↑ 12.1 20 1

Nested Loop Left Join (cost=2.26..393,773.95 rows=241 width=321) (actual time=24,950.516..31,143.064 rows=20 loops=1)

11. 74.454 31,138.859 ↑ 12.1 20 1

Nested Loop Left Join (cost=1.98..393,701.03 rows=241 width=295) (actual time=24,949.984..31,138.859 rows=20 loops=1)

12. 31,064.405 31,064.405 ↑ 12.1 20 1

Nested Loop Left Join (cost=1.42..393,333.45 rows=241 width=273) (actual time=24,936.342..31,064.405 rows=20 loops=1)

  • Join Filter: ((sal.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 23
13. 0.133 31,064.275 ↑ 12.1 20 1

Nested Loop (cost=1.42..393,259.43 rows=241 width=277) (actual time=24,936.330..31,064.275 rows=20 loops=1)

14. 3.925 31,050.742 ↑ 12.1 20 1

Nested Loop (cost=0.99..392,993.57 rows=241 width=224) (actual time=24,935.513..31,050.742 rows=20 loops=1)

15. 149.328 29,372.157 ↑ 1.7 1,235 1

Nested Loop (cost=0.56..387,909.90 rows=2,086 width=177) (actual time=79.486..29,372.157 rows=1,235 loops=1)

16. 265.189 265.189 ↓ 4.1 123,224 1

Seq Scan on claim_reconciliation cr (cost=0.00..220,027.94 rows=29,993 width=73) (actual time=0.312..265.189 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. 28,957.640 28,957.640 ↓ 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.235..0.235 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. 1,674.660 1,674.660 ↓ 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=1.356..1.356 rows=0 loops=1,235)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (center_id = 1)
  • Rows Removed by Filter: 1
19. 13.360 13.360 ↑ 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.668..0.668 rows=1 loops=20)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
20. 0.031 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.009 0.009 ↑ 1.9 11 1

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

22. 0.000 74.320 ↑ 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.714..3.716 rows=1 loops=20)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (plan_id = ic.plan_id)
23. 4.060 4.060 ↑ 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.203..0.203 rows=1 loops=20)

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

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

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
25. 120.480 120.480 ↑ 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=6.024..6.024 rows=1 loops=20)

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

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

27. 0.910 0.910 ↑ 1.6 442 1

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

28. 1.420 3.720 ↑ 1.1 549 20

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

29. 2.300 2.300 ↑ 1.0 626 1

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

30. 18.040 18.040 ↑ 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.902..0.902 rows=1 loops=20)

  • Index Cond: (plan_id = pip.plan_id)
31. 0.027 0.040 ↑ 1.7 3 20

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

32. 0.013 0.013 ↑ 1.2 4 1

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

33. 50.980 50.980 ↑ 1.0 1 20

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

  • Index Cond: (remittance_id = cr.latest_remittance_id)
34. 18.920 18.920 ↑ 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.553..0.946 rows=1 loops=20)

  • Filter: (cr.latest_remittance_id = remittance_id)
  • Rows Removed by Filter: 1
35.          

SubPlan (forIndex Scan)

36. 0.040 0.040 ↑ 1.0 1 20

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