explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZS3I

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 211,629.048 ↑ 4.0 1 1

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

2. 75.241 211,629.022 ↑ 4.0 1 1

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

  • Join Filter: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
  • Rows Removed by Join Filter: 1,007,588
3. 0.003 203,069.600 ↑ 4.0 1 1

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

4. 0.004 203,069.595 ↑ 4.0 1 1

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

5. 0.004 203,068.236 ↑ 4.0 1 1

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

6. 0.005 203,068.202 ↑ 4.0 1 1

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

7. 0.004 203,067.153 ↑ 4.0 1 1

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

8. 0.004 203,065.824 ↑ 4.0 1 1

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

9. 571.740 203,065.811 ↑ 4.0 1 1

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

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
10. 0.037 3,300.517 ↑ 4.0 1 1

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

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
11. 0.011 3,300.480 ↑ 4.0 1 1

Nested Loop (cost=3.27..587,546.25 rows=4 width=356) (actual time=3,122.218..3,300.480 rows=1 loops=1)

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

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

  • Filter: ((center_name)::text = 'NMC AlNahda'::text)
  • Rows Removed by Filter: 37
13. 0.006 3,300.411 ↑ 5.2 30 1

Nested Loop Left Join (cost=3.27..587,538.84 rows=155 width=344) (actual time=3,121.819..3,300.411 rows=30 loops=1)

14. 0.026 3,299.835 ↑ 5.2 30 1

Nested Loop Left Join (cost=1.56..586,970.50 rows=155 width=328) (actual time=3,121.755..3,299.835 rows=30 loops=1)

15. 0.973 3,299.719 ↑ 5.2 30 1

Nested Loop Left Join (cost=1.28..586,896.59 rows=155 width=315) (actual time=3,121.736..3,299.719 rows=30 loops=1)

  • Join Filter: ((stpa.tpa_id)::text = (pr.secondary_sponsor_id)::text)
  • Rows Removed by Join Filter: 16,710
16. 0.569 3,297.966 ↑ 5.2 30 1

Nested Loop Left Join (cost=1.28..585,398.28 rows=155 width=291) (actual time=3,121.526..3,297.966 rows=30 loops=1)

  • Join Filter: ((ptpa.tpa_id)::text = (pr.primary_sponsor_id)::text)
  • Rows Removed by Join Filter: 7,474
17. 0.002 3,296.947 ↑ 5.2 30 1

Nested Loop Left Join (cost=1.28..583,899.97 rows=155 width=277) (actual time=3,121.293..3,296.947 rows=30 loops=1)

  • Join Filter: (vn.visit_type = pr.visit_type)
  • Rows Removed by Join Filter: 28
18. 0.021 3,296.915 ↑ 5.2 30 1

Nested Loop Left Join (cost=1.28..583,891.02 rows=155 width=247) (actual time=3,121.277..3,296.915 rows=30 loops=1)

19. 0.004 3,296.654 ↑ 5.2 30 1

Nested Loop Left Join (cost=0.85..583,737.10 rows=155 width=214) (actual time=3,121.251..3,296.654 rows=30 loops=1)

20. 0.018 3,296.560 ↑ 5.2 30 1

Nested Loop Left Join (cost=0.43..582,464.90 rows=155 width=205) (actual time=3,121.233..3,296.560 rows=30 loops=1)

  • Join Filter: ((prc.customer_id)::text = (b.visit_id)::text)
21. 0.049 3,296.542 ↑ 5.2 30 1

Nested Loop (cost=0.43..581,800.40 rows=155 width=157) (actual time=3,121.226..3,296.542 rows=30 loops=1)

22. 3,296.253 3,296.253 ↑ 5.2 30 1

Seq Scan on bill b (cost=0.00..580,490.65 rows=155 width=92) (actual time=3,121.180..3,296.253 rows=30 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-05-01'::date) AND (date(open_date) <= '2020-06-02'::date))
  • Rows Removed by Filter: 8,210,887
23. 0.240 0.240 ↑ 1.0 1 30

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

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
24. 0.000 0.000 ↓ 0.0 0 30

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

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.090 0.090 ↓ 0.0 0 30

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.003..0.003 rows=0 loops=30)

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

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

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
28. 0.025 0.030 ↑ 2.0 2 30

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

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.005..0.005 rows=2 loops=1)

