explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9dex

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 25,311.319 ↓ 28.0 28 1

Nested Loop (cost=8.84..130,437.00 rows=1 width=254) (actual time=1,128.124..25,311.319 rows=28 loops=1)

  • Total runtime: 25311.614 ms
2. 0.874 25,310.721 ↓ 28.0 28 1

Nested Loop (cost=8.84..130,432.65 rows=1 width=246) (actual time=1,128.105..25,310.721 rows=28 loops=1)

  • Join Filter: (bc.service_sub_group_id = ssg.service_sub_group_id)
  • Rows Removed by Join Filter: 4368
3. 0.439 25,309.847 ↓ 28.0 28 1

Nested Loop Left Join (cost=8.84..130,427.12 rows=1 width=234) (actual time=1,128.076..25,309.847 rows=28 loops=1)

  • Join Filter: ((dept.dept_id)::text = (d.dept_id)::text)
  • Rows Removed by Join Filter: 1626
4. 11.258 25,309.408 ↓ 28.0 28 1

Nested Loop Left Join (cost=8.84..130,424.79 rows=1 width=229) (actual time=1,128.067..25,309.408 rows=28 loops=1)

  • Join Filter: ((sp.doctor_id)::text = (d.doctor_id)::text)
  • Rows Removed by Join Filter: 49450
5. 0.082 25,298.150 ↓ 28.0 28 1

Nested Loop Left Join (cost=8.84..130,258.03 rows=1 width=206) (actual time=1,127.863..25,298.150 rows=28 loops=1)

6. 0.120 25,298.068 ↓ 28.0 28 1

Nested Loop Left Join (cost=8.40..130,253.46 rows=1 width=202) (actual time=1,127.861..25,298.068 rows=28 loops=1)

7. 0.286 25,297.948 ↓ 28.0 28 1

Nested Loop Left Join (cost=7.97..130,248.93 rows=1 width=198) (actual time=1,127.858..25,297.948 rows=28 loops=1)

8. 0.748 25,297.662 ↓ 28.0 28 1

Nested Loop (cost=7.41..130,244.22 rows=1 width=198) (actual time=1,127.849..25,297.662 rows=28 loops=1)

9. 36.596 25,296.914 ↓ 28.0 28 1

Nested Loop Left Join (cost=6.98..130,237.76 rows=1 width=172) (actual time=1,127.821..25,296.914 rows=28 loops=1)

  • Join Filter: ((bc.physiotherapist_user)::text = (u.emp_username)::text)
  • Rows Removed by Join Filter: 265469
10. 0.259 25,225.654 ↓ 28.0 28 1

Nested Loop Left Join (cost=6.98..129,015.46 rows=1 width=154) (actual time=1,127.752..25,225.654 rows=28 loops=1)

11. 0.100 3.611 ↓ 28.0 28 1

Nested Loop (cost=2.24..181.61 rows=1 width=141) (actual time=0.133..3.611 rows=28 loops=1)

12. 0.054 2.979 ↓ 28.0 28 1

Nested Loop (cost=1.68..176.41 rows=1 width=142) (actual time=0.111..2.979 rows=28 loops=1)

13. 0.109 2.155 ↓ 55.0 55 1

Nested Loop (cost=1.11..13.17 rows=1 width=51) (actual time=0.087..2.155 rows=55 loops=1)

14. 0.326 0.326 ↓ 215.0 215 1

Index Scan using patient_registarion_center_idx on patient_registration pr (cost=0.56..6.58 rows=1 width=37) (actual time=0.019..0.326 rows=215 loops=1)

15. 1.720 1.720 ↓ 0.0 0 215

Index Scan using bill_visit_id_idx on bill b (cost=0.56..6.59 rows=1 width=29) (actual time=0.007..0.008 rows=0 loops=215)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: (((finalized_date)::date >= '2019-05-01'::date) AND ((finalized_date)::date <= '2019-05-03'::date))
  • Rows Removed by Filter: 1
16. 0.770 0.770 ↑ 2.0 1 55

Index Scan using revenue_allocation_base_bill_no_idx on revenue_allocation_base (cost=0.56..163.22 rows=2 width=91) (actual time=0.012..0.014 rows=1 loops=55)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (service_group = ANY ('{Physiotherapy,"Speech Therapy","Occupational Therapy","Respiratory Therapy",Cosmetology}'::text[]))
  • Rows Removed by Filter: 1
17. 0.532 0.532 ↑ 1.0 1 28

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..5.19 rows=1 width=37) (actual time=0.017..0.019 rows=1 loops=28)

  • Index Cond: ((charge_id)::text = (revenue_allocation_base.line_number)::text)
  • Filter: ((revenue_allocation_base.bill_no)::text = (bill_no)::text)
18. 19,116.430 25,221.784 ↑ 1.0 1 28

Hash Right Join (cost=4.74..128,833.84 rows=1 width=33) (actual time=890.455..900.778 rows=1 loops=28)

  • Hash Cond: (((sp.prescription_id)::character varying)::text = (bac.activity_id)::text)
19. 6,104.878 6,104.878 ↑ 1.0 3,372,971 26

Seq Scan on services_prescribed sp (cost=0.00..107,744.44 rows=3,373,544 width=23) (actual time=0.002..234.803 rows=3,372,971 loops=26)

20. 0.056 0.476 ↑ 1.0 1 28

Hash (cost=4.73..4.73 rows=1 width=17) (actual time=0.017..0.017 rows=1 loops=28)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.420 0.420 ↑ 1.0 1 28

Index Scan using bac_charge_id_index on bill_activity_charge bac (cost=0.56..4.73 rows=1 width=17) (actual time=0.015..0.015 rows=1 loops=28)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
22. 34.664 34.664 ↓ 1.0 9,482 28

Seq Scan on u_user u (cost=0.00..1,103.80 rows=9,480 width=27) (actual time=0.004..1.238 rows=9,482 loops=28)

23. 0.000 0.588 ↑ 1.0 1 28

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..6.45 rows=1 width=41) (actual time=0.020..0.021 rows=1 loops=28)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
24. 0.224 0.224 ↓ 0.0 0 28

Index Scan using order_no_index on patient_activities pa (cost=0.56..4.70 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=28)

  • Index Cond: (order_no = sp.prescription_id)
25. 0.056 0.056 ↓ 0.0 0 28

Index Scan using ip_prescription_pkey on ip_prescription ip (cost=0.43..4.52 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=28)

  • Index Cond: (prescription_id = pa.prescription_id)
26. 0.028 0.028 ↓ 0.0 0 28

Index Scan using patient_prescription_pkey on patient_prescription pp (cost=0.44..4.56 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=28)

  • Index Cond: (patient_presc_id = sp.doc_presc_id)
27. 4.984 4.984 ↑ 1.0 1,767 28

Seq Scan on doctors d (cost=0.00..144.67 rows=1,767 width=38) (actual time=0.009..0.178 rows=1,767 loops=28)

28. 0.140 0.140 ↑ 1.0 59 28

Seq Scan on department dept (cost=0.00..1.59 rows=59 width=21) (actual time=0.002..0.005 rows=59 loops=28)

29. 0.308 0.308 ↑ 1.0 157 28

Seq Scan on service_sub_groups ssg (cost=0.00..3.57 rows=157 width=20) (actual time=0.002..0.011 rows=157 loops=28)

30. 0.224 0.224 ↑ 1.0 1 28

Seq Scan on hospital_center_master hcm (cost=0.00..4.31 rows=1 width=16) (actual time=0.002..0.008 rows=1 loops=28)

  • Filter: (center_id = 24)
  • Rows Removed by Filter: 24