explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Oqa

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 680.672 ↑ 20.0 1 1

Limit (cost=558,082.56..558,082.61 rows=20 width=160) (actual time=680.671..680.672 rows=1 loops=1)

2. 0.002 680.670 ↑ 83.0 1 1

Sort (cost=558,082.56..558,082.77 rows=83 width=160) (actual time=680.670..680.670 rows=1 loops=1)

  • Sort Key: foo.reg_date DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.002 680.660 ↑ 83.0 1 1

Subquery Scan on foo (cost=460,778.34..558,080.35 rows=83 width=160) (actual time=680.659..680.660 rows=1 loops=1)

4. 0.062 680.658 ↑ 83.0 1 1

Group (cost=460,778.34..558,079.52 rows=83 width=302) (actual time=680.657..680.658 rows=1 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.014 680.579 ↑ 83.0 1 1

Sort (cost=460,778.34..460,778.54 rows=83 width=238) (actual time=680.579..680.579 rows=1 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.010 680.565 ↑ 83.0 1 1

Hash Join (cost=28,125.53..460,775.69 rows=83 width=238) (actual time=680.514..680.565 rows=1 loops=1)

  • Hash Cond: ((doc.dept_id)::text = (dept.dept_id)::text)
7. 0.012 680.479 ↑ 83.0 1 1

Hash Left Join (cost=28,123.20..460,773.13 rows=83 width=225) (actual time=680.428..680.479 rows=1 loops=1)

  • Hash Cond: ((pd.salutation)::text = (s.salutation_id)::text)
8. 0.024 680.452 ↑ 83.0 1 1

Hash Join (cost=28,121.73..460,771.40 rows=83 width=116) (actual time=680.400..680.452 rows=1 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
9. 0.004 678.515 ↑ 83.0 1 1

Nested Loop (cost=28,035.84..460,685.29 rows=83 width=85) (actual time=678.464..678.515 rows=1 loops=1)

10. 0.024 678.500 ↑ 83.0 1 1

Nested Loop (cost=28,035.41..459,984.15 rows=83 width=52) (actual time=678.449..678.500 rows=1 loops=1)

11. 587.163 678.350 ↑ 6.9 18 1

Bitmap Heap Scan on patient_registration pr (cost=28,034.85..325,288.61 rows=124 width=52) (actual time=678.342..678.350 rows=18 loops=1)

  • Recheck Cond: (center_id = 9)
  • Filter: (reg_date >= '2019-06-20'::date)
  • Rows Removed by Filter: 1521494
  • Heap Blocks: exact=266308
12. 91.187 91.187 ↓ 1.0 1,521,515 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..28,034.81 rows=1,517,651 width=0) (actual time=91.187..91.187 rows=1,521,515 loops=1)

13. 0.126 0.126 ↓ 0.0 0 18

Index Scan using pa_patient_id_idx on patient_activities pa (cost=0.56..1,085.16 rows=109 width=16) (actual time=0.007..0.007 rows=0 loops=18)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND (prescription_type = ANY ('{M,V,E,A}'::bpchar[])))
  • Rows Removed by Filter: 1
14. 0.011 0.011 ↑ 1.0 1 1

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..8.45 rows=1 width=48) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
15. 0.991 1.913 ↑ 1.0 1,773 1

Hash (cost=63.73..63.73 rows=1,773 width=38) (actual time=1.913..1.913 rows=1,773 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 139kB
16. 0.922 0.922 ↑ 1.0 1,773 1

Seq Scan on doctors doc (cost=0.00..63.73 rows=1,773 width=38) (actual time=0.010..0.922 rows=1,773 loops=1)

17. 0.005 0.015 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=156) (actual time=0.015..0.015 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.010 0.010 ↑ 1.0 21 1

Seq Scan on salutation_master s (cost=0.00..1.21 rows=21 width=156) (actual time=0.007..0.010 rows=21 loops=1)

19. 0.048 0.076 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.076..0.076 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
20. 0.028 0.028 ↑ 1.0 59 1

Seq Scan on department dept (cost=0.00..1.59 rows=59 width=21) (actual time=0.011..0.028 rows=59 loops=1)

21.          

SubPlan (forGroup)

22. 0.009 0.009 ↑ 109.0 1 1

Index Scan using pa_patient_id_idx on patient_activities pct_1 (cost=0.56..1,172.03 rows=109 width=32) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND (prescription_type = ANY ('{M,V,E,A}'::bpchar[])))
  • Rows Removed by Filter: 3
23. 0.008 0.008 ↑ 109.0 1 1

Index Scan using pa_patient_id_idx on patient_activities pct (cost=0.56..1,172.03 rows=109 width=32) (actual time=0.007..0.008 rows=1 loops=1)

24. 0.008 0.008 ↑ 109.0 1 1

Index Scan using pa_patient_id_idx on patient_activities pct (cost=0.56..1,172.03 rows=109 width=32) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND (prescription_type = ANY ('{M,V,E,A}'::bpchar[])))
  • Rows Removed by Filter: 3