explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MygW

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 4,190.864 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,758,282.78..7,426,856.28 rows=4 width=573) (actual time=4,190.864..4,190.864 rows=0 loops=1)

2. 0.000 4,190.863 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,758,282.36..7,426,852.07 rows=4 width=560) (actual time=4,190.863..4,190.863 rows=0 loops=1)

  • Join Filter: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
3. 0.001 4,190.863 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,565,320.71..7,217,789.23 rows=4 width=542) (actual time=4,190.863..4,190.863 rows=0 loops=1)

4. 0.001 4,190.862 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,565,320.29..7,217,785.64 rows=4 width=543) (actual time=4,190.862..4,190.862 rows=0 loops=1)

5. 0.000 4,190.861 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,565,319.86..7,217,781.07 rows=4 width=534) (actual time=4,190.861..4,190.861 rows=0 loops=1)

6. 0.001 4,190.861 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,565,319.43..7,217,747.23 rows=4 width=520) (actual time=4,190.861..4,190.861 rows=0 loops=1)

7. 0.000 4,190.860 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,565,319.29..7,217,746.61 rows=4 width=411) (actual time=4,190.860..4,190.860 rows=0 loops=1)

8. 0.001 4,190.860 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,565,318.86..7,217,742.39 rows=4 width=402) (actual time=4,190.860..4,190.860 rows=0 loops=1)

9. 0.002 4,190.859 ↓ 0.0 0 1

Merge Left Join (cost=6,565,318.57..7,217,739.73 rows=4 width=384) (actual time=4,190.859..4,190.859 rows=0 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
10. 0.019 4,190.857 ↓ 0.0 0 1

Sort (cost=587,546.29..587,546.30 rows=4 width=356) (actual time=4,190.857..4,190.857 rows=0 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
11. 0.005 4,190.838 ↓ 0.0 0 1

Nested Loop (cost=3.27..587,546.25 rows=4 width=356) (actual time=4,190.838..4,190.838 rows=0 loops=1)

  • Join Filter: (pr.center_id = hcm.center_id)
  • Rows Removed by Join Filter: 1
12. 0.065 0.065 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..5.47 rows=1 width=20) (actual time=0.061..0.065 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC AlNahda'::text)
  • Rows Removed by Filter: 37
13. 0.003 4,190.768 ↑ 155.0 1 1

Nested Loop Left Join (cost=3.27..587,538.84 rows=155 width=344) (actual time=3,764.778..4,190.768 rows=1 loops=1)

14. 0.004 4,190.024 ↑ 155.0 1 1

Nested Loop Left Join (cost=1.56..586,970.50 rows=155 width=328) (actual time=3,764.036..4,190.024 rows=1 loops=1)

15. 0.039 4,190.007 ↑ 155.0 1 1

Nested Loop Left Join (cost=1.28..586,896.59 rows=155 width=315) (actual time=3,764.020..4,190.007 rows=1 loops=1)

  • Join Filter: ((stpa.tpa_id)::text = (pr.secondary_sponsor_id)::text)
  • Rows Removed by Join Filter: 611
16. 0.013 4,189.797 ↑ 155.0 1 1

Nested Loop Left Join (cost=1.28..585,398.28 rows=155 width=291) (actual time=3,763.810..4,189.797 rows=1 loops=1)

  • Join Filter: ((ptpa.tpa_id)::text = (pr.primary_sponsor_id)::text)
  • Rows Removed by Join Filter: 177
17. 0.003 4,189.725 ↑ 155.0 1 1

Nested Loop Left Join (cost=1.28..583,899.97 rows=155 width=277) (actual time=3,763.738..4,189.725 rows=1 loops=1)

  • Join Filter: (vn.visit_type = pr.visit_type)
  • Rows Removed by Join Filter: 1
18. 0.008 4,189.709 ↑ 155.0 1 1

Nested Loop Left Join (cost=1.28..583,891.02 rows=155 width=247) (actual time=3,763.723..4,189.709 rows=1 loops=1)

19. 0.004 4,187.506 ↑ 155.0 1 1

Nested Loop Left Join (cost=0.85..583,737.10 rows=155 width=214) (actual time=3,761.521..4,187.506 rows=1 loops=1)

20. 0.003 4,187.485 ↑ 155.0 1 1

Nested Loop Left Join (cost=0.43..582,464.90 rows=155 width=205) (actual time=3,761.499..4,187.485 rows=1 loops=1)

  • Join Filter: ((prc.customer_id)::text = (b.visit_id)::text)
21. 0.017 4,187.475 ↑ 155.0 1 1

Nested Loop (cost=0.43..581,800.40 rows=155 width=157) (actual time=3,761.490..4,187.475 rows=1 loops=1)

22. 4,186.659 4,186.659 ↑ 155.0 1 1

Seq Scan on bill b (cost=0.00..580,490.65 rows=155 width=92) (actual time=3,760.675..4,186.659 rows=1 loops=1)

  • Filter: (is_tpa AND (status <> 'X'::bpchar) AND (CASE WHEN is_tpa THEN 'Yes'::text ELSE 'No'::text END = 'Yes'::text) AND (date(open_date) >= '2020-06-01'::date) AND (date(open_date) <= '2020-06-02'::date))
  • Rows Removed by Filter: 8,210,916
23. 0.799 0.799 ↑ 1.0 1 1

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..8.45 rows=1 width=65) (actual time=0.799..0.799 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
24. 0.004 0.007 ↓ 0.0 0 1

Materialize (cost=0.00..14.20 rows=280 width=48) (actual time=0.007..0.007 rows=0 loops=1)

25. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on store_retail_customers prc (cost=0.00..12.80 rows=280 width=48) (actual time=0.003..0.003 rows=0 loops=1)

26. 0.017 0.017 ↓ 0.0 0 1

Index Only Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..8.21 rows=1 width=9) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: (incoming_visit_id = (b.visit_id)::text)
  • Heap Fetches: 0
27. 2.195 2.195 ↑ 1.0 1 1

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.99 rows=1 width=48) (actual time=2.195..2.195 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
28. 0.008 0.013 ↑ 2.0 2 1

Materialize (cost=0.00..1.06 rows=4 width=40) (actual time=0.013..0.013 rows=2 loops=1)

29. 0.005 0.005 ↑ 2.0 2 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=40) (actual time=0.004..0.005 rows=2 loops=1)

