explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O44W

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,976.330 ↓ 0.0 0 1

GroupAggregate (cost=177,614.27..177,614.30 rows=1 width=47) (actual time=1,976.330..1,976.330 rows=0 loops=1)

2. 0.009 1,976.329 ↓ 0.0 0 1

Sort (cost=177,614.27..177,614.28 rows=1 width=47) (actual time=1,976.329..1,976.329 rows=0 loops=1)

  • Sort Key: dr.doctor_name, chc.chargehead_name
  • Sort Method: quicksort Memory: 25kB
3. 0.000 1,976.320 ↓ 0.0 0 1

Nested Loop (cost=0.86..177,614.26 rows=1 width=47) (actual time=1,976.320..1,976.320 rows=0 loops=1)

  • Join Filter: (bc.service_sub_group_id = ssg.service_sub_group_id)
4. 0.000 1,976.320 ↓ 0.0 0 1

Nested Loop (cost=0.86..177,609.01 rows=1 width=51) (actual time=1,976.320..1,976.320 rows=0 loops=1)

  • Join Filter: ((bc.charge_head)::text = (chc.chargehead_id)::text)
5. 0.001 1,976.320 ↓ 0.0 0 1

Nested Loop (cost=0.86..177,603.74 rows=1 width=41) (actual time=1,976.320..1,976.320 rows=0 loops=1)

6. 0.014 1,976.319 ↓ 0.0 0 1

Nested Loop (cost=0.43..167,049.33 rows=113 width=33) (actual time=1,976.319..1,976.319 rows=0 loops=1)

  • Join Filter: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 0.073 0.073 ↓ 1.0 201 1

Seq Scan on doctors dr (cost=0.00..10.51 rows=200 width=31) (actual time=0.005..0.073 rows=201 loops=1)

  • Filter: ((doctor_name)::text <> 'NONE'::text)
8. 0.021 1,976.232 ↓ 0.0 0 201

Materialize (cost=0.43..166,652.14 rows=129 width=18) (actual time=9.832..9.832 rows=0 loops=201)

9. 0.001 1,976.211 ↓ 0.0 0 1

Nested Loop (cost=0.43..166,651.49 rows=129 width=18) (actual time=1,976.211..1,976.211 rows=0 loops=1)

10. 1,976.210 1,976.210 ↓ 0.0 0 1

Seq Scan on bill b (cost=0.00..165,560.48 rows=129 width=20) (actual time=1,976.210..1,976.210 rows=0 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND (CASE WHEN is_tpa THEN 'Yes'::text ELSE 'No'::text END = 'No'::text) AND (CASE WHEN (status = 'A'::bpchar) THEN 'Open'::text WHEN (status = 'F'::bpchar) THEN 'Finalized'::text WHEN (status = 'C'::bpchar) THEN 'Closed'::text ELSE 'Cancelled'::text END = ANY ('{F,C}'::text[])))
  • Rows Removed by Filter: 2573994
11. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..8.45 rows=1 width=19) (never executed)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.43..93.39 rows=1 width=28) (never executed)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: ((status <> 'X'::bpchar) AND (act_quantity > 0::numeric) AND (date(posted_date) >= '2019-09-01'::date) AND (date(posted_date) <= '2019-09-30'::date))
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on chargehead_constants chc (cost=0.00..4.65 rows=49 width=22) (never executed)

  • Filter: ((chargehead_name)::text <> ALL ('{"Pharmacy Charge","Pharmacy Medicine","Pharmacy Returns","Pharmacy Sales","Pharmacy Sales Returns","Professional Charge","Bed Charge","Bed Charge (ICU)",Equipment,"Nurse Charge","Nurse Charge (ICU)","Duty Doctor Charge","Duty Doctor Charge (ICU)"}'::text[]))
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on service_sub_groups ssg (cost=0.00..3.81 rows=115 width=4) (never executed)

  • Filter: ((service_sub_group_name)::text <> ALL ('{Others,Miscellaneous,Dialysis,DIALYSIS}'::text[]))