explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XnsD

Settings
# exclusive inclusive rows x rows loops node
1. 1,206.137 75,071.938 ↓ 9.4 44,282 1

HashAggregate (cost=2,854,279.09..2,856,686.30 rows=4,697 width=546) (actual time=75,016.270..75,071.938 rows=44,282 loops=1)

  • Group Key: e.exam_key, e.event_key, e.examination, examcd.examcd_name, e.accession, statsmethods.getpatienttypedesc(ev.patient_type), ev.speciality, examcd.modality, ev.abnormal, ev.even
  • Buffers: shared hit=10681071 read=923312 dirtied=9398 written=379
2. 31,506.440 73,865.801 ↓ 12.1 56,884 1

Nested Loop (cost=2.97..2,854,020.75 rows=4,697 width=546) (actual time=14.929..73,865.801 rows=56,884 loops=1)

  • Join Filter: (e.event_key = status.event_key)
  • Buffers: shared hit=10672176 read=921953 dirtied=9398 written=379
3. 75.083 41,075.154 ↓ 42.1 44,283 1

Nested Loop (cost=2.41..2,848,482.99 rows=1,051 width=547) (actual time=13.398..41,075.154 rows=44,283 loops=1)

  • Buffers: shared hit=10057787 read=855103 dirtied=9398 written=348
4. 105.843 39,848.713 ↓ 42.1 44,283 1

Nested Loop (cost=1.97..2,847,983.18 rows=1,051 width=514) (actual time=13.354..39,848.713 rows=44,283 loops=1)

  • Buffers: shared hit=9917320 read=818069 dirtied=9395 written=330
5. 57.774 38,635.795 ↓ 42.1 44,283 1

Nested Loop (cost=1.41..2,845,584.29 rows=1,052 width=330) (actual time=13.293..38,635.795 rows=44,283 loops=1)

  • Buffers: shared hit=9725811 read=787752 dirtied=9395 written=318
6. 599.973 37,885.341 ↓ 15.9 69,268 1

Nested Loop (cost=1.13..2,844,208.94 rows=4,364 width=300) (actual time=13.260..37,885.341 rows=69,268 loops=1)

  • Buffers: shared hit=9517659 read=787748 dirtied=9395 written=318
7. 16,868.569 16,868.569 ↓ 15.1 1,570,523 1

Index Scan using reports_idx04 on reports r (cost=0.56..2,430,767.74 rows=104,082 width=278) (actual time=12.026..16,868.569 rows=1,570,523 loops=1)

  • Index Cond: (date_typed >= '2019-02-01 00:00:00'::timestamp without time zone)
  • Filter: ((date_reported >= '2019-02-01 00:00:00'::timestamp without time zone) AND ((report_type)::text = 'N'::text))
  • Rows Removed by Filter: 1721556
  • Buffers: shared hit=1860123 read=580380 dirtied=9394 written=245
8. 20,416.799 20,416.799 ↓ 0.0 0 1,570,523

Index Scan using exams_pkey on exams e (cost=0.56..3.96 rows=1 width=26) (actual time=0.013..0.013 rows=0 loops=1,570,523)

  • Index Cond: (exam_key = r.exam_key)
  • Filter: (((appt_site)::text = 'RM301'::text) AND ((deleted)::text = 'N'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=7657536 read=207368 dirtied=1 written=73
9. 692.680 692.680 ↑ 1.0 1 69,268

Index Scan using examcd_pkey on examcd (cost=0.28..0.31 rows=1 width=36) (actual time=0.009..0.010 rows=1 loops=69,268)

  • Index Cond: ((code)::text = (e.examination)::text)
  • Filter: ((modality)::text = ANY ('{C,M}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=208152 read=4
10. 1,107.075 1,107.075 ↑ 1.0 1 44,283

Index Scan using events_pkey on events ev (cost=0.56..2.27 rows=1 width=184) (actual time=0.024..0.025 rows=1 loops=44,283)

  • Index Cond: (event_key = e.event_key)
  • Filter: ((deleted)::text = 'N'::text)
  • Buffers: shared hit=191509 read=30317 written=12
11. 1,151.358 1,151.358 ↑ 1.0 1 44,283

Index Scan using patient_pkey on patient p (cost=0.43..0.47 rows=1 width=33) (actual time=0.025..0.026 rows=1 loops=44,283)

  • Index Cond: (computer_number = ev.computer_number)
  • Buffers: shared hit=140467 read=37034 dirtied=3 written=18
12. 1,284.207 1,284.207 ↑ 23.0 1 44,283

Index Scan using status_idx09 on status (cost=0.57..2.74 rows=23 width=7) (actual time=0.025..0.029 rows=1 loops=44,283)

  • Index Cond: ((event_key = ev.event_key) AND ((status_current)::text = 'Y'::text))
  • Filter: ((status_category)::text = 'AT'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=194749 read=35593 written=11
Planning time : 6.168 ms
Execution time : 75,076.874 ms