30. 0.033 0.059 ↑ 3.6 178 1

Materialize (cost=0.00..23.53 rows=635 width=34) (actual time=0.008..0.059 rows=178 loops=1)

31. 0.026 0.026 ↑ 3.6 178 1

Seq Scan on tpa_master ptpa (cost=0.00..20.35 rows=635 width=34) (actual time=0.005..0.026 rows=178 loops=1)

32. 0.098 0.171 ↑ 1.0 612 1

Materialize (cost=0.00..23.53 rows=635 width=34) (actual time=0.005..0.171 rows=612 loops=1)

33. 0.073 0.073 ↑ 1.0 612 1

Seq Scan on tpa_master stpa (cost=0.00..20.35 rows=635 width=34) (actual time=0.002..0.073 rows=612 loops=1)

34. 0.013 0.013 ↑ 1.0 1 1

Index Scan using organization_details_pkey on organization_details od (cost=0.28..0.48 rows=1 width=29) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
35. 0.002 0.741 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.71..3.66 rows=1 width=48) (actual time=0.739..0.741 rows=1 loops=1)

36. 0.004 0.720 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.42..2.80 rows=1 width=48) (actual time=0.718..0.720 rows=1 loops=1)

37. 0.003 0.707 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.14..2.51 rows=1 width=32) (actual time=0.706..0.707 rows=1 loops=1)

38. 0.006 0.695 ↑ 1.0 1 1

Nested Loop (cost=0.71..1.39 rows=1 width=20) (actual time=0.694..0.695 rows=1 loops=1)

39. 0.676 0.676 ↑ 1.0 1 1

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.43..1.10 rows=1 width=42) (actual time=0.675..0.676 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 1
40. 0.013 0.013 ↑ 1.0 1 1

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.29 rows=1 width=10) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 1
41. 0.009 0.009 ↑ 1.0 1 1

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.43..1.11 rows=1 width=38) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
42. 0.009 0.009 ↑ 1.0 1 1

