explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OUne

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 274,423.363 ↓ 1.1 210 1

GroupAggregate (cost=4,641,481.74..4,641,526.83 rows=184 width=408) (actual time=274,013.309..274,423.363 rows=210 loops=1)

  • Group Key: tpa.tpa_name, hcm.center_name
2. 101.374 274,029.374 ↓ 108.9 138,622 1

Sort (cost=4,641,481.74..4,641,484.92 rows=1,273 width=329) (actual time=274,012.842..274,029.374 rows=138,622 loops=1)

  • Sort Key: tpa.tpa_name, hcm.center_name
  • Sort Method: quicksort Memory: 25638kB
3. 269,031.134 273,928.000 ↓ 108.9 138,622 1

Hash Right Join (cost=4,640,647.73..4,641,416.09 rows=1,273 width=329) (actual time=273,880.978..273,928.000 rows=138,622 loops=1)

  • Hash Cond: ((bc.claim_id)::text = (ipa.claim_id)::text)
4. 5.074 4,896.866 ↓ 1.0 32,476 1

Unique (cost=262,999.59..263,317.75 rows=31,816 width=21) (actual time=4,889.423..4,896.866 rows=32,476 loops=1)

5. 144.413 4,891.792 ↓ 1.0 32,587 1

Sort (cost=262,999.59..263,079.13 rows=31,816 width=21) (actual time=4,889.421..4,891.792 rows=32,587 loops=1)

  • Sort Key: b.insurance_deduction, bc.claim_id, pr.center_id
  • Sort Method: quicksort Memory: 3314kB
6. 15.234 4,747.379 ↓ 1.0 32,587 1

