explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QwSn

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 9,387.190 ↑ 1.0 1 1

Aggregate (cost=16,666,939.88..16,666,939.89 rows=1 width=0) (actual time=9,387.190..9,387.190 rows=1 loops=1)

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
2. 0.001 9,387.188 ↓ 0.0 0 1

Nested Loop Left Join (cost=15,936,057.17..16,666,928.69 rows=895 width=360) (actual time=9,387.188..9,387.188 rows=0 loops=1)

3. 0.000 9,387.187 ↓ 0.0 0 1

Nested Loop Left Join (cost=15,936,056.75..16,666,442.59 rows=895 width=384) (actual time=9,387.187..9,387.187 rows=0 loops=1)

4. 0.000 9,387.187 ↓ 0.0 0 1

Merge Left Join (cost=15,936,056.33..16,666,038.77 rows=895 width=385) (actual time=9,387.187..9,387.187 rows=0 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
5. 0.002 9,387.187 ↓ 0.0 0 1

Merge Left Join (cost=4,466,247.90..4,905,520.61 rows=895 width=371) (actual time=9,387.187..9,387.187 rows=0 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
6. 6,268.777 9,387.185 ↓ 0.0 0 1

Sort (cost=450,914.47..450,916.71 rows=895 width=343) (actual time=9,387.185..9,387.185 rows=0 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
  • -> Hash Left Join (cost=433496.67..450870.59 rows=895 width=343) (actual time=9387.181..9387.181 rows=0 loops=
  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Merge Right Join (cost=433495.20..450856.81 rows=895 width=347) (actual time=9387.181..9387.181 rows=
  • Merge Cond: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
  • -> GroupAggregate (cost=167571.09..179809.62 rows=409427 width=23) (actual time=6268.763..6268.763
  • -> Sort (cost=167571.09..170285.84 rows=1085901 width=23) (actual time=6268.751..6268.754 ro
  • Sort Key: insurance_claim_resubmission.claim_id
  • Sort Method: external merge Disk: 35912kB
  • -> Seq Scan on insurance_claim_resubmission (cost=0.00..58707.01 rows=1085901 width=23
  • -> Sort (cost=265924.11..265926.35 rows=895 width=329) (actual time=3118.415..3118.415 rows=0 loop
  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 25kB
7. 0.002 3,118.408 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,993.01..265,353.22 rows=895 width=320) (actual time=3,118.408..3,118.408 rows=0 loops=1)

8. 0.000 3,118.406 ↓ 0.0 0 1

Hash Left Join (cost=9,992.58..259,163.34 rows=895 width=306) (actual time=3,118.406..3,118.406 rows=0 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
9. 0.001 3,118.406 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,991.49..259,149.94 rows=895 width=304) (actual time=3,118.406..3,118.406 rows=0 loops=1)

10. 0.001 3,118.405 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,989.92..257,432.61 rows=895 width=308) (actual time=3,118.405..3,118.405 rows=0 loops=1)

11. 0.001 3,118.404 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,989.65..257,161.85 rows=895 width=285) (actual time=3,118.404..3,118.404 rows=0 loops=1)

12. 0.001 3,118.403 ↓ 0.0 0 1

Nested Loop Left Join (cost=9,989.37..256,891.09 rows=895 width=272) (actual time=3,118.403..3,118.403 rows=0 loops=1)

13. 22.738 3,118.402 ↓ 0.0 0 1

Nested Loop (cost=9,989.09..256,618.09 rows=895 width=259) (actual time=3,118.402..3,118.402 rows=0 loops=1)

  • Join Filter: (pr.center_id = hcm.center_id)
14. 0.000 3,095.664 ↓ 6.6 135,381 1

Nested Loop Left Join (cost=9,989.09..256,304.97 rows=20,589 width=251) (actual time=234.262..3,095.664 rows=135,381 loops=1)

15. 172.815 1,881.138 ↓ 6.6 135,381 1

Hash Left Join (cost=9,988.66..245,202.76 rows=20,589 width=218) (actual time=234.221..1,881.138 rows=135,381 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
16. 31.862 1,508.974 ↓ 6.6 135,381 1

Hash Left Join (cost=602.26..235,574.68 rows=20,589 width=209) (actual time=34.838..1,508.974 rows=135,381 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
17. 83.601 1,477.111 ↓ 6.6 135,381 1

Nested Loop (cost=585.96..235,481.16 rows=20,589 width=161) (actual time=34.826..1,477.111 rows=135,381 loops=1)

18. 286.578 310.462 ↓ 6.6 135,381 1

Bitmap Heap Scan on bill b (cost=585.52..81,042.74 rows=20,589 width=92) (actual time=34.792..310.462 rows=135,381 loops=1)

  • Recheck Cond: ((date(open_date) >= '2017-12-01'::date) AND (date(open_date) <= '2017-12-31'::date))
  • Rows Removed by Filter: 60906
19. 23.884 23.884 ↓ 7.1 196,287 1

Bitmap Index Scan on idx_bill_open_date (cost=0.00..580.38 rows=27,594 width=0) (actual time=23.884..23.884 rows=196,287 loops=1)

  • Index Cond: ((date(open_date) >= '2017-12-01'::date) AND (date(open_date) <= '2017-12-31'::date))
20. 1,083.048 1,083.048 ↑ 1.0 1 135,381

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..7.49 rows=1 width=69) (actual time=0.008..0.008 rows=1 loops=135,381)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
21. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=12.80..12.80 rows=280 width=48) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
22. 0.001 0.001 ↓ 0.0 0 1

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

23. 45.627 199.349 ↑ 1.0 225,307 1

Hash (cost=6,570.07..6,570.07 rows=225,307 width=9) (actual time=199.349..199.349 rows=225,307 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 9022kB
24. 153.722 153.722 ↑ 1.0 225,307 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6,570.07 rows=225,307 width=9) (actual time=0.003..153.722 rows=225,307 loops=1)

25. 1,218.429 1,218.429 ↑ 1.0 1 135,381

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.53 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=135,381)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
26. 0.000 0.000 ↓ 0.0 0 135,381

Materialize (cost=0.00..4.29 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=135,381)

27. 0.017 0.017 ↓ 0.0 0 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.29 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=1)

  • Filter: ((center_name)::text = 'Brightpoint'::text)
  • Rows Removed by Filter: 23
28. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_details_pkey on organization_details od (cost=0.28..0.30 rows=1 width=29) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using tpa_master_pkey on tpa_master ptpa (cost=0.28..0.29 rows=1 width=33) (never executed)

  • Index Cond: ((tpa_id)::text = (pr.primary_sponsor_id)::text)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using tpa_master_pkey on tpa_master stpa (cost=0.28..0.29 rows=1 width=33) (never executed)

  • Index Cond: ((tpa_id)::text = (pr.secondary_sponsor_id)::text)
31. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.57..1.91 rows=1 width=28) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.14..1.40 rows=1 width=20) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..0.84 rows=1 width=20) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.43..0.54 rows=1 width=42) (never executed)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
35. 0.000 0.000 ↓ 0.0 0

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.30 rows=1 width=10) (never executed)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.43..0.54 rows=1 width=38) (never executed)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
37. 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..0.50 rows=1 width=16) (never executed)

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