Index Scan using insurance_company_master_pkey on insurance_company_master sicm (cost=0.28..0.29 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((insurance_co_id)::text = (sec_plan.insurance_co)::text)
43. 0.019 0.019 ↑ 1.0 1 1

Index Scan using insurance_plan_main_pkey on insurance_plan_main sipm (cost=0.29..0.85 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (sec_plan.plan_id = plan_id)
44. 0.000 0.000 ↓ 0.0 0

Materialize (cost=5,977,772.28..6,614,280.68 rows=6,365,084 width=43) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Group (cost=5,977,772.28..6,534,717.13 rows=6,365,084 width=953) (never executed)

  • Group Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resubmission_type, sic.comments, sic.account_group, sip.patient_id, sic.denial_remarks, sic.submission_id_with_correction, sic.payers_reference_no, bcl_2.claim_id, sic_1.submission_batch_id, sic_1.main_visit_id, sic_1.status, sic_1.resubmission_count, sic_1.closure_type, sic_1.action_remarks, sic_1.attachment, sic_1.attachment_content_type, sic_1.resubmission_type, sic_1.comments, sic_1.account_group, sic_1.denial_remarks, sic_1.submission_id_with_correction, sic_1.payers_reference_no, sic.op_type, sic_1.op_type
46. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,977,772.28..5,993,684.99 rows=6,365,084 width=953) (never executed)

  • Sort Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resubmission_type, sic.comments, sic.account_group, sip.patient_id, sic.denial_remarks, sic.submission_id_with_correction, sic.payers_reference_no, bcl_2.claim_id, sic_1.submission_batch_id, sic_1.main_visit_id, sic_1.status, sic_1.resubmission_count, sic_1.closure_type, sic_1.action_remarks, sic_1.attachment, sic_1.attachment_content_type, sic_1.resubmission_type, sic_1.comments, sic_1.account_group, sic_1.denial_remarks, sic_1.submission_id_with_correction, sic_1.payers_reference_no, sic.op_type, sic_1.op_type
47. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=2,025,953.66..2,582,516.15 rows=6,365,084 width=953) (never executed)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
48. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=291,057.71..829,949.85 rows=6,365,084 width=476) (never executed)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
49. 0.000 0.000 ↓ 0.0 0

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..522,968.18 rows=6,365,084 width=15) (never executed)

  • Heap Fetches: 0
50. 0.000 0.000 ↓ 0.0 0

Sort (cost=291,057.28..291,059.01 rows=691 width=476) (never executed)

  • Sort Key: bcl_2.bill_no
51. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.30..291,024.69 rows=691 width=476) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..290,614.80 rows=777 width=461) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..10,461.62 rows=45,382 width=20) (never executed)

  • Index Cond: (priority = 2)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.43..6.16 rows=1 width=481) (never executed)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.43..0.52 rows=1 width=29) (never executed)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
56. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,734,895.95..1,735,155.93 rows=103,992 width=492) (never executed)

  • Sort Key: bcl_1.bill_no
57. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.30..1,726,230.24 rows=103,992 width=492) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=0.86..1,664,511.06 rows=116,996 width=477) (never executed)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.43..650,647.96 rows=6,830,753 width=20) (never executed)

  • Filter: (priority = 1)
60. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.43..894,175.09 rows=7,161,067 width=481) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.43..876,272.42 rows=7,161,067 width=481) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..0.52 rows=1 width=29) (never executed)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_category_master_pkey on insurance_category_master sicam (cost=0.29..0.67 rows=1 width=26) (never executed)

  • Index Cond: (category_id = sipm.category_id)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.43..1.05 rows=1 width=17) (never executed)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
65. 0.000 0.000 ↓ 0.0 0

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=156) (never executed)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..8.45 rows=1 width=29) (never executed)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
67. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.43..1.14 rows=1 width=23) (never executed)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
68. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..0.90 rows=1 width=17) (never executed)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: ((is_resubmission = 'N'::bpchar) AND (is_reconciliation = 'N'::bpchar))
69. 0.000 0.000 ↓ 0.0 0

Materialize (cost=192,961.65..197,840.80 rows=195,166 width=46) (never executed)

70. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=192,961.65..194,913.31 rows=195,166 width=46) (never executed)

  • Group Key: insurance_claim_resubmission.claim_id
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on insurance_claim_resubmission (cost=0.00..183,372.43 rows=1,917,843 width=23) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..0.99 rows=1 width=17) (never executed)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
Planning time : 15.562 ms
Execution time : 4,198.087 ms