Gather (cost=1,000.86..260,620.16 rows=31,816 width=21) (actual time=17.765..4,747.379 rows=32,587 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 4,732.145 4,732.145 ↑ 1.2 10,862 3

Nested Loop (cost=0.86..256,438.56 rows=13,257 width=21) (actual time=17.090..4,732.145 rows=10,862 loops=3)

  • -> Nested Loop (cost=0.43..247864.24 rows=16640 width=21) (actual time=16.105..3587.204 rows=12960 loops
  • -> Parallel Seq Scan on patient_registration pr (cost=0.00..163502.28 rows=14823 width=20) (actual
  • Filter: ((use_drg)::text = 'Y'::text)
  • Rows Removed by Filter: 1097387
  • -> Index Scan using bill_visit_id_idx on bill b (cost=0.43..5.68 rows=1 width=32) (actual time=0.1
  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • -> Index Scan using bill_claim_bill_no_idx on bill_claim bc (cost=0.43..0.51 rows=1 width=29) (actual ti
  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
8. 117.906 268,991.215 ↓ 17,327.8 138,622 1

Hash (cost=4,377,648.04..4,377,648.04 rows=8 width=326) (actual time=268,991.215..268,991.215 rows=138,622 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 17503kB
9. 316.830 268,873.309 ↓ 17,327.8 138,622 1

Nested Loop Left Join (cost=4,377,579.44..4,377,648.04 rows=8 width=326) (actual time=256,817.171..268,873.309 rows=138,622 loops=1)

10. 10,731.387 268,556.479 ↓ 17,327.8 138,622 1

Nested Loop Left Join (cost=4,377,579.31..4,377,646.80 rows=8 width=112) (actual time=256,816.844..268,556.479 rows=138,622 loops=1)

11. 945.314 257,825.092 ↓ 17,327.8 138,622 1

GroupAggregate (cost=4,377,578.88..4,377,579.14 rows=8 width=123) (actual time=256,804.115..257,825.092 rows=138,622 loops=1)

  • Group Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
12. 1,529.448 256,879.778 ↓ 66,742.4 533,939 1

Sort (cost=4,377,578.88..4,377,578.90 rows=8 width=70) (actual time=256,804.084..256,879.778 rows=533,939 loops=1)

  • Sort Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
  • Sort Method: quicksort Memory: 89714kB
13. 9,703.227 255,350.330 ↓ 66,742.4 533,939 1

Nested Loop Left Join (cost=2,837,338.59..4,377,578.76 rows=8 width=70) (actual time=76,422.631..255,350.330 rows=533,939 loops=1)

14. 8,750.263 245,647.103 ↓ 66,742.4 533,939 1

Nested Loop Left Join (cost=2,837,338.16..4,377,573.21 rows=8 width=70) (actual time=76,413.513..245,647.103 rows=533,939 loops=1)

15. 334.042 236,896.840 ↓ 66,398.4 531,187 1

Nested Loop Left Join (cost=2,837,337.73..4,377,569.44 rows=8 width=69) (actual time=76,412.744..236,896.840 rows=531,187 loops=1)

16. 417.600 197,254.960 ↓ 66,398.4 531,187 1

Nested Loop (cost=2,837,337.17..4,377,563.63 rows=8 width=80) (actual time=76,398.917..197,254.960 rows=531,187 loops=1)

17. 3,781.284 195,243.799 ↓ 66,398.4 531,187 1

Hash Join (cost=2,837,336.90..4,377,561.31 rows=8 width=68) (actual time=76,398.885..195,243.799 rows=531,187 loops=1)

  • Hash Cond: (((insurance_submission_history.claim_id)::text = (ipa.claim_id)::text) AND ((insurance_submission_history.activity_id)::text = (ipa.charge_id)::text))
  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 19889
18. 5,673.346 44,114.437 ↑ 1.0 10,634,574 1

Unique (cost=2,395,424.21..2,821,632.09 rows=10,655,197 width=148) (actual time=32,711.250..44,114.437 rows=10,634,574 loops=1)

19. 36,483.278 38,441.091 ↑ 1.0 10,655,197 1

Sort (cost=2,395,424.21..2,422,062.21 rows=10,655,197 width=148) (actual time=32,711.248..38,441.091 rows=10,655,197 loops=1)

  • Sort Key: insurance_submission_history.submission_batch_id, insurance_submission_history.claim_id, insurance_submission_history.activity_id, insurance_submission_history.item_code, insurance_submission_history.amount, insurance_submission_history.insurance_claim_amount, insurance_submission_history.charge_head, insurance_submission_history.act_description, insurance_submission_history.act_quantity, insurance_submission_history.clinician_id, insurance_submission_history.member_id, insurance_submission_history.insurance_co, insurance_submission_history.sponsor_id, insurance_submission_history.resubmission, insurance_submission_history.patient_share
  • Sort Method: external merge Disk: 1708152kB
20. 1,957.813 1,957.813 ↑ 1.0 10,655,197 1

Seq Scan on insurance_submission_history (cost=0.00..350,469.97 rows=10,655,197 width=148) (actual time=0.426..1,957.813 rows=10,655,197 loops=1)

21. 328.906 42,643.638 ↓ 1.3 699,840 1

Hash (cost=434,049.88..434,049.88 rows=524,187 width=38) (actual time=42,643.638..42,643.638 rows=699,840 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 57808kB
22. 1,432.228 42,314.732 ↓ 1.3 699,840 1

Hash Join (cost=779.63..434,049.88 rows=524,187 width=38) (actual time=129.189..42,314.732 rows=699,840 loops=1)

  • Hash Cond: (ipa.remittance_id = ir.remittance_id)
  • Join Filter: (SubPlan 2)
  • Rows Removed by Join Filter: 189148
23. 3,415.953 3,415.953 ↑ 1.0 12,151,369 1

Seq Scan on insurance_payment_allocation ipa (cost=0.00..359,273.69 rows=12,151,369 width=42) (actual time=0.020..3,415.953 rows=12,151,369 loops=1)

24. 3.741 129.055 ↓ 1.0 7,864 1

Hash (cost=682.03..682.03 rows=7,808 width=4) (actual time=129.055..129.055 rows=7,864 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 341kB
25. 125.314 125.314 ↓ 1.0 7,864 1

Index Scan using idx_insurance_remittance_received_date on insurance_remittance ir (cost=0.29..682.03 rows=7,808 width=4) (actual time=7.874..125.314 rows=7,864 loops=1)

  • Index Cond: ((received_date >= '2018-05-01'::date) AND (received_date <= '2018-11-30'::date))
26.          

SubPlan (forHash Join)

27. 0.000 37,337.496 ↑ 1.0 1 888,988

Limit (cost=628.33..628.33 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=888,988)

28. 12,445.832 37,337.496 ↑ 158.0 1 888,988

Sort (cost=628.33..628.73 rows=158 width=4) (actual time=0.042..0.042 rows=1 loops=888,988)

  • Sort Key: insurance_payment_allocation.remittance_id
  • Sort Method: top-N heapsort Memory: 25kB
29. 13,334.820 24,891.664 ↑ 1.4 117 888,988

Bitmap Heap Scan on insurance_payment_allocation (cost=5.78..627.54 rows=158 width=4) (actual time=0.014..0.028 rows=117 loops=888,988)

  • Recheck Cond: ((claim_id)::text = (ipa.claim_id)::text)
  • Heap Blocks: exact=6409948
30. 11,556.844 11,556.844 ↑ 1.4 117 888,988

Bitmap Index Scan on idx_insurance_payment_allocation_claim_id (cost=0.00..5.75 rows=158 width=0) (actual time=0.013..0.013 rows=117 loops=888,988)

  • Index Cond: ((claim_id)::text = (ipa.claim_id)::text)
31.          

SubPlan (forHash Join)

32. 551.076 104,704.440 ↑ 1.0 1 551,076

Limit (cost=1,285.80..1,285.80 rows=1 width=17) (actual time=0.190..0.190 rows=1 loops=551,076)

33. 6,061.836 104,153.364 ↑ 109.0 1 551,076

Sort (cost=1,285.80..1,286.07 rows=109 width=17) (actual time=0.189..0.189 rows=1 loops=551,076)

  • Sort Key: isb.submission_date
  • Sort Method: quicksort Memory: 25kB
34. 16,898.070 98,091.528 ↑ 2.5 43 551,076

Nested Loop (cost=0.85..1,285.25 rows=109 width=17) (actual time=0.018..0.178 rows=43 loops=551,076)

35. 9,919.368 9,919.368 ↑ 2.5 43 551,076

Index Scan using idx_claim_id_ins_sub_history on insurance_submission_history ish (cost=0.43..441.84 rows=109 width=9) (actual time=0.012..0.018 rows=43 loops=551,076)

  • Index Cond: ((claim_id)::text = (ipa.claim_id)::text)
36. 71,274.090 71,274.090 ↑ 1.0 1 23,758,030

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch isb (cost=0.42..7.74 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=23,758,030)

  • Index Cond: ((submission_batch_id)::text = (ish.submission_batch_id)::text)
37. 1,593.561 1,593.561 ↑ 1.0 1 531,187

Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.27..0.29 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=531,187)

  • Index Cond: ((tpa_id)::text = (insurance_submission_history.sponsor_id)::text)
38. 39,307.838 39,307.838 ↑ 1.0 1 531,187

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..0.72 rows=1 width=24) (actual time=0.074..0.074 rows=1 loops=531,187)

  • Index Cond: ((insurance_submission_history.activity_id)::text = (charge_id)::text)
  • Filter: ((claim_id)::text = (insurance_submission_history.claim_id)::text)
  • Rows Removed by Filter: 0
39. 0.000 8,498.992 ↑ 1.0 1 531,187

Index Scan using bill_claim_claim_idx on bill_claim bc_1 (cost=0.43..0.46 rows=1 width=29) (actual time=0.016..0.016 rows=1 loops=531,187)

  • Index Cond: ((claim_id)::text = (bcc.claim_id)::text)
40. 9,610.902 9,610.902 ↑ 1.0 1 533,939

Index Scan using bill_pkey on bill b_1 (cost=0.43..0.69 rows=1 width=29) (actual time=0.018..0.018 rows=1 loops=533,939)

  • Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
41. 10,535.272 10,535.272 ↑ 1.0 1 138,622

Index Scan using patient_registration_pkey on patient_registration pr1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.076..0.076 rows=1 loops=138,622)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
42. 138.622 138.622 ↑ 1.0 1 138,622

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.16 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=138,622)

  • Index Cond: (center_id = pr1.center_id)