explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F2jC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 46,087.868 ↑ 1.8 13 1

GroupAggregate (cost=3,314,014.19..3,314,019.40 rows=23 width=408) (actual time=46,087.722..46,087.868 rows=13 loops=1)

  • Group Key: tpa.tpa_name, hcm.center_name
2. 0.053 46,087.693 ↑ 1.6 92 1

Sort (cost=3,314,014.19..3,314,014.55 rows=145 width=329) (actual time=46,087.688..46,087.693 rows=92 loops=1)

  • Sort Key: tpa.tpa_name, hcm.center_name
  • Sort Method: quicksort Memory: 37kB
3. 2.611 46,087.640 ↑ 1.6 92 1

Hash Right Join (cost=3,313,319.52..3,314,008.98 rows=145 width=329) (actual time=46,086.670..46,087.640 rows=92 loops=1)

  • Hash Cond: ((bc.claim_id)::text = (ipa.claim_id)::text)
4. 5.400 871.670 ↓ 1.1 32,480 1

Unique (cost=258,144.22..258,433.91 rows=28,969 width=21) (actual time=863.483..871.670 rows=32,480 loops=1)

5. 134.411 866.270 ↓ 1.1 32,591 1

Sort (cost=258,144.22..258,216.65 rows=28,969 width=21) (actual time=863.481..866.270 rows=32,591 loops=1)

  • Sort Key: b.insurance_deduction, bc.claim_id, pr.center_id
  • Sort Method: quicksort Memory: 3315kB
6. 11.464 731.859 ↓ 1.1 32,591 1

