explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VTpJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 199,344.915 ↑ 1.0 1 1

Aggregate (cost=17,920,830.62..17,920,830.63 rows=1 width=8) (actual time=199,344.914..199,344.915 rows=1 loops=1)

2. 124,131.136 199,341.729 ↓ 77.1 47,404 1

Merge Left Join (cost=17,460,436.80..17,920,822.94 rows=615 width=1,600) (actual time=199,037.443..199,341.729 rows=47,404 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
3. 603.105 75,210.593 ↓ 77.1 47,398 1

Merge Left Join (cost=2,704,717.49..3,008,334.03 rows=615 width=14) (actual time=74,936.444..75,210.593 rows=47,398 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
4. 176.909 5,468.020 ↓ 77.1 47,398 1

Sort (cost=97,806.97..97,808.51 rows=615 width=14) (actual time=5,461.959..5,468.020 rows=47,398 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 3758kB
5. 23.085 5,291.111 ↓ 77.1 47,398 1

Gather (cost=1,402.87..97,778.48 rows=615 width=14) (actual time=46.060..5,291.111 rows=47,398 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 814.572 5,268.026 ↓ 61.7 15,799 3

Nested Loop Left Join (cost=402.87..96,716.98 rows=256 width=14) (actual time=28.938..5,268.026 rows=15,799 loops=3)

  • -> Nested Loop Left Join (cost=402.44..94806.00 rows=256 width=14) (actual time=28.650..5008.908 rows=15784 lo
7. 4,453.348 4,453.403 ↓ 61.7 15,784 3

Hash Join (cost=401.31..94,457.95 rows=256 width=30) (actual time=28.526..4,453.403 rows=15,784 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
  • -> Nested Loop (cost=399.01..94437.32 rows=5898 width=34) (actual time=28.204..4441.097 rows=51069
  • -> Parallel Bitmap Heap Scan on bill b (cost=398.58..54100.77 rows=5898 width=29) (actual ti
  • Recheck Cond: ((date(open_date) >= '2018-05-01'::date) AND (date(open_date) <= '2018-05-
  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 19261
  • Heap Blocks: exact=18362
  • -> Bitmap Index Scan on idx_bill_open_date (cost=0.00..395.04 rows=18661 width=0) (act
  • Index Cond: ((date(open_date) >= '2018-05-01'::date) AND (date(open_date) <= '2018
  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
8. 0.007 0.055 ↑ 1.0 1 3

Hash (cost=2.29..2.29 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.048 0.048 ↑ 1.0 1 3

Seq Scan on hospital_center_master hcm (cost=0.00..2.29 rows=1 width=4) (actual time=0.037..0.048 rows=1 loops=3)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 22
10. 0.001 0.035 ↑ 1.0 1 47,353

Nested Loop Left Join (cost=1.13..1.35 rows=1 width=16) (actual time=0.034..0.035 rows=1 loops=47,353)

11. 0.001 0.026 ↑ 1.0 1 47,353

Nested Loop (cost=0.70..0.81 rows=1 width=16) (actual time=0.026..0.026 rows=1 loops=47,353)

12. 0.021 0.021 ↑ 1.0 1 47,353

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.43..0.52 rows=1 width=42) (actual time=0.021..0.021 rows=1 loops=47,353)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
13. 0.004 0.004 ↑ 1.0 1 47,353

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.27..0.29 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=47,353)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 15732
14. 0.008 0.008 ↓ 0.0 0 47,353

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.43..0.53 rows=1 width=38) (actual time=0.008..0.008 rows=0 loops=47,353)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
15. 0.016 0.016 ↑ 1.0 1 47,353

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..7.45 rows=1 width=29) (actual time=0.016..0.016 rows=1 loops=47,353)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
16. 1,011.800 69,139.468 ↑ 1.0 2,929,660 1

Materialize (cost=2,606,910.52..2,903,114.02 rows=2,962,035 width=15) (actual time=64,021.245..69,139.468 rows=2,929,660 loops=1)

17. 68,127.668 68,127.668 ↑ 1.0 2,929,615 1

Group (cost=2,606,910.52..2,866,088.58 rows=2,962,035 width=977) (actual time=64,021.230..68,127.668 rows=2,929,615 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
18. 0.000 65,263.687 ↓ 1.0 2,973,174 1

Sort (cost=2,606,910.52..2,614,315.61 rows=2,962,035 width=977) (actual time=64,021.225..65,263.687 rows=2,973,174 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: 508632kB
19. 2,919.962 52,652.637 ↓ 1.0 2,973,258 1

Merge Left Join (cost=815,851.62..1,012,874.05 rows=2,962,035 width=977) (actual time=46,163.401..52,652.637 rows=2,973,258 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
20. 1,047.542 11,416.647 ↓ 1.0 2,973,215 1

Merge Left Join (cost=133,949.55..322,725.19 rows=2,962,035 width=488) (actual time=8,494.969..11,416.647 rows=2,973,215 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
21. 1,866.059 1,866.059 ↓ 1.0 2,973,215 1

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..181,358.95 rows=2,962,035 width=15) (actual time=0.529..1,866.059 rows=2,973,215 loops=1)

  • Heap Fetches: 11
22. 201.910 8,503.046 ↓ 131.7 86,950 1

Sort (cost=133,949.12..133,950.77 rows=660 width=488) (actual time=8,494.434..8,503.046 rows=86,950 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 9965kB
23. 81.606 8,301.136 ↓ 65.9 43,476 1

Gather (cost=1,001.29..133,918.21 rows=660 width=488) (actual time=3.976..8,301.136 rows=43,476 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
24. 2,935.256 8,219.530 ↓ 56.0 21,738 2

Nested Loop (cost=1.29..132,852.21 rows=388 width=488) (actual time=1.825..8,219.530 rows=21,738 loops=2)

25. 5,234.786 5,284.143 ↓ 52.5 22,145 2

Nested Loop (cost=0.86..132,596.57 rows=422 width=473) (actual time=0.915..5,284.143 rows=22,145 loops=2)

26. 49.116 49.116 ↑ 1.2 21,602 2

Parallel Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..13,465.90 rows=25,593 width=20) (actual time=0.040..49.116 rows=21,602 loops=2)

  • Index Cond: (priority = 2)
27. 0.241 0.241 ↑ 1.0 1 43,205

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.43..4.64 rows=1 width=493) (actual time=0.154..0.241 rows=1 loops=43,205)

  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
28. 0.131 0.131 ↑ 1.0 1 44,290

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.43..0.60 rows=1 width=29) (actual time=0.129..0.131 rows=1 loops=44,290)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
29. 15,130.133 38,316.028 ↓ 64.4 2,972,724 1

Sort (cost=681,902.07..682,017.50 rows=46,172 width=504) (actual time=37,668.422..38,316.028 rows=2,972,724 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 500864kB
30. 0.000 23,185.895 ↓ 63.4 2,929,183 1

Gather (cost=3,923.61..678,324.96 rows=46,172 width=504) (actual time=3.179..23,185.895 rows=2,929,183 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
31. 12,438.095 23,273.183 ↓ 50.8 976,394 3

Nested Loop (cost=2,923.61..672,707.76 rows=19,238 width=504) (actual time=4.525..23,273.183 rows=976,394 loops=3)

32. 2,187.677 10,835.076 ↓ 49.2 1,028,352 3

Merge Join (cost=2,923.18..660,054.61 rows=20,887 width=489) (actual time=4.342..10,835.076 rows=1,028,352 loops=3)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 42923
33. 2,864.815 2,864.815 ↑ 1.2 1,014,167 3

Parallel Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.43..259,277.41 rows=1,267,582 width=20) (actual time=0.245..2,864.815 rows=1,014,167 loops=3)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 14402
34. 5,782.584 5,782.584 ↑ 1.0 3,215,639 3

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.43..372,266.66 rows=3,215,845 width=493) (actual time=0.025..5,782.584 rows=3,215,639 loops=3)

35. 0.012 0.012 ↑ 1.0 1 3,085,055

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..0.60 rows=1 width=29) (actual time=0.011..0.012 rows=1 loops=3,085,055)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
36. 440.224 123,705.061 ↓ 2.0 2,540,635 1

Materialize (cost=14,755,719.31..14,909,349.98 rows=1,254,128 width=48) (actual time=122,253.045..123,705.061 rows=2,540,635 loops=1)

37. 568.240 123,264.837 ↓ 2.0 2,540,635 1

GroupAggregate (cost=14,755,719.31..14,893,673.38 rows=1,254,128 width=388) (actual time=122,253.035..123,264.837 rows=2,540,635 loops=1)

  • Group Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
38. 122,696.597 122,696.597 ↑ 4.9 2,550,033 1

Sort (cost=14,755,719.31..14,787,072.51 rows=12,541,279 width=384) (actual time=122,253.025..122,696.597 rows=2,550,033 loops=1)

  • Sort Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
  • Sort Method: external merge Disk: 110992kB
39. 258.900 112,339.299 ↑ 4.9 2,550,039 1

Subquery Scan on all_insurance_remittance_details_view (cost=10,934,172.67..11,090,938.65 rows=12,541,279 width=384) (actual time=111,617.896..112,339.299 rows=2,550,039 loops=1)

40. 9,751.505 112,080.306 ↑ 4.9 2,550,039 1

Sort (cost=10,934,172.67..10,965,525.86 rows=12,541,279 width=400) (actual time=111,617.892..112,080.306 rows=2,550,039 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 137544kB
41. 2,520.375 102,328.801 ↑ 4.9 2,550,039 1

Unique (cost=6,964,187.13..7,183,659.51 rows=12,541,279 width=400) (actual time=88,814.323..102,328.801 rows=2,550,039 loops=1)

42. 52,442.514 99,808.426 ↑ 1.0 12,176,138 1

Sort (cost=6,964,187.13..6,995,540.33 rows=12,541,279 width=400) (actual time=88,814.322..99,808.426 rows=12,176,138 loops=1)

  • Sort Key: bill_charge.bill_no, insurance_payment_allocation.payment_reference, ir.remittance_id, ir.received_date, ir.is_recovery, ir.reference_no
  • Sort Method: external merge Disk: 654616kB
43. 1,150.483 47,365.912 ↑ 1.0 12,176,138 1

Append (cost=1,389,366.23..3,213,673.98 rows=12,541,279 width=400) (actual time=22,700.360..47,365.912 rows=12,176,138 loops=1)

44. 2,250.770 41,083.699 ↑ 1.0 12,140,946 1

Hash Join (cost=1,389,366.23..2,059,031.11 rows=12,151,369 width=43) (actual time=22,700.359..41,083.699 rows=12,140,946 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
45. 13,261.098 38,800.843 ↑ 1.0 12,151,369 1

Hash Join (cost=1,385,496.98..2,023,263.02 rows=12,151,369 width=27) (actual time=22,668.167..38,800.843 rows=12,151,369 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
46. 2,922.281 2,922.281 ↑ 1.0 12,151,369 1

Seq Scan on insurance_payment_allocation (cost=0.00..359,273.69 rows=12,151,369 width=23) (actual time=0.008..2,922.281 rows=12,151,369 loops=1)

47. 4,403.567 22,617.464 ↓ 1.0 17,782,497 1

Hash (cost=1,059,020.88..1,059,020.88 rows=17,782,488 width=24) (actual time=22,617.464..22,617.464 rows=17,782,497 loops=1)

  • Buckets: 2097152 Batches: 16 Memory Usage: 78846kB
48. 18,213.897 18,213.897 ↓ 1.0 17,782,497 1

Seq Scan on bill_charge (cost=0.00..1,059,020.88 rows=17,782,488 width=24) (actual time=0.737..18,213.897 rows=17,782,497 loops=1)

49. 14.204 32.086 ↑ 1.0 90,500 1

Hash (cost=2,738.00..2,738.00 rows=90,500 width=20) (actual time=32.086..32.086 rows=90,500 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5649kB
50. 17.882 17.882 ↑ 1.0 90,500 1

Seq Scan on insurance_remittance ir (cost=0.00..2,738.00 rows=90,500 width=20) (actual time=0.015..17.882 rows=90,500 loops=1)

51. 12.391 5,131.730 ↓ 0.0 0 1

Gather (cost=271,288.40..854,538.27 rows=129,970 width=43) (actual time=5,131.665..5,131.730 rows=0 loops=1)

  • Workers Launched: 2
52. 0.001 5,119.339 ↓ 0.0 0 3

Nested Loop (cost=270,288.40..840,541.27 rows=54,154 width=43) (actual time=5,119.339..5,119.339 rows=0 loops=3)

53. 200.374 5,119.338 ↓ 0.0 0 3

Merge Join (cost=270,287.84..775,273.46 rows=54,154 width=39) (actual time=5,119.338..5,119.338 rows=0 loops=3)

  • Merge Cond: (ipa.remittance_id = ipu.remittance_id)
54. 1,667.803 4,918.053 ↓ 1.6 4,049,769 3

Parallel Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=268,297.76..766,308.58 rows=2,531,535 width=23) (actual time=3,279.824..4,918.053 rows=4,049,769 loops=3)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 583
55.          

SubPlan (forParallel Index Scan)

56. 3,165.469 3,250.250 ↑ 1.7 155,667 3

Nested Loop (cost=1.00..267,647.47 rows=259,940 width=4) (actual time=0.642..3,250.250 rows=155,667 loops=3)

57. 84.433 84.761 ↑ 1.7 155,667 3

Nested Loop (cost=0.43..35,621.65 rows=259,940 width=14) (actual time=0.163..84.761 rows=155,667 loops=3)

58. 0.102 0.102 ↑ 1.0 302 3

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..8.02 rows=302 width=4) (actual time=0.026..0.102 rows=302 loops=3)

59. 0.226 0.226 ↑ 1.8 515 906

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.43..108.75 rows=918 width=14) (actual time=0.015..0.226 rows=515 loops=906)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
60. 0.020 0.020 ↑ 1.0 1 467,001

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.89 rows=1 width=10) (actual time=0.020..0.020 rows=1 loops=467,001)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 155667
61. 0.097 0.911 ↑ 1.4 223 3

Sort (cost=1,990.08..1,990.84 rows=302 width=24) (actual time=0.894..0.911 rows=223 loops=3)

  • Sort Key: ipu.remittance_id
  • Sort Method: quicksort Memory: 42kB
62. 0.814 0.814 ↑ 1.4 223 3

Nested Loop (cost=0.29..1,977.64 rows=302 width=24) (actual time=0.353..0.814 rows=223 loops=3)

63. 0.000 0.093 ↑ 1.0 302 3

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..8.02 rows=302 width=10) (actual time=0.006..0.031 rows=302 loops=3)

64. 1.812 1.812 ↑ 1.0 1 906

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.29..6.52 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=906)

  • Index Cond: (remittance_id = ipu.remittance_id)
65. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
66. 36.818 215.281 ↑ 7.4 35,192 1

Gather (cost=1,022.14..174,691.80 rows=259,940 width=42) (actual time=9.124..215.281 rows=35,192 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
67. 89.156 178.463 ↑ 8.7 17,596 2

Nested Loop (cost=22.14..147,697.80 rows=152,906 width=42) (actual time=77.378..178.463 rows=17,596 loops=2)

68. 9.322 89.302 ↑ 8.7 17,596 2

Nested Loop (cost=21.58..11,211.92 rows=152,906 width=52) (actual time=77.366..89.302 rows=17,596 loops=2)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
69. 4.403 79.918 ↑ 1.6 112 2

Merge Join (cost=21.15..4,099.67 rows=178 width=46) (actual time=77.330..79.918 rows=112 loops=2)

  • Merge Cond: (ir_2.remittance_id = ipu_1.remittance_id)
70. 75.258 75.258 ↑ 1.2 45,228 2

Parallel Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.29..3,944.52 rows=53,235 width=14) (actual time=0.059..75.258 rows=45,228 loops=2)

71. 0.149 0.257 ↑ 1.0 302 2

Sort (cost=20.46..21.21 rows=302 width=32) (actual time=0.223..0.257 rows=302 loops=2)

  • Sort Key: ipu_1.remittance_id
  • Sort Method: quicksort Memory: 48kB
72. 0.108 0.108 ↑ 1.0 302 2

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..8.02 rows=302 width=32) (actual time=0.023..0.108 rows=302 loops=2)

73. 0.062 0.062 ↑ 5.8 158 223

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.43..28.48 rows=918 width=14) (actual time=0.004..0.062 rows=158 loops=223)

  • Index Cond: (remittance_id = ir_2.remittance_id)
74. 0.005 0.005 ↑ 1.0 1 35,192

Index Only Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..0.89 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=35,192)

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 35192