explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cjFY

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 11.585 ↓ 0.0 0 1

Limit (cost=41,785.88..41,785.89 rows=1 width=160) (actual time=11.585..11.585 rows=0 loops=1)

2. 0.051 11.583 ↓ 0.0 0 1

Sort (cost=41,785.88..41,785.89 rows=1 width=160) (actual time=11.583..11.583 rows=0 loops=1)

  • Sort Key: foo.reg_date DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.001 11.532 ↓ 0.0 0 1

Subquery Scan on foo (cost=40,613.55..41,785.87 rows=1 width=160) (actual time=11.532..11.532 rows=0 loops=1)

4. 0.001 11.531 ↓ 0.0 0 1

Group (cost=40,613.55..41,785.86 rows=1 width=302) (actual time=11.531..11.531 rows=0 loops=1)

  • Group Key: pr.patient_id, dept.dept_id, doc.doctor_id, pd.patient_name, s.salutation, pd.middle_name, pd.last_name
  • Filter: (array_to_string((SubPlan 2), ','::text) ~~* '%false%'::text)
5. 0.031 11.530 ↓ 0.0 0 1

Sort (cost=40,613.55..40,613.56 rows=1 width=238) (actual time=11.530..11.530 rows=0 loops=1)

  • Sort Key: pr.patient_id, dept.dept_id, doc.doctor_id, pd.patient_name, s.salutation, pd.middle_name, pd.last_name
  • Sort Method: quicksort Memory: 25kB
6. 0.001 11.499 ↓ 0.0 0 1

Nested Loop (cost=1.42..40,613.54 rows=1 width=238) (actual time=11.499..11.499 rows=0 loops=1)

  • Join Filter: ((doc.dept_id)::text = (dept.dept_id)::text)
7. 0.001 11.498 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.42..40,611.22 rows=1 width=225) (actual time=11.498..11.498 rows=0 loops=1)

  • Join Filter: ((s.salutation_id)::text = (pd.salutation)::text)
8. 0.001 11.497 ↓ 0.0 0 1

Nested Loop (cost=1.42..40,609.74 rows=1 width=116) (actual time=11.497..11.497 rows=0 loops=1)

  • Join Filter: ((pr.doctor)::text = (doc.doctor_id)::text)
9. 0.000 11.496 ↓ 0.0 0 1

Nested Loop (cost=1.42..40,523.85 rows=1 width=85) (actual time=11.496..11.496 rows=0 loops=1)

10. 0.001 11.496 ↓ 0.0 0 1

Nested Loop (cost=0.86..39,351.00 rows=1 width=85) (actual time=11.496..11.496 rows=0 loops=1)

11. 11.495 11.495 ↓ 0.0 0 1

Index Scan using patient_registarion_center_idx on patient_registration pr (cost=0.43..39,342.56 rows=1 width=52) (actual time=11.495..11.495 rows=0 loops=1)

  • Index Cond: (center_id = 201)
  • Filter: (reg_date >= '2019-06-20'::date)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..8.45 rows=1 width=48) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
13. 0.000 0.000 ↓ 0.0 0

Index Scan using pa_patient_id_idx on patient_activities pa (cost=0.56..1,171.76 rows=109 width=16) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using pa_patient_id_idx on patient_activities pa (cost=0.56..1,171.76 rows=109 width=16) (never executed)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND (prescription_type = ANY ('{M,V,E,A}'::bpchar[])))
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors doc (cost=0.00..63.73 rows=1,773 width=38) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on salutation_master s (cost=0.00..1.21 rows=21 width=156) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on department dept (cost=0.00..1.59 rows=59 width=21) (never executed)

18.          

SubPlan (forGroup)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using pa_patient_id_idx on patient_activities pct_1 (cost=0.56..1,172.03 rows=109 width=32) (never executed)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND (prescription_type = ANY ('{M,V,E,A}'::bpchar[])))
20. 0.000 0.000 ↓ 0.0 0

Index Scan using pa_patient_id_idx on patient_activities pct (cost=0.56..1,172.03 rows=109 width=32) (never executed)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND (prescription_type = ANY ('{M,V,E,A}'::bpchar[])))
Planning time : 33.351 ms
Execution time : 14.811 ms