explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 82LK

Settings
# exclusive inclusive rows x rows loops node
1. 0.098 167,859.439 ↑ 1.0 20 1

Limit (cost=13,607,670.16..13,607,670.21 rows=20 width=160) (actual time=167,859.341..167,859.439 rows=20 loops=1)

2. 0.134 167,859.341 ↑ 681.8 20 1

Sort (cost=13,607,670.16..13,607,704.25 rows=13,635 width=160) (actual time=167,859.339..167,859.341 rows=20 loops=1)

  • Sort Key: foo.reg_date DESC
  • Sort Method: quicksort Memory: 35kB
3. 0.029 167,859.207 ↑ 505.0 27 1

Subquery Scan on foo (cost=1,412,200.82..13,607,307.34 rows=13,635 width=160) (actual time=167,770.247..167,859.207 rows=27 loops=1)

4. 16.156 167,859.178 ↑ 505.0 27 1

Group (cost=1,412,200.82..13,606,898.29 rows=13,635 width=190) (actual time=167,770.238..167,859.178 rows=27 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)
  • Rows Removed by Filter: 559
5. 34.826 167,676.099 ↑ 1.7 7,839 1

Sort (cost=1,412,200.82..1,412,234.91 rows=13,635 width=126) (actual time=167,674.610..167,676.099 rows=7,839 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: 2266kB
6. 6.006 167,641.273 ↑ 1.7 7,839 1

Hash Join (cost=673,375.24..1,411,264.44 rows=13,635 width=126) (actual time=149,045.500..167,641.273 rows=7,839 loops=1)

  • Hash Cond: ((doc.dept_id)::text = (dept.dept_id)::text)
7. 5.808 167,635.223 ↑ 1.7 7,839 1

Hash Left Join (cost=673,370.49..1,411,216.83 rows=13,635 width=113) (actual time=149,045.356..167,635.223 rows=7,839 loops=1)

  • Hash Cond: ((pd.salutation)::text = (s.salutation_id)::text)
8. 13.242 167,629.370 ↑ 1.7 7,839 1

Nested Loop (cost=673,368.18..1,411,158.52 rows=13,635 width=117) (actual time=149,045.208..167,629.370 rows=7,839 loops=1)

9. 6.635 167,467.187 ↑ 1.7 7,839 1

Hash Join (cost=673,367.75..1,361,354.81 rows=13,635 width=83) (actual time=149,044.371..167,467.187 rows=7,839 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
10. 1,738.041 167,458.634 ↑ 1.7 7,839 1

Hash Join (cost=673,151.25..1,361,102.30 rows=13,644 width=53) (actual time=149,042.353..167,458.634 rows=7,839 loops=1)

  • Hash Cond: ((pa.patient_id)::text = (pr.patient_id)::text)
11. 135,607.444 158,320.240 ↑ 1.1 5,436,112 1

Bitmap Heap Scan on patient_activities pa (cost=202,644.17..875,247.22 rows=5,846,845 width=16) (actual time=22,781.293..158,320.240 rows=5,436,112 loops=1)

  • Recheck Cond: (activity_status = 'D'::bpchar)
  • Filter: ((prescription_type = 'M'::bpchar) OR (prescription_type = 'V'::bpchar) OR (prescription_type = 'E'::bpchar) OR (prescription_type = 'A'::bpchar))
  • Rows Removed by Filter: 2063215
  • Heap Blocks: exact=335524
12. 22,712.796 22,712.796 ↓ 1.0 7,500,450 1

Bitmap Index Scan on patient_activities_as (cost=0.00..201,182.45 rows=7,426,119 width=0) (actual time=22,712.796..22,712.796 rows=7,500,450 loops=1)

  • Index Cond: (activity_status = 'D'::bpchar)
13. 8.250 7,400.353 ↓ 1.0 21,089 1

Hash (cost=469,845.80..469,845.80 rows=20,347 width=53) (actual time=7,400.353..7,400.353 rows=21,089 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2028kB
14. 1,672.004 7,392.103 ↓ 1.0 21,089 1

Bitmap Heap Scan on patient_registration pr (cost=26,018.69..469,845.80 rows=20,347 width=53) (actual time=5,788.592..7,392.103 rows=21,089 loops=1)

  • Recheck Cond: (center_id = 13)
  • Filter: (reg_date >= '2020-01-18'::date)
  • Rows Removed by Filter: 1048457
  • Heap Blocks: exact=324930
15. 5,720.099 5,720.099 ↓ 1.0 1,082,819 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..26,013.60 rows=1,062,289 width=0) (actual time=5,720.099..5,720.099 rows=1,082,819 loops=1)

  • Index Cond: (center_id = 13)
16. 0.708 1.918 ↑ 1.0 2,104 1

Hash (cost=148.12..148.12 rows=2,104 width=38) (actual time=1.918..1.918 rows=2,104 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 178kB
17. 1.210 1.210 ↑ 1.0 2,104 1

Seq Scan on doctors doc (cost=0.00..148.12 rows=2,104 width=38) (actual time=0.021..1.210 rows=2,104 loops=1)

18. 148.941 148.941 ↑ 1.0 1 7,839

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..3.65 rows=1 width=49) (actual time=0.019..0.019 rows=1 loops=7,839)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
19. 0.018 0.045 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.045..0.045 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.027 0.027 ↑ 1.0 21 1

Seq Scan on salutation_master s (cost=0.00..1.63 rows=21 width=14) (actual time=0.020..0.027 rows=21 loops=1)

21. 0.027 0.044 ↑ 1.0 60 1

Hash (cost=2.80..2.80 rows=60 width=21) (actual time=0.044..0.044 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
22. 0.017 0.017 ↑ 1.0 60 1

Seq Scan on department dept (cost=0.00..2.80 rows=60 width=21) (actual time=0.009..0.017 rows=60 loops=1)

23.          

SubPlan (for Group)

24. 163.494 163.494 ↑ 11.2 13 586

Index Scan using idx_patient_op_activities on patient_activities pct_1 (cost=0.56..894.09 rows=146 width=32) (actual time=0.035..0.279 rows=13 loops=586)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND ((prescription_type = 'M'::bpchar) OR (prescription_type = 'V'::bpchar) OR (prescription_type = 'E'::bpchar) OR (prescription_type = 'A'::bpchar)))
  • Rows Removed by Filter: 20
25. 3.429 3.429 ↑ 5.8 25 27

Index Scan using idx_patient_op_activities on patient_activities pct (cost=0.56..894.09 rows=146 width=32) (actual time=0.031..0.127 rows=25 loops=27)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: ((activity_status = 'D'::bpchar) AND ((prescription_type = 'M'::bpchar) OR (prescription_type = 'V'::bpchar) OR (prescription_type = 'E'::bpchar) OR (prescription_type = 'A'::bpchar)))
  • Rows Removed by Filter: 66
Planning time : 28.810 ms
Execution time : 167,869.677 ms