explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ChtL

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8.354 ↓ 7.0 7 1

Limit (cost=2,357.95..2,357.95 rows=1 width=1,239) (actual time=8.352..8.354 rows=7 loops=1)

2. 0.065 8.352 ↓ 7.0 7 1

Sort (cost=2,357.95..2,357.95 rows=1 width=1,239) (actual time=8.351..8.352 rows=7 loops=1)

  • Sort Key: b.open_date, b.bill_no
  • Sort Method: quicksort Memory: 28kB
3. 2.718 8.287 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,029.01..2,357.93 rows=1 width=983) (actual time=3.840..8.287 rows=7 loops=1)

4. 0.013 5.569 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,028.86..2,356.75 rows=1 width=972) (actual time=1.875..5.569 rows=7 loops=1)

5. 0.017 5.472 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,028.59..2,348.44 rows=1 width=963) (actual time=1.860..5.472 rows=7 loops=1)

6. 0.015 5.392 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,028.44..2,348.20 rows=1 width=952) (actual time=1.844..5.392 rows=7 loops=1)

7. 0.031 5.272 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,028.17..2,339.90 rows=1 width=943) (actual time=1.821..5.272 rows=7 loops=1)

  • Join Filter: (blm.bill_label_id = b.bill_label_id)
  • Rows Removed by Join Filter: 7
8. 0.020 5.234 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,028.17..2,338.88 rows=1 width=825) (actual time=1.814..5.234 rows=7 loops=1)

9. 0.016 5.151 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,028.03..2,338.71 rows=1 width=614) (actual time=1.797..5.151 rows=7 loops=1)

10. 0.014 5.093 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,027.76..2,338.41 rows=1 width=604) (actual time=1.774..5.093 rows=7 loops=1)

11. 0.012 5.079 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,027.62..2,338.25 rows=1 width=395) (actual time=1.770..5.079 rows=7 loops=1)

12. 0.230 5.060 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,027.34..2,334.18 rows=1 width=377) (actual time=1.767..5.060 rows=7 loops=1)

  • Join Filter: ((adm.patient_id)::text = (b.visit_id)::text)
  • Rows Removed by Join Filter: 987
13. 0.019 4.704 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,027.34..2,329.62 rows=1 width=373) (actual time=1.706..4.704 rows=7 loops=1)

14. 0.012 4.636 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,027.19..2,321.44 rows=1 width=356) (actual time=1.695..4.636 rows=7 loops=1)

15. 0.027 4.624 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,027.05..2,321.27 rows=1 width=337) (actual time=1.692..4.624 rows=7 loops=1)

16. 0.014 4.541 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,026.91..2,321.10 rows=1 width=318) (actual time=1.677..4.541 rows=7 loops=1)

17. 0.288 4.401 ↓ 7.0 7 1

Nested Loop (cost=1,026.62..2,312.79 rows=1 width=301) (actual time=1.656..4.401 rows=7 loops=1)

  • Join Filter: ((b.bill_rate_plan_id)::text = (od.org_id)::text)
  • Rows Removed by Join Filter: 1288
18. 2.784 3.945 ↓ 7.0 7 1

Nested Loop Left Join (cost=1,026.62..2,305.63 rows=1 width=298) (actual time=1.579..3.945 rows=7 loops=1)

  • Filter: patient_confidentiality_check(COALESCE(p.patient_group, 0), p.mr_no)
19. 0.214 1.021 ↓ 3.5 7 1