Gather (cost=1,000.86..255,997.30 rows=28,969 width=21) (actual time=4.354..731.859 rows=32,591 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 720.395 720.395 ↑ 1.1 10,864 3

Nested Loop (cost=0.86..252,100.40 rows=12,070 width=21) (actual time=1.245..720.395 rows=10,864 loops=3)

  • -> Nested Loop (cost=0.43..243180.46 rows=15241 width=21) (actual time=1.163..600.358 rows=12961 loops=3
  • -> Parallel Seq Scan on patient_registration pr (cost=0.00..163502.46 rows=13576 width=20) (actual
  • Filter: ((use_drg)::text = 'Y'::text)
  • Rows Removed by Filter: 1097418
  • -> Index Scan using bill_visit_id_idx on bill b (cost=0.43..5.86 rows=1 width=32) (actual time=0.0
  • 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.58 rows=1 width=29) (actual ti
  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
8. 45,213.359 45,213.359 ↓ 92.0 92 1

Hash (cost=3,055,175.28..3,055,175.28 rows=1 width=326) (actual time=45,213.359..45,213.359 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
9. 0.000 45,213.320 ↓ 92.0 92 1

Nested Loop Left Join (cost=3,055,167.20..3,055,175.28 rows=1 width=326) (actual time=45,212.095..45,213.320 rows=92 loops=1)

10. 0.078 45,213.228 ↓ 92.0 92 1

Nested Loop Left Join (cost=3,055,167.06..3,055,175.13 rows=1 width=112) (actual time=45,212.088..45,213.228 rows=92 loops=1)

11. 0.520 45,212.598 ↓ 92.0 92 1

GroupAggregate (cost=3,055,166.63..3,055,166.67 rows=1 width=123) (actual time=45,212.065..45,212.598 rows=92 loops=1)

  • Group Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
12. 0.764 45,212.078 ↓ 838.0 838 1

Sort (cost=3,055,166.63..3,055,166.64 rows=1 width=69) (actual time=45,212.039..45,212.078 rows=838 loops=1)

  • Sort Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
  • Sort Method: quicksort Memory: 142kB
13. 0.271 45,211.314 ↓ 838.0 838 1

Nested Loop Semi Join (cost=2,415,836.40..3,055,166.62 rows=1 width=69) (actual time=37,857.458..45,211.314 rows=838 loops=1)

  • Join Filter: ((insurance_submission_history.claim_id)::text = (insurance_payment_allocation.claim_id)::text)
14. 0.000 45,206.015 ↓ 838.0 838 1

Nested Loop Semi Join (cost=2,415,835.84..3,055,154.93 rows=1 width=105) (actual time=37,857.438..45,206.015 rows=838 loops=1)

15. 0.329 45,197.658 ↓ 838.0 838 1

Nested Loop Left Join (cost=2,415,834.98..3,055,148.06 rows=1 width=100) (actual time=37,857.405..45,197.658 rows=838 loops=1)

16. 0.600 45,193.139 ↓ 838.0 838 1

Nested Loop Left Join (cost=2,415,834.55..3,055,147.37 rows=1 width=100) (actual time=37,857.389..45,193.139 rows=838 loops=1)

17. 0.029 45,188.384 ↓ 831.0 831 1

Nested Loop Left Join (cost=2,415,834.12..3,055,146.86 rows=1 width=99) (actual time=37,857.374..45,188.384 rows=831 loops=1)

18. 0.068 45,183.369 ↓ 831.0 831 1

Nested Loop (cost=2,415,833.56..3,055,145.95 rows=1 width=96) (actual time=37,857.356..45,183.369 rows=831 loops=1)

19. 1,414.074 45,180.808 ↓ 831.0 831 1

Hash Join (cost=2,415,833.29..3,055,145.66 rows=1 width=84) (actual time=37,857.323..45,180.808 rows=831 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))
20. 5,002.225 43,753.701 ↑ 1.0 10,634,583 1

Unique (cost=2,395,425.42..2,821,633.66 rows=10,655,206 width=149) (actual time=34,294.112..43,753.701 rows=10,634,583 loops=1)

21. 37,360.592 38,751.476 ↑ 1.0 10,655,206 1

Sort (cost=2,395,425.42..2,422,063.43 rows=10,655,206 width=149) (actual time=34,294.111..38,751.476 rows=10,655,206 loops=1)

  • Sort Method: external merge Disk: 1708152kB
22. 1,390.884 1,390.884 ↑ 1.0 10,655,206 1

Seq Scan on insurance_submission_history (cost=0.00..350,470.06 rows=10,655,206 width=149) (actual time=0.013..1,390.884 rows=10,655,206 loops=1)

23. 0.251 13.033 ↑ 36.9 709 1

Hash (cost=20,015.16..20,015.16 rows=26,181 width=45) (actual time=13.033..13.033 rows=709 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 310kB
24. 5.229 12.782 ↑ 36.9 709 1

Gather (cost=1,000.43..20,015.16 rows=26,181 width=45) (actual time=5.932..12.782 rows=709 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
25. 0.693 7.553 ↑ 43.5 354 2

Nested Loop (cost=0.43..16,397.06 rows=15,401 width=45) (actual time=2.759..7.553 rows=354 loops=2)

26. 6.844 6.844 ↑ 2.9 40 2

Parallel Seq Scan on insurance_remittance ir (cost=0.00..2,632.58 rows=115 width=4) (actual time=2.297..6.844 rows=40 loops=2)

  • Filter: ((received_date >= '2018-09-01'::date) AND (received_date <= '2018-11-30'::date))
  • Rows Removed by Filter: 45213
27. 0.016 0.016 ↑ 102.1 9 80

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=0.43..110.50 rows=919 width=41) (actual time=0.014..0.016 rows=9 loops=80)

  • Index Cond: (remittance_id = ir.remittance_id)
28. 2.493 2.493 ↑ 1.0 1 831

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=831)

  • Index Cond: ((tpa_id)::text = (insurance_submission_history.sponsor_id)::text)
29. 4.986 4.986 ↑ 1.0 1 831

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..0.89 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=831)

  • Index Cond: ((insurance_submission_history.activity_id)::text = (charge_id)::text)
  • Filter: ((claim_id)::text = (insurance_submission_history.claim_id)::text)
30. 4.155 4.155 ↑ 1.0 1 831

Index Scan using bill_claim_claim_idx on bill_claim bc_1 (cost=0.43..0.50 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=831)

  • Index Cond: ((claim_id)::text = (bcc.claim_id)::text)
31. 4.190 4.190 ↑ 1.0 1 838

Index Scan using bill_pkey on bill b_1 (cost=0.43..0.69 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=838)

  • Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
32. 0.838 8.380 ↑ 1.0 1 838

Nested Loop (cost=0.85..6.86 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=838)

  • Join Filter: ((insurance_submission_history.submission_batch_id)::text = (isb.submission_batch_id)::text)
33. 5.028 5.028 ↑ 1.0 1 838

Index Scan using idx_claim_id_ins_sub_history on insurance_submission_history ish1 (cost=0.43..6.41 rows=1 width=23) (actual time=0.006..0.006 rows=1 loops=838)

  • Index Cond: ((claim_id)::text = (insurance_submission_history.claim_id)::text)
  • Filter: ((insurance_submission_history.submission_batch_id)::text = (submission_batch_id)::text)
  • Rows Removed by Filter: 3
34. 2.514 2.514 ↑ 1.0 1 838

Index Only Scan using insurance_submission_batch_pkey on insurance_submission_batch isb (cost=0.42..0.44 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=838)

  • Index Cond: (submission_batch_id = (ish1.submission_batch_id)::text)
  • Heap Fetches: 838
35. 5.028 5.028 ↑ 1.0 1 838

Index Scan using idx_insurance_payment_allocation_claim_id on insurance_payment_allocation (cost=0.56..11.68 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=838)

  • Index Cond: ((claim_id)::text = (ipa.claim_id)::text)
  • Filter: (ipa.remittance_id = remittance_id)
  • Rows Removed by Filter: 3
36. 0.552 0.552 ↑ 1.0 1 92

Index Scan using patient_registration_pkey on patient_registration pr1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=92)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
37. 0.092 0.092 ↑ 1.0 1 92

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=92)

  • Index Cond: (center_id = pr1.center_id)