explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MWXt

Settings
# exclusive inclusive rows x rows loops node
1. 69.049 180,812.758 ↓ 775.0 775 1

GroupAggregate (cost=177,513.83..177,513.86 rows=1 width=47) (actual time=180,701.495..180,812.758 rows=775 loops=1)

2. 1,083.681 180,743.709 ↓ 92,045.0 92,045 1

Sort (cost=177,513.83..177,513.83 rows=1 width=47) (actual time=180,701.470..180,743.709 rows=92,045 loops=1)

  • Sort Key: dr.doctor_name, chc.chargehead_name
  • Sort Method: external merge Disk: 5200kB
3. 943.513 179,660.028 ↓ 92,045.0 92,045 1

Nested Loop (cost=0.86..177,513.82 rows=1 width=47) (actual time=23,630.217..179,660.028 rows=92,045 loops=1)

  • Join Filter: (bc.service_sub_group_id = ssg.service_sub_group_id)
  • Rows Removed by Join Filter: 10742239
4. 780.794 176,288.141 ↓ 93,399.0 93,399 1

Nested Loop (cost=0.86..177,508.56 rows=1 width=51) (actual time=23,630.202..176,288.141 rows=93,399 loops=1)

  • Join Filter: ((bc.charge_head)::text = (chc.chargehead_id)::text)
  • Rows Removed by Join Filter: 5974271
5. 0.000 172,783.087 ↓ 123,830.0 123,830 1

Nested Loop (cost=0.86..177,503.30 rows=1 width=41) (actual time=23,630.177..172,783.087 rows=123,830 loops=1)

6. 63,122.820 134,784.620 ↓ 21,246.2 2,379,574 1

Nested Loop (cost=0.43..167,029.34 rows=112 width=33) (actual time=15,063.589..134,784.620 rows=2,379,574 loops=1)

  • Join Filter: ((pr.doctor)::text = (dr.doctor_id)::text)
  • Rows Removed by Join Filter: 504036509
7. 0.476 0.476 ↓ 1.0 201 1

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

  • Filter: ((doctor_name)::text <> 'NONE'::text)
8. 49,887.539 71,661.324 ↓ 19,683.5 2,519,483 201

Materialize (cost=0.43..166,635.15 rows=128 width=18) (actual time=0.004..356.524 rows=2,519,483 loops=201)

9. 1,559.375 21,773.785 ↓ 19,683.5 2,519,483 1

Nested Loop (cost=0.43..166,634.51 rows=128 width=18) (actual time=0.092..21,773.785 rows=2,519,483 loops=1)

10. 2,505.306 2,505.306 ↓ 19,764.6 2,529,872 1

Seq Scan on bill b (cost=0.00..165,551.95 rows=128 width=20) (actual time=0.034..2,505.306 rows=2,529,872 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 ('{Finalized,Closed}'::text[])))
  • Rows Removed by Filter: 45021
11. 17,709.104 17,709.104 ↑ 1.0 1 2,529,872

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..8.45 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=2,529,872)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
12. 38,073.184 38,073.184 ↓ 0.0 0 2,379,574

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.43..93.51 rows=1 width=28) (actual time=0.016..0.016 rows=0 loops=2,379,574)

  • 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))
  • Rows Removed by Filter: 2
13. 2,724.260 2,724.260 ↑ 1.0 49 123,830

Seq Scan on chargehead_constants chc (cost=0.00..4.65 rows=49 width=22) (actual time=0.001..0.022 rows=49 loops=123,830)

  • 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[]))
  • Rows Removed by Filter: 14
14. 2,428.374 2,428.374 ↓ 1.0 116 93,399

Seq Scan on service_sub_groups ssg (cost=0.00..3.81 rows=115 width=4) (actual time=0.001..0.026 rows=116 loops=93,399)

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