explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Hxl

Settings
# exclusive inclusive rows x rows loops node
1. 447.534 839,461.068 ↓ 48.5 34,899 1

Hash Left Join (cost=73,947.32..453,550.88 rows=719 width=516) (actual time=51,968.001..839,461.068 rows=34,899 loops=1)

  • Hash Cond: ((bcc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
2. 214,741.857 839,011.957 ↓ 48.5 34,899 1

Nested Loop Left Join (cost=73,897.20..453,478.01 rows=719 width=501) (actual time=51,966.477..839,011.957 rows=34,899 loops=1)

3. 101.560 624,270.100 ↓ 48.5 34,897 1

Hash Left Join (cost=73,896.77..449,714.77 rows=719 width=483) (actual time=51,949.657..624,270.100 rows=34,897 loops=1)

  • Hash Cond: ((pbcc.denial_code)::text = (pidc.denial_code)::text)
4. 92.389 624,168.540 ↓ 48.5 34,897 1

Hash Left Join (cost=73,865.52..449,678.03 rows=719 width=457) (actual time=51,936.208..624,168.540 rows=34,897 loops=1)

  • Hash Cond: ((pri_plan.insurance_co)::text = (picm.insurance_co_id)::text)
5. 70,346.096 624,076.151 ↓ 48.5 34,897 1

Hash Left Join (cost=73,852.96..449,655.58 rows=719 width=442) (actual time=51,935.699..624,076.151 rows=34,897 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
  • -> Hash Left Join (cost=73842.24..449635.73 rows=719 width=430) (actual time=51935.310..623980.079 rows=34897 loops=
  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
  • -> Hash Left Join (cost=73792.12..449575.77 rows=719 width=416) (actual time=51933.717..623887.342 rows=34897
  • Hash Cond: ((pr.admitted_dept)::text = (admdep.dept_id)::text)
  • -> Nested Loop Left Join (cost=73790.04..449563.90 rows=719 width=206) (actual time=51933.590..623778.61
  • -> Hash Join (cost=73789.05..447819.80 rows=719 width=213) (actual time=51933.386..603136.495 rows
  • Hash Cond: (pr.center_id = hcm.center_id)
  • -> Nested Loop Left Join (cost=73787.87..447776.39 rows=9344 width=203) (actual time=51806.5
  • -> Nested Loop Left Join (cost=73787.45..440446.77 rows=9344 width=188) (actual time=5
  • Join Filter: ((pbcl.claim_id)::text = (pbcc.claim_id)::text)
  • Rows Removed by Join Filter: 544
  • -> Hash Join (cost=73787.02..427576.85 rows=9344 width=209) (actual time=51780.3
  • Hash Cond: ((bcc.bill_no)::text = (b.bill_no)::text)
  • -> Seq Scan on bill_charge bcc (cost=0.00..344779.86 rows=2377743 width=10
  • Filter: ((claim_status <> 'C'::bpchar) AND (status = 'A'::bpchar) AND
  • Rows Removed by Filter: 2602675
  • -> Hash (cost=73731.70..73731.70 rows=4425 width=106) (actual time=51769.6
6. 381.230 51,522.971 ↓ 12.9 57,112 1

Nested Loop (cost=16,716.92..73,731.70 rows=4,425 width=106) (actual time=139.314..51,522.971 rows=57,112 loops=1)

7. 1,364.306 2,539.429 ↓ 12.9 57,112 1

Hash Right Join (cost=16,716.49..43,589.39 rows=4,425 width=60) (actual time=139.289..2,539.429 rows=57,112 loops=1)

  • Hash Cond: ((pbcl.bill_no)::text = (b.bill_no)::text)
8. 1,035.884 1,035.884 ↓ 1.0 913,404 1

Seq Scan on bill_claim pbcl (cost=0.00..23,412.74 rows=913,142 width=29) (actual time=0.010..1,035.884 rows=913,404 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 29523
9. 16.657 139.239 ↓ 12.9 57,111 1

Hash (cost=16,661.18..16,661.18 rows=4,425 width=46) (actual time=139.239..139.239 rows=57,111 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 2 (originally 1) Memory Usage: 3585kB
10. 115.130 122.582 ↓ 12.9 57,111 1

Bitmap Heap Scan on bill b (cost=125.83..16,661.18 rows=4,425 width=46) (actual time=11.186..122.582 rows=57,111 loops=1)

  • Recheck Cond: ((date(finalized_date) >= '2016-09-01'::date) AND (date(finalized_date) <= '2016-09-30'::date))
  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 11005
  • Heap Blocks: exact=23813
11. 7.452 7.452 ↓ 12.1 68,184 1

Bitmap Index Scan on bill_finalized_date_idx (cost=0.00..124.73 rows=5,630 width=0) (actual time=7.452..7.452 rows=68,184 loops=1)

  • Index Cond: ((date(finalized_date) >= '2016-09-01'::date) AND (date(finalized_date) <= '2016-09-30'::date))
12. 48,602.312 48,602.312 ↑ 1.0 1 57,112

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..6.80 rows=1 width=61) (actual time=0.850..0.851 rows=1 loops=57,112)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
13. 397,940.907 397,940.907 ↓ 0.0 0 82,509

Index Scan using bill_charge_claim_charge_id on bill_charge_claim pbcc (cost=0.43..1.36 rows=1 width=40) (actual time=4.769..4.823 rows=0 loops=82,509)

  • Index Cond: ((charge_id)::text = (bcc.charge_id)::text)
  • Filter: ((denial_code IS NOT NULL) AND ((bill_no)::text = (bcc.bill_no)::text))
14. 83,746.635 83,746.635 ↑ 1.0 1 82,509

Index Only Scan using patient_details_pkey on patient_details pd (cost=0.42..0.77 rows=1 width=15) (actual time=1.002..1.015 rows=1 loops=82,509)

  • Index Cond: (mr_no = (pr.mr_no)::text)
  • Heap Fetches: 0
15. 0.001 0.008 ↑ 1.0 1 1

Hash (cost=1.16..1.16 rows=1 width=18) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..1.16 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 12
17. 104.691 20,519.436 ↑ 1.0 1 34,897

Nested Loop Left Join (cost=0.99..2.42 rows=1 width=39) (actual time=0.585..0.588 rows=1 loops=34,897)

18. 174.485 19,193.350 ↑ 1.0 1 34,897

Nested Loop (cost=0.57..1.29 rows=1 width=39) (actual time=0.547..0.550 rows=1 loops=34,897)

19. 18,565.204 18,565.204 ↑ 1.0 1 34,897

Index Scan using policy_patient_idx on patient_insurance_plans pri_plan (cost=0.42..1.11 rows=1 width=53) (actual time=0.531..0.532 rows=1 loops=34,897)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((priority = 1) AND ((mr_no)::text = (pr.mr_no)::text))
  • Rows Removed by Filter: 0
20. 453.661 453.661 ↑ 1.0 1 34,897

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.14..0.16 rows=1 width=10) (actual time=0.012..0.013 rows=1 loops=34,897)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 34897
21. 1,221.395 1,221.395 ↓ 0.0 0 34,897

Index Scan using policy_patient_idx on patient_insurance_plans sec_plan (cost=0.42..1.12 rows=1 width=38) (actual time=0.034..0.035 rows=0 loops=34,897)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
22. 0.047 0.098 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=256) (actual time=0.098..0.098 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.051 0.051 ↑ 1.0 48 1

Seq Scan on department admdep (cost=0.00..1.48 rows=48 width=256) (actual time=0.027..0.051 rows=48 loops=1)

24. 0.000 1.577 ↑ 1.1 990 1

Hash (cost=36.72..36.72 rows=1,072 width=30) (actual time=1.577..1.577 rows=990 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
25. 0.765 0.765 ↑ 1.1 990 1

Seq Scan on doctors dr (cost=0.00..36.72 rows=1,072 width=30) (actual time=0.020..0.765 rows=990 loops=1)

26. 0.206 0.376 ↑ 1.0 254 1

Hash (cost=7.54..7.54 rows=254 width=32) (actual time=0.376..0.376 rows=254 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
27. 0.170 0.170 ↑ 1.0 254 1

Seq Scan on tpa_master ptpa (cost=0.00..7.54 rows=254 width=32) (actual time=0.007..0.170 rows=254 loops=1)

28. 0.266 0.497 ↑ 1.0 336 1

Hash (cost=8.36..8.36 rows=336 width=31) (actual time=0.497..0.497 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
29. 0.231 0.231 ↑ 1.0 336 1

Seq Scan on insurance_company_master picm (cost=0.00..8.36 rows=336 width=31) (actual time=0.012..0.231 rows=336 loops=1)

30. 0.711 13.423 ↑ 1.0 900 1

Hash (cost=20.00..20.00 rows=900 width=36) (actual time=13.423..13.423 rows=900 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
31. 12.712 12.712 ↑ 1.0 900 1

Seq Scan on insurance_denial_codes pidc (cost=0.00..20.00 rows=900 width=36) (actual time=0.008..12.712 rows=900 loops=1)

32. 214,616.550 214,616.550 ↑ 4.0 1 34,897

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis pdiag (cost=0.43..5.19 rows=4 width=50) (actual time=5.137..6.150 rows=1 loops=34,897)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 3
33. 0.782 1.452 ↑ 1.1 990 1

Hash (cost=36.72..36.72 rows=1,072 width=30) (actual time=1.452..1.452 rows=990 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
34. 0.670 0.670 ↑ 1.1 990 1

Seq Scan on doctors predoc (cost=0.00..36.72 rows=1,072 width=30) (actual time=0.018..0.670 rows=990 loops=1)