Hash (cost=1.04..1.04 rows=4 width=6) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=6) (never executed)

40. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Hash (cost=1.21..1.21 rows=21 width=14) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4,015,333.42..4,443,881.02 rows=4,285,476 width=43) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Group (cost=4,015,333.42..4,390,312.57 rows=4,285,476 width=943) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Sort (cost=4,015,333.42..4,026,047.11 rows=4,285,476 width=943) (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=1,544,317.24..1,770,901.30 rows=4,285,476 width=943) (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=280,437.80..495,235.87 rows=4,285,476 width=471) (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..204,074.57 rows=4,285,476 width=15) (never executed)

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

Sort (cost=280,437.36..280,439.02 rows=664 width=471) (never executed)

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

Nested Loop (cost=1.30..280,406.24 rows=664 width=471) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..280,006.85 rows=742 width=456) (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..12,074.79 rows=43,527 width=20) (never executed)

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.15 rows=1 width=476) (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.53 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,263,879.45..1,264,053.23 rows=69,512 width=487) (never executed)

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

Nested Loop (cost=1.30..1,258,288.95 rows=69,512 width=487) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=0.86..1,216,498.13 rows=77,640 width=472) (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..495,082.29 rows=4,554,381 width=20) (never executed)

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

Materialize (cost=0.43..640,199.80 rows=4,786,594 width=476) (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..628,233.31 rows=4,786,594 width=476) (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.53 rows=1 width=29) (never executed)

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

