explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XZOB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 56,400.764 ↓ 3,356.0 3,356 1

Nested Loop (cost=2,054,428.17..2,177,835.06 rows=1 width=225) (actual time=53,513.438..56,400.764 rows=3,356 loops=1)

  • Total runtime: 56402.549 ms
2. 8.208 56,369.080 ↓ 3,356.0 3,356 1

Nested Loop (cost=2,054,428.17..2,177,829.65 rows=1 width=229) (actual time=53,513.374..56,369.080 rows=3,356 loops=1)

3. 14.397 56,360.872 ↓ 3,356.0 3,356 1

Nested Loop (cost=2,054,428.02..2,177,826.47 rows=1 width=217) (actual time=53,513.363..56,360.872 rows=3,356 loops=1)

4. 23.740 56,346.475 ↓ 3,356.0 3,356 1

Nested Loop (cost=2,054,427.88..2,177,824.80 rows=1 width=212) (actual time=53,513.347..56,346.475 rows=3,356 loops=1)

5. 205.422 56,322.735 ↓ 3,356.0 3,356 1

Nested Loop (cost=2,054,427.61..2,177,820.93 rows=1 width=189) (actual time=53,513.307..56,322.735 rows=3,356 loops=1)

6. 1,348.444 56,117.313 ↓ 3,356.0 3,356 1

Nested Loop (cost=2,054,427.18..2,177,816.71 rows=1 width=163) (actual time=53,512.701..56,117.313 rows=3,356 loops=1)

7. 90.046 54,768.869 ↓ 13,136.0 13,136 1

Nested Loop Left Join (cost=2,054,426.62..2,177,811.56 rows=1 width=141) (actual time=53,511.615..54,768.869 rows=13,136 loops=1)

8. 1,458.236 54,678.823 ↓ 13,136.0 13,136 1

Hash Join (cost=2,054,426.33..2,177,807.57 rows=1 width=123) (actual time=53,511.580..54,678.823 rows=13,136 loops=1)

  • Hash Cond: (((sp.prescription_id)::character varying)::text = (bac.activity_id)::text)
9. 1,017.691 1,017.691 ↑ 1.0 3,231,477 1

Seq Scan on services_prescribed sp (cost=0.00..103,183.83 rows=3,231,583 width=19) (actual time=0.422..1,017.691 rows=3,231,477 loops=1)

10. 28.164 52,202.896 ↓ 13,136.0 13,136 1

Hash (cost=2,054,426.32..2,054,426.32 rows=1 width=115) (actual time=52,202.896..52,202.896 rows=13,136 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2887kB
11. 2,605.747 52,174.732 ↓ 13,136.0 13,136 1

Nested Loop (cost=75,355.64..2,054,426.32 rows=1 width=115) (actual time=27,864.677..52,174.732 rows=13,136 loops=1)

  • Join Filter: ((revenue_allocation_base.line_number)::text = (bac.charge_id)::text)
12. 4,717.524 49,568.985 ↓ 14,168.0 14,168 1

Nested Loop (cost=75,355.07..2,054,421.90 rows=1 width=128) (actual time=27,864.654..49,568.985 rows=14,168 loops=1)

13. 19,494.203 44,851.461 ↓ 1.4 14,168 1

Hash Join (cost=75,354.51..2,003,095.06 rows=10,190 width=119) (actual time=27,864.064..44,851.461 rows=14,168 loops=1)

  • Hash Cond: ((revenue_allocation_base.bill_no)::text = (b.bill_no)::text)
14. 25,357.258 25,357.258 ↓ 1.0 698,406 1

Seq Scan on revenue_allocation_base (cost=0.00..1,920,699.99 rows=693,866 width=90) (actual time=15.084..25,357.258 rows=698,406 loops=1)

  • Filter: (service_group = ANY ('{Physiotherapy,"Speech Therapy","Occupational Therapy","Respiratory Therapy",Cosmetology}'::text[]))
  • Rows Removed by Filter: 27507001
15. 0.000 19,059.571 ↓ 1.0 96,715 1

Hash (cost=74,158.40..74,158.40 rows=95,689 width=29) (actual time=19,059.571..19,059.571 rows=96,715 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 5951kB
16. 18,926.219 18,926.219 ↓ 1.0 96,715 1

Index Scan using bill_finalized_date_idx on bill b (cost=0.56..74,158.40 rows=95,689 width=29) (actual time=13.337..18,926.219 rows=96,715 loops=1)

  • Index Cond: (((finalized_date)::date >= '2019-04-01'::date) AND ((finalized_date)::date <= '2019-04-30'::date))
17. 4,703.776 4,703.776 ↑ 1.0 1 14,168

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..5.03 rows=1 width=37) (actual time=0.329..0.332 rows=1 loops=14,168)

  • Index Cond: ((charge_id)::text = (revenue_allocation_base.line_number)::text)
  • Filter: ((revenue_allocation_base.bill_no)::text = (bill_no)::text)
18. 2,578.576 2,578.576 ↑ 1.0 1 14,168

Index Scan using bac_charge_id_index on bill_activity_charge bac (cost=0.56..4.41 rows=1 width=17) (actual time=0.180..0.182 rows=1 loops=14,168)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
19. 78.816 78.816 ↑ 1.0 1 13,136

Index Scan using u_user_pk on u_user u (cost=0.29..3.98 rows=1 width=27) (actual time=0.006..0.006 rows=1 loops=13,136)

  • Index Cond: ((bc.physiotherapist_user)::text = (emp_username)::text)
20. 1,339.872 1,339.872 ↓ 0.0 0 13,136

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..5.15 rows=1 width=37) (actual time=0.102..0.102 rows=0 loops=13,136)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
  • Filter: (center_id = 9)
  • Rows Removed by Filter: 1
21. 201.360 201.360 ↑ 1.0 1 3,356

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..4.21 rows=1 width=41) (actual time=0.060..0.060 rows=1 loops=3,356)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
22. 20.136 20.136 ↑ 1.0 1 3,356

Index Scan using doctors_pkey on doctors d (cost=0.28..3.86 rows=1 width=38) (actual time=0.006..0.006 rows=1 loops=3,356)

  • Index Cond: ((doctor_id)::text = (sp.doctor_id)::text)
23. 10.068 10.068 ↑ 1.0 1 3,356

Index Scan using department_pkey on department dept (cost=0.14..1.66 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=3,356)

  • Index Cond: ((dept_id)::text = (d.dept_id)::text)
24. 0.000 6.712 ↑ 1.0 1 3,356

Index Scan using service_sub_groups_pkey on service_sub_groups ssg (cost=0.14..3.17 rows=1 width=20) (actual time=0.001..0.002 rows=1 loops=3,356)

  • Index Cond: (service_sub_group_id = bc.service_sub_group_id)
25. 20.136 20.136 ↑ 1.0 1 3,356

Seq Scan on hospital_center_master hcm (cost=0.00..5.38 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=3,356)

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