30. 0.366 0.450 ↑ 2.5 250 30

Materialize (cost=0.00..23.53 rows=635 width=34) (actual time=0.000..0.015 rows=250 loops=30)

31. 0.084 0.084 ↑ 1.0 612 1

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

32. 0.703 0.780 ↑ 1.1 557 30

Materialize (cost=0.00..23.53 rows=635 width=34) (actual time=0.000..0.026 rows=557 loops=30)

33. 0.077 0.077 ↑ 1.0 635 1

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

34. 0.090 0.090 ↑ 1.0 1 30

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

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
35. 0.030 0.570 ↑ 1.0 1 30

Nested Loop Left Join (cost=1.71..3.66 rows=1 width=48) (actual time=0.018..0.019 rows=1 loops=30)

36. 0.030 0.510 ↑ 1.0 1 30

Nested Loop Left Join (cost=1.42..2.80 rows=1 width=48) (actual time=0.016..0.017 rows=1 loops=30)

37. 0.000 0.450 ↑ 1.0 1 30

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

38. 0.030 0.300 ↑ 1.0 1 30

Nested Loop (cost=0.71..1.39 rows=1 width=20) (actual time=0.009..0.010 rows=1 loops=30)

39. 0.180 0.180 ↑ 1.0 1 30

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.006..0.006 rows=1 loops=30)

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

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

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 30
41. 0.150 0.150 ↓ 0.0 0 30

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.005..0.005 rows=0 loops=30)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
42. 0.030 0.030 ↓ 0.0 0 30

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

  • Index Cond: ((insurance_co_id)::text = (sec_plan.insurance_co)::text)
43. 0.030 0.030 ↓ 0.0 0 30

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

  • Index Cond: (sec_plan.plan_id = plan_id)
44. 1,015.189 199,193.554 ↑ 2.0 3,240,235 1

Materialize (cost=5,977,772.28..6,614,280.68 rows=6,365,084 width=43) (actual time=195,168.551..199,193.554 rows=3,240,235 loops=1)

45. 1,637.784 198,178.365 ↑ 2.0 3,240,235 1

Group (cost=5,977,772.28..6,534,717.13 rows=6,365,084 width=953) (actual time=195,168.538..198,178.365 rows=3,240,235 loops=1)

  • 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. 6,543.477 196,540.581 ↑ 2.0 3,253,428 1

Sort (cost=5,977,772.28..5,993,684.99 rows=6,365,084 width=953) (actual time=195,168.524..196,540.581 rows=3,253,428 loops=1)

  • 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
  • Sort Method: external merge Disk: 1,151,880kB
47. 6,346.069 189,997.104 ↑ 1.0 6,363,695 1

Merge Left Join (cost=2,025,953.66..2,582,516.15 rows=6,365,084 width=953) (actual time=177,173.558..189,997.104 rows=6,363,695 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
48. 2,106.749 7,595.136 ↑ 1.0 6,363,693 1

Merge Left Join (cost=291,057.71..829,949.85 rows=6,365,084 width=476) (actual time=2,506.303..7,595.136 rows=6,363,693 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
49. 2,972.608 2,972.608 ↑ 1.0 6,363,693 1

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) (actual time=0.029..2,972.608 rows=6,363,693 loops=1)

  • Heap Fetches: 6,363,709
50. 184.484 2,515.779 ↓ 136.8 94,537 1

Sort (cost=291,057.28..291,059.01 rows=691 width=476) (actual time=2,506.266..2,515.779 rows=94,537 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10,645kB
51. 50.490 2,331.295 ↓ 68.4 47,270 1

Nested Loop (cost=1.30..291,024.69 rows=691 width=476) (actual time=2.270..2,331.295 rows=47,270 loops=1)

52. 36.111 1,353.263 ↓ 62.8 48,818 1

Nested Loop (cost=0.86..290,614.80 rows=777 width=461) (actual time=2.252..1,353.263 rows=48,818 loops=1)

53. 319.904 319.904 ↓ 1.0 47,488 1

Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..10,461.62 rows=45,382 width=20) (actual time=2.218..319.904 rows=47,488 loops=1)

  • Index Cond: (priority = 2)
54. 997.248 997.248 ↑ 1.0 1 47,488

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.43..6.16 rows=1 width=481) (actual time=0.016..0.021 rows=1 loops=47,488)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
55. 927.542 927.542 ↑ 1.0 1 48,818

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.43..0.52 rows=1 width=29) (actual time=0.019..0.019 rows=1 loops=48,818)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
56. 33,738.334 176,055.899 ↓ 61.2 6,362,900 1