Materialize (cost=11,469,808.43..11,755,667.71 rows=1,938,029 width=28) (never executed)

64. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=11,469,808.43..11,731,442.35 rows=1,938,029 width=38) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Sort (cost=11,469,808.43..11,518,259.16 rows=19,380,290 width=38) (never executed)

  • Sort Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
66. 0.000 0.000 ↓ 0.0 0

Subquery Scan on all_insurance_remittance_details_view (cost=8,351,824.59..8,594,078.22 rows=19,380,290 width=38) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Sort (cost=8,351,824.59..8,400,275.32 rows=19,380,290 width=44) (never executed)

  • Sort Key: bill_charge.bill_no
68. 0.000 0.000 ↓ 0.0 0

Append (cost=2,108,745.99..5,409,853.38 rows=19,380,290 width=44) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2,108,745.99..4,376,900.99 rows=18,878,700 width=44) (never executed)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
70. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2,102,088.65..3,898,276.15 rows=18,878,700 width=28) (never executed)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on insurance_payment_allocation (cost=0.00..570,264.00 rows=18,878,700 width=24) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,615,965.62..1,615,965.62 rows=26,478,162 width=24) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Seq Scan on bill_charge (cost=0.00..1,615,965.62 rows=26,478,162 width=24) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Hash (cost=4,709.93..4,709.93 rows=155,793 width=20) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Seq Scan on insurance_remittance ir (cost=0.00..4,709.93 rows=155,793 width=20) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=254,356.59..617,502.61 rows=167,197 width=44) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=254,356.03..415,143.20 rows=167,197 width=40) (never executed)

  • Merge Cond: (ipu.remittance_id = ipa.remittance_id)
78. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=21.22..728.31 rows=301 width=24) (never executed)

  • Merge Cond: (ir_1.remittance_id = ipu.remittance_id)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.42..7,293.18 rows=155,793 width=14) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Sort (cost=20.40..21.15 rows=301 width=10) (never executed)

  • Sort Key: ipu.remittance_id
81. 0.000 0.000 ↓ 0.0 0

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..8.01 rows=301 width=10) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=254,334.81..1,351,986.00 rows=9,439,350 width=24) (never executed)

  • Filter: (NOT (hashed SubPlan 1))
83.          

SubPlan (forIndex Scan)

84. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.00..253,498.39 rows=334,393 width=4) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.44..47,808.20 rows=334,393 width=14) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..8.01 rows=301 width=4) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.44..147.14 rows=1,166 width=14) (never executed)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
88. 0.000 0.000 ↓ 0.0 0

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.61 rows=1 width=10) (never executed)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 0
89. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..1.20 rows=1 width=24) (never executed)

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
90. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=22.22..221,646.88 rows=334,393 width=42) (never executed)

91. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=21.65..15,956.69 rows=334,393 width=52) (never executed)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
92. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=21.22..728.31 rows=301 width=46) (never executed)

  • Merge Cond: (ir_2.remittance_id = ipu_1.remittance_id)
93. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..7,293.18 rows=155,793 width=14) (never executed)

94. 0.000 0.000 ↓ 0.0 0

Sort (cost=20.40..21.15 rows=301 width=32) (never executed)

  • Sort Key: ipu_1.remittance_id
95. 0.000 0.000 ↓ 0.0 0

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..8.01 rows=301 width=32) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.44..36.02 rows=1,166 width=14) (never executed)

  • Index Cond: (remittance_id = ir_2.remittance_id)
97. 0.000 0.000 ↓ 0.0 0

Index Only Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..0.61 rows=1 width=10) (never executed)

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 0
98. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
99. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))