explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2A1e

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 30,065.992 ↑ 1.0 1 1

Aggregate (cost=156,337.32..156,337.33 rows=1 width=8) (actual time=30,065.992..30,065.992 rows=1 loops=1)

  • Buffers: shared hit=2,771,810
2. 6.411 30,065.963 ↑ 385.2 263 1

Result (cost=128,476.52..155,070.92 rows=101,312 width=2,171) (actual time=30,059.645..30,065.963 rows=263 loops=1)

  • Buffers: shared hit=2,771,810
3. 2.127 30,059.552 ↑ 385.2 263 1

Sort (cost=128,476.52..128,729.80 rows=101,312 width=2,147) (actual time=30,059.524..30,059.552 rows=263 loops=1)

  • Sort Key: pt.name
  • Sort Method: quicksort Memory: 45kB
  • Buffers: shared hit=2,768,767
4. 7.824 30,057.425 ↑ 385.2 263 1

Nested Loop (cost=48,700.52..52,775.71 rows=101,312 width=2,147) (actual time=96.212..30,057.425 rows=263 loops=1)

  • Buffers: shared hit=2,768,767
5. 19.240 68.331 ↓ 105.0 21,010 1

HashAggregate (cost=48,700.09..48,702.09 rows=200 width=8) (actual time=55.745..68.331 rows=21,010 loops=1)

  • Group Key: v.cln_patient_id
  • Buffers: shared hit=16,476
6. 2.652 49.091 ↑ 1.0 32,585 1

Append (cost=0.43..48,617.17 rows=33,169 width=8) (actual time=0.017..49.091 rows=32,585 loops=1)

  • Buffers: shared hit=16,476
7. 21.003 21.003 ↑ 1.0 32,584 1

Index Scan using ndx_vst_seedocid on cln_visit v (cost=0.43..39,568.90 rows=33,110 width=8) (actual time=0.017..21.003 rows=32,584 loops=1)

  • Index Cond: (see_doctor_id = '30909376290001'::bigint)
  • Filter: ((visit_status)::text > 'VST0'::text)
  • Buffers: shared hit=9,821
8. 25.436 25.436 ↑ 59.0 1 1

Seq Scan on cln_patientmedicalrecord pmd (cost=0.00..8,550.73 rows=59 width=8) (actual time=19.659..25.436 rows=1 loops=1)

  • Filter: ((defunct_ind = 'N'::bpchar) AND (last_updated_by = '30909376160001'::bigint))
  • Rows Removed by Filter: 126,037
  • Buffers: shared hit=6,655
9. 126.849 29,981.270 ↓ 0.0 0 21,010

Index Scan using pk_cln_patient on cln_patient pt (cost=0.43..20.36 rows=1 width=17) (actual time=1.427..1.427 rows=0 loops=21,010)

  • Index Cond: (cln_patient_id = v.cln_patient_id)
  • Filter: ((ptf_topclinic_id = '1273249300'::bigint) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,752,291
10.          

SubPlan (for Index Scan)

11. 21.010 29,792.180 ↓ 0.0 0 21,010

Bitmap Heap Scan on cln_visit v_1 (cost=5.86..7.88 rows=1 width=0) (actual time=1.418..1.418 rows=0 loops=21,010)

  • Recheck Cond: ((cln_patient_id = pt.cln_patient_id) AND (visit_datetime >= '2020-09-01 00:00:00'::timestamp without time zone) AND (visit_datetime < '2020-09-04 00:00:00'::timestamp without time zone))
  • Filter: (((visit_status)::text > 'VST0'::text) AND ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=273
  • Buffers: shared hit=2,605,841
12. 21.010 29,771.170 ↓ 0.0 0 21,010

BitmapAnd (cost=5.86..5.86 rows=1 width=0) (actual time=1.417..1.417 rows=0 loops=21,010)

  • Buffers: shared hit=2,605,568
13. 84.040 84.040 ↑ 3.5 4 21,010

Bitmap Index Scan on ndx_vst_ptid (cost=0.00..2.54 rows=14 width=0) (actual time=0.004..0.004 rows=4 loops=21,010)

  • Index Cond: (cln_patient_id = pt.cln_patient_id)
  • Buffers: shared hit=63,358
14. 29,666.120 29,666.120 ↓ 368.5 23,585 21,010

Bitmap Index Scan on ndx_vst_vstdt (cost=0.00..3.07 rows=64 width=0) (actual time=1.412..1.412 rows=23,585 loops=21,010)

  • Index Cond: ((visit_datetime >= '2020-09-01 00:00:00'::timestamp without time zone) AND (visit_datetime < '2020-09-04 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=2,542,210
15. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_vst_vstdt on cln_visit v_2 (cost=0.43..34.13 rows=56 width=8) (never executed)

  • Index Cond: ((visit_datetime >= '2020-09-01 00:00:00'::timestamp without time zone) AND (visit_datetime < '2020-09-04 00:00:00'::timestamp without time zone))
  • Filter: (((visit_status)::text > 'VST0'::text) AND ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])))
16. 0.000 62.241 ↓ 0.0 0 20,747

Nested Loop (cost=0.69..8.06 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=20,747)

  • Buffers: shared hit=62,296
17. 62.241 62.241 ↓ 0.0 0 20,747

Index Scan using ndx_ptmrd_ptid on cln_patientmedicalrecord pmd_1 (cost=0.42..5.77 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=20,747)

  • Index Cond: (cln_patient_id = pt.cln_patient_id)
  • Filter: ((last_updated_datetime >= '2020-09-01 00:00:00'::timestamp without time zone) AND (last_updated_datetime < '2020-09-04 00:00:00'::timestamp without time zone) AND (defunct_ind = 'N'::bpchar))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=62,296
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_ptf_clinic on ptf_clinic c (cost=0.28..2.29 rows=1 width=8) (never executed)

  • Index Cond: (ptf_clinic_id = pmd_1.ptf_clinic_id)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=33.23..8,901.07 rows=436 width=8) (never executed)

  • Hash Cond: (pmd_2.ptf_clinic_id = c_1.ptf_clinic_id)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on cln_patientmedicalrecord pmd_2 (cost=0.00..8,866.69 rows=436 width=16) (never executed)

  • Filter: ((last_updated_datetime >= '2020-09-01 00:00:00'::timestamp without time zone) AND (last_updated_datetime < '2020-09-04 00:00:00'::timestamp without time zone) AND (defunct_ind = 'N'::bpchar))
21. 0.000 0.000 ↓ 0.0 0

Hash (cost=23.70..23.70 rows=762 width=8) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_ptf_clinic on ptf_clinic c_1 (cost=0.28..23.70 rows=762 width=8) (never executed)

  • Heap Fetches: 0 APP1-9008