explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l8ad

Settings
# exclusive inclusive rows x rows loops node
1. 0.330 74.517 ↓ 2.9 82 1

Hash Left Join (cost=5,108.70..12,109.54 rows=28 width=1,278) (actual time=38.620..74.517 rows=82 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (sdiag.visit_id)::text)
2. 0.076 73.130 ↓ 2.9 82 1

Hash Left Join (cost=5,036.79..12,036.25 rows=28 width=1,223) (actual time=37.510..73.130 rows=82 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pdiag.visit_id)::text)
3. 0.070 72.898 ↓ 2.9 82 1

Hash Left Join (cost=5,023.52..12,022.77 rows=28 width=1,204) (actual time=37.323..72.898 rows=82 loops=1)

  • Hash Cond: ((pbcc.denial_code)::text = (pidc.denial_code)::text)
4. 0.081 72.815 ↓ 2.9 82 1

Hash Left Join (cost=5,022.50..12,021.63 rows=28 width=692) (actual time=37.279..72.815 rows=82 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
5. 0.067 72.677 ↓ 2.9 82 1

Hash Left Join (cost=5,021.12..12,019.89 rows=28 width=582) (actual time=37.189..72.677 rows=82 loops=1)

  • Hash Cond: ((ppip.insurance_co)::text = (picm.insurance_co_id)::text)
6. 0.106 72.593 ↓ 2.9 82 1

Hash Left Join (cost=5,019.74..12,018.13 rows=28 width=173) (actual time=37.132..72.593 rows=82 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
7. 0.069 72.405 ↓ 2.9 82 1

Nested Loop Left Join (cost=5,014.42..12,012.56 rows=28 width=159) (actual time=37.011..72.405 rows=82 loops=1)

8. 0.094 71.762 ↓ 2.9 82 1

Nested Loop Left Join (cost=5,013.46..11,796.04 rows=28 width=150) (actual time=36.982..71.762 rows=82 loops=1)

9. 0.088 70.930 ↓ 2.9 82 1

Nested Loop Left Join (cost=5,013.17..11,706.49 rows=28 width=126) (actual time=36.919..70.930 rows=82 loops=1)

10. 0.092 68.710 ↓ 2.9 82 1

Nested Loop Left Join (cost=5,012.75..11,513.64 rows=28 width=108) (actual time=36.903..68.710 rows=82 loops=1)

  • Join Filter: ((pbcl.claim_id)::text = (pbcc.claim_id)::text)
11. 0.078 68.454 ↓ 2.9 82 1

Hash Left Join (cost=5,012.48..11,429.03 rows=28 width=106) (actual time=36.867..68.454 rows=82 loops=1)

  • Hash Cond: ((b.bill_no)::text = (pbcl.bill_no)::text)
12. 0.055 68.187 ↓ 2.9 82 1

Hash Join (cost=4,994.29..11,410.69 rows=28 width=107) (actual time=36.659..68.187 rows=82 loops=1)

  • Hash Cond: ((bcc.charge_head)::text = (chc.chargehead_id)::text)
13. 1.278 68.094 ↓ 2.9 82 1

Hash Join (cost=4,990.35..11,406.37 rows=28 width=96) (actual time=36.607..68.094 rows=82 loops=1)

  • Hash Cond: ((bcc.bill_no)::text = (b.bill_no)::text)
14. 30.394 30.394 ↓ 1.2 11,152 1

Index Scan using bill_charge_status_idx on bill_charge bcc (cost=0.42..6,379.82 rows=9,691 width=76) (actual time=0.165..30.394 rows=11,152 loops=1)

  • Index Cond: (status = 'X'::bpchar)
  • Filter: ((charge_head)::text <> ALL ('{PHMED,PHCMED,PHRET,PHCRET}'::text[]))
15. 0.067 36.422 ↑ 4.1 114 1

Hash (cost=4,984.06..4,984.06 rows=470 width=30) (actual time=36.422..36.422 rows=114 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
16. 36.026 36.355 ↑ 4.1 114 1

Bitmap Heap Scan on bill b (cost=53.40..4,984.06 rows=470 width=30) (actual time=2.540..36.355 rows=114 loops=1)

  • Recheck Cond: (status = 'X'::bpchar)
  • Filter: is_tpa
  • Rows Removed by Filter: 2712
17. 0.329 0.329 ↓ 1.0 2,848 1

Bitmap Index Scan on bill_status_idx (cost=0.00..53.28 rows=2,782 width=0) (actual time=0.329..0.329 rows=2,848 loops=1)

  • Index Cond: (status = 'X'::bpchar)
18. 0.022 0.038 ↑ 1.0 86 1

Hash (cost=2.86..2.86 rows=86 width=23) (actual time=0.038..0.038 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
19. 0.016 0.016 ↑ 1.0 86 1

Seq Scan on chargehead_constants chc (cost=0.00..2.86 rows=86 width=23) (actual time=0.004..0.016 rows=86 loops=1)

20. 0.075 0.189 ↑ 1.0 447 1

Hash (cost=12.48..12.48 rows=457 width=19) (actual time=0.189..0.189 rows=447 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
21. 0.114 0.114 ↑ 1.0 447 1

Seq Scan on bill_claim pbcl (cost=0.00..12.48 rows=457 width=19) (actual time=0.010..0.114 rows=447 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 51
22. 0.164 0.164 ↓ 0.0 0 82

Index Scan using bill_charge_claim_charge_idx on bill_charge_claim pbcc (cost=0.28..3.01 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=82)

  • Index Cond: ((charge_id)::text = (bcc.charge_id)::text)
23. 2.132 2.132 ↑ 1.0 1 82

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..6.88 rows=1 width=27) (actual time=0.026..0.026 rows=1 loops=82)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
24. 0.738 0.738 ↑ 1.0 1 82

Index Scan using patient_details_pkey on patient_details pd (cost=0.29..3.19 rows=1 width=33) (actual time=0.008..0.009 rows=1 loops=82)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 0.082 0.574 ↑ 1.0 1 82

Nested Loop Left Join (cost=0.96..7.72 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=82)

26. 0.082 0.410 ↑ 1.0 1 82

Nested Loop Left Join (cost=0.69..5.62 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=82)

27. 0.246 0.246 ↑ 1.0 1 82

Index Only Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.42..3.52 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=82)

  • Index Cond: (patient_id = (pr.patient_id)::text)
  • Heap Fetches: 82
28. 0.082 0.082 ↓ 0.0 0 82

Index Scan using policy_patient_idx on patient_insurance_plans ppip (cost=0.27..2.09 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=82)

  • Index Cond: ((patient_id)::text = (pr_1.patient_id)::text)
  • Filter: (priority = 1)
29. 0.082 0.082 ↓ 0.0 0 82

Index Scan using policy_patient_idx on patient_insurance_plans spip (cost=0.27..2.09 rows=1 width=23) (actual time=0.001..0.001 rows=0 loops=82)

  • Index Cond: ((patient_id)::text = (pr_1.patient_id)::text)
  • Filter: (priority = 2)
30. 0.055 0.082 ↑ 1.0 103 1

Hash (cost=4.03..4.03 rows=103 width=29) (actual time=0.082..0.082 rows=103 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
31. 0.027 0.027 ↑ 1.0 103 1

Seq Scan on tpa_master ptpa (cost=0.00..4.03 rows=103 width=29) (actual time=0.007..0.027 rows=103 loops=1)

32. 0.010 0.017 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=456) (actual time=0.017..0.017 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
33. 0.007 0.007 ↑ 1.0 17 1

Seq Scan on insurance_company_master picm (cost=0.00..1.17 rows=17 width=456) (actual time=0.004..0.007 rows=17 loops=1)

34. 0.010 0.057 ↓ 1.1 18 1

Hash (cost=1.17..1.17 rows=17 width=156) (actual time=0.057..0.057 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
35. 0.047 0.047 ↓ 1.1 18 1

Seq Scan on salutation_master sm (cost=0.00..1.17 rows=17 width=156) (actual time=0.044..0.047 rows=18 loops=1)

36. 0.001 0.013 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=564) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on insurance_denial_codes pidc (cost=0.00..1.01 rows=1 width=564) (actual time=0.012..0.012 rows=1 loops=1)

38. 0.075 0.156 ↓ 1.2 238 1

Hash (cost=10.71..10.71 rows=204 width=28) (actual time=0.156..0.156 rows=238 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
39. 0.081 0.081 ↓ 1.2 239 1

Seq Scan on mrd_diagnosis pdiag (cost=0.00..10.71 rows=204 width=28) (actual time=0.007..0.081 rows=239 loops=1)

  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 174
40. 0.031 1.057 ↓ 1.3 71 1

Hash (cost=71.24..71.24 rows=54 width=73) (actual time=1.057..1.057 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
41. 0.010 1.026 ↓ 1.3 72 1

Subquery Scan on sdiag (cost=70.16..71.24 rows=54 width=73) (actual time=0.999..1.026 rows=72 loops=1)

42. 0.924 1.016 ↓ 1.3 72 1

HashAggregate (cost=70.16..70.70 rows=54 width=30) (actual time=0.997..1.016 rows=72 loops=1)

43. 0.092 0.092 ↓ 1.0 80 1

Seq Scan on mrd_diagnosis (cost=0.00..10.71 rows=79 width=30) (actual time=0.007..0.092 rows=80 loops=1)

  • Filter: (diag_type = 'S'::bpchar)
  • Rows Removed by Filter: 333