explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W7nF

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,966.100 ↓ 0.0 0 1

GroupAggregate (cost=177,513.83..177,513.86 rows=1 width=47) (actual time=1,966.100..1,966.100 rows=0 loops=1)

2. 0.027 1,966.097 ↓ 0.0 0 1

Sort (cost=177,513.83..177,513.83 rows=1 width=47) (actual time=1,966.097..1,966.097 rows=0 loops=1)

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

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

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

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

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

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

6. 0.018 1,966.064 ↓ 0.0 0 1

Nested Loop (cost=0.43..167,029.34 rows=112 width=33) (actual time=1,966.064..1,966.064 rows=0 loops=1)

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

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

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

Materialize (cost=0.43..166,635.15 rows=128 width=18) (actual time=9.781..9.781 rows=0 loops=201)

9. 0.005 1,965.952 ↓ 0.0 0 1

Nested Loop (cost=0.43..166,634.51 rows=128 width=18) (actual time=1,965.952..1,965.952 rows=0 loops=1)

10. 1,965.947 1,965.947 ↓ 0.0 0 1

Seq Scan on bill b (cost=0.00..165,551.95 rows=128 width=20) (actual time=1,965.947..1,965.947 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: 2574073
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.51 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 ((act_description)::text <> ALL ('{"SYRINGE PUMP CHARGE/HOUR","NEBULIZER CHARGE/ HOUR","MONITOR CHARGE/HOUR","MEDICAL RECORD-DOCUMENTATION CHARGE"}'::text[])) 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[]))