Sort (cost=1,734,895.95..1,735,155.93 rows=103,992 width=492) (actual time=174,667.236..176,055.899 rows=6,362,900 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 1,150,664kB
57. 1,554.085 142,317.565 ↓ 60.7 6,315,610 1

Nested Loop (cost=1.30..1,726,230.24 rows=103,992 width=492) (actual time=0.060..142,317.565 rows=6,315,610 loops=1)

58. 8,620.751 71,275.300 ↓ 59.4 6,948,818 1

Merge Join (cost=0.86..1,664,511.06 rows=116,996 width=477) (actual time=0.039..71,275.300 rows=6,948,818 loops=1)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 203,740
59. 19,691.356 19,691.356 ↑ 1.0 6,828,648 1

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) (actual time=0.021..19,691.356 rows=6,828,648 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 47,488
60. 1,428.605 42,963.193 ↑ 1.0 7,158,176 1

Materialize (cost=0.43..894,175.09 rows=7,161,067 width=481) (actual time=0.014..42,963.193 rows=7,158,176 loops=1)

61. 41,534.588 41,534.588 ↑ 1.0 7,158,176 1

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.43..876,272.42 rows=7,161,067 width=481) (actual time=0.012..41,534.588 rows=7,158,176 loops=1)

62. 69,488.180 69,488.180 ↑ 1.0 1 6,948,818

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..0.52 rows=1 width=29) (actual time=0.010..0.010 rows=1 loops=6,948,818)

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

Index Scan using insurance_category_master_pkey on insurance_category_master sicam (cost=0.29..0.67 rows=1 width=26) (actual time=0.008..0.009 rows=0 loops=1)

  • Index Cond: (category_id = sipm.category_id)
64. 1.325 1.325 ↑ 1.0 1 1

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.43..1.05 rows=1 width=17) (actual time=1.325..1.325 rows=1 loops=1)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
65. 1.044 1.044 ↑ 1.0 1 1

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=156) (actual time=1.044..1.044 rows=1 loops=1)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
66. 0.030 0.030 ↑ 1.0 1 1

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..8.45 rows=1 width=29) (actual time=0.027..0.030 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
67. 1.355 1.355 ↑ 1.0 1 1

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.43..1.14 rows=1 width=23) (actual time=1.355..1.355 rows=1 loops=1)

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

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..0.90 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: ((is_resubmission = 'N'::bpchar) AND (is_reconciliation = 'N'::bpchar))
69. 214.464 8,484.181 ↓ 5.2 1,007,588 1

Materialize (cost=192,961.65..197,840.80 rows=195,166 width=46) (actual time=7,950.688..8,484.181 rows=1,007,588 loops=1)

70. 1,572.660 8,269.717 ↓ 5.2 1,007,588 1

HashAggregate (cost=192,961.65..194,913.31 rows=195,166 width=46) (actual time=7,950.685..8,269.717 rows=1,007,588 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
71. 6,697.057 6,697.057 ↓ 1.0 1,917,847 1

Seq Scan on insurance_claim_resubmission (cost=0.00..183,372.43 rows=1,917,843 width=23) (actual time=0.736..6,697.057 rows=1,917,847 loops=1)

72. 0.008 0.008 ↓ 0.0 0 1

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..0.99 rows=1 width=17) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
Planning time : 23.537 ms
Execution time : 211,911.930 ms