Nested Loop Left Join (cost=1,026.34..2,304.47 rows=2 width=258) (actual time=0.783..1.021 rows=7 loops=1)

  • -> Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..8.44 rows=1 width=100) (actual time=0.026..0.027 rows=1 loo (...)
20. 0.071 0.807 ↓ 3.5 7 1

Bitmap Heap Scan on bill b (cost=1,025.92..2,287.58 rows=2 width=167) (actual time=0.751..0.807 rows=7 loops=1)

  • Recheck Cond: ((status = 'A'::bpchar) AND ((open_date)::date >= '2019-03-06'::date))
  • Filter: ((visit_type = ANY ('{i,o}'::bpchar[])) AND (CASE WHEN (total_amount < 0::numeric) THEN 'Y'::text ELSE 'N'::text END = 'N'::text))
  • Rows Removed by Filter: 1
  • Index Cond: ((b.visit_id)::text = (patient_id)::text)
21. 0.115 0.736 ↓ 0.0 0 1

BitmapAnd (cost=1,025.92..1,025.92 rows=400 width=0) (actual time=0.736..0.736 rows=0 loops=1)

22. 0.597 0.597 ↓ 2.6 3,107 1

Bitmap Index Scan on bill_status_idx (cost=0.00..25.41 rows=1,199 width=0) (actual time=0.597..0.597 rows=3,107 loops=1)

  • Index Cond: (status = 'A'::bpchar)
23. 0.024 0.024 ↑ 386.3 138 1

Bitmap Index Scan on idx_bill_open_date (cost=0.00..1,000.25 rows=53,311 width=0) (actual time=0.024..0.024 rows=138 loops=1)

  • Index Cond: ((open_date)::date >= '2019-03-06'::date)
24. 0.140 0.140 ↑ 1.0 1 7

Index Scan using patient_details_pkey on patient_details p (cost=0.29..0.32 rows=1 width=49) (actual time=0.019..0.020 rows=1 loops=7)

  • Index Cond: ((pr.mr_no)::text = (mr_no)::text)
25. 0.168 0.168 ↑ 1.0 185 7

Seq Scan on organization_details od (cost=0.00..4.85 rows=185 width=19) (actual time=0.004..0.024 rows=185 loops=7)

26. 0.126 0.126 ↓ 0.0 0 7

Index Scan using pharmacy_retail_customers_pkey on store_retail_customers phc (cost=0.29..8.30 rows=1 width=26) (actual time=0.018..0.018 rows=0 loops=7)

  • Index Cond: ((b.visit_id)::text = (customer_id)::text)
27. 0.056 0.056 ↑ 1.0 1 7

Index Scan using tpa_master_pkey on tpa_master tpap (cost=0.14..0.16 rows=1 width=29) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: ((tpa_id)::text = (pr.primary_sponsor_id)::text)
28. 0.000 0.000 ↓ 0.0 0 7

Index Scan using tpa_master_pkey on tpa_master tpas (cost=0.14..0.16 rows=1 width=29) (actual time=0.000..0.000 rows=0 loops=7)

  • Index Cond: ((tpa_id)::text = (pr.secondary_sponsor_id)::text)
29. 0.049 0.049 ↓ 0.0 0 7

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.14..8.16 rows=1 width=26) (actual time=0.007..0.007 rows=0 loops=7)

  • Index Cond: ((incoming_visit_id)::text = (b.visit_id)::text)
30. 0.126 0.126 ↓ 1.2 141 7

Seq Scan on admission adm (cost=0.00..3.14 rows=114 width=13) (actual time=0.003..0.018 rows=141 loops=7)

31. 0.007 0.007 ↓ 0.0 0 7

Index Scan using bed_names_pkey on bed_names bn (cost=0.29..4.06 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=7)

  • Index Cond: (bed_id = adm.bed_id)
32. 0.000 0.000 ↓ 0.0 0 7

Index Scan using ward_names_pkey on ward_names wn (cost=0.14..0.16 rows=1 width=436) (actual time=0.000..0.000 rows=0 loops=7)

  • Index Cond: ((ward_no)::text = (bn.ward_no)::text)
33. 0.042 0.042 ↓ 0.0 0 7

Index Scan using doctors_pkey on doctors doc (cost=0.27..0.29 rows=1 width=21) (actual time=0.005..0.006 rows=0 loops=7)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
34. 0.063 0.063 ↑ 1.0 1 7

Index Scan using department_pkey on department dep (cost=0.14..0.16 rows=1 width=256) (actual time=0.009..0.009 rows=1 loops=7)

  • Index Cond: ((dept_id)::text = (pr.dept_name)::text)
35. 0.007 0.007 ↑ 1.0 1 7

Seq Scan on bill_label_master blm (cost=0.00..1.01 rows=1 width=122) (actual time=0.001..0.001 rows=1 loops=7)

36. 0.105 0.105 ↑ 1.0 1 7

Index Scan using bill_claim_bill_no_idx on bill_claim pbc (cost=0.27..8.29 rows=1 width=19) (actual time=0.014..0.015 rows=1 loops=7)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (priority = 1)
37. 0.063 0.063 ↑ 1.0 1 7

Index Scan using insurance_claim_id_index on insurance_claim pic (cost=0.14..0.23 rows=1 width=20) (actual time=0.008..0.009 rows=1 loops=7)

  • Index Cond: ((claim_id)::text = (pbc.claim_id)::text)
38. 0.084 0.084 ↓ 0.0 0 7

Index Scan using bill_claim_bill_no_idx on bill_claim sbc (cost=0.27..8.29 rows=1 width=19) (actual time=0.012..0.012 rows=0 loops=7)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
39. 0.000 0.000 ↓ 0.0 0 7

Index Scan using insurance_claim_id_index on insurance_claim sic (cost=0.14..0.62 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=7)

  • Index Cond: ((claim_id)::text = (sbc.claim_id)::text)