explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nptd

Settings
# exclusive inclusive rows x rows loops node
1. 637.917 55,665.393 ↓ 6.2 44,290 1

HashAggregate (cost=3,644,538.86..3,648,229.37 rows=7,201 width=546) (actual time=55,602.132..55,665.393 rows=44,290 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=3916630 read=1491548 dirtied=33 written=37
2. 25,093.565 55,027.476 ↓ 7.9 56,900 1

Nested Loop (cost=162.44..3,644,142.81 rows=7,201 width=546) (actual time=4.981..55,027.476 rows=56,900 loops=1)

  • Buffers: shared hit=3907764 read=1490160 dirtied=33 written=37
3. 54.039 29,023.511 ↓ 7.9 56,900 1

Nested Loop (cost=162.00..3,637,099.80 rows=7,201 width=513) (actual time=3.335..29,023.511 rows=56,900 loops=1)

  • Join Filter: (e.event_key = status.event_key)
  • Buffers: shared hit=3273748 read=1445047 dirtied=29 written=37
4. 105.087 28,083.652 ↓ 2.9 44,291 1

Nested Loop (cost=161.44..3,592,310.68 rows=15,140 width=514) (actual time=3.258..28,083.652 rows=44,291 loops=1)

  • Buffers: shared hit=3078755 read=1409656 dirtied=29 written=37
5. 424.710 27,225.618 ↓ 2.9 44,291 1

Nested Loop (cost=160.87..3,557,732.21 rows=15,164 width=330) (actual time=3.209..27,225.618 rows=44,291 loops=1)

  • Buffers: shared hit=2884333 read=1382208 dirtied=29 written=37
6. 928.033 15,009.198 ↓ 1.9 693,630 1

Hash Join (cost=160.31..1,481,932.24 rows=362,121 width=56) (actual time=3.131..15,009.198 rows=693,630 loops=1)

  • Hash Cond: ((e.examination)::text = (examcd.code)::text)
  • Buffers: shared hit=41860 read=893513 dirtied=28 written=27
7. 14,078.141 14,078.141 ↑ 1.0 1,496,441 1

Seq Scan on exams e (cost=0.00..1,472,519.65 rows=1,501,621 width=26) (actual time=0.061..14,078.141 rows=1,496,441 loops=1)

  • Filter: (((appt_site)::text = 'RM301'::text) AND ((deleted)::text = 'N'::text))
  • Rows Removed by Filter: 33660272
  • Buffers: shared hit=41766 read=893513 dirtied=28 written=27
8. 0.992 3.024 ↑ 1.0 1,397 1

Hash (cost=142.85..142.85 rows=1,397 width=36) (actual time=3.024..3.024 rows=1,397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
  • Buffers: shared hit=94
9. 1.554 2.032 ↑ 1.0 1,397 1

Bitmap Heap Scan on examcd (cost=27.38..142.85 rows=1,397 width=36) (actual time=0.513..2.032 rows=1,397 loops=1)

  • Recheck Cond: ((modality)::text = ANY ('{C,M}'::text[]))
  • Heap Blocks: exact=83
  • Buffers: shared hit=94
10. 0.478 0.478 ↑ 1.0 1,397 1

Bitmap Index Scan on examcd_idx04 (cost=0.00..27.04 rows=1,397 width=0) (actual time=0.478..0.478 rows=1,397 loops=1)

  • Index Cond: ((modality)::text = ANY ('{C,M}'::text[]))
  • Buffers: shared hit=11
11. 11,791.710 11,791.710 ↓ 0.0 0 693,630

Index Scan using reports_udx01 on reports r (cost=0.56..5.70 rows=3 width=278) (actual time=0.017..0.017 rows=0 loops=693,630)

  • Index Cond: ((exam_key = e.exam_key) AND ((report_type)::text = 'N'::text))
  • Filter: (date_reported >= '2019-02-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2842473 read=488695 dirtied=1 written=10
12. 752.947 752.947 ↑ 1.0 1 44,291

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

  • Index Cond: (event_key = e.event_key)
  • Filter: ((deleted)::text = 'N'::text)
  • Buffers: shared hit=194422 read=27448
13. 885.820 885.820 ↑ 2.0 1 44,291

Index Scan using status_idx09 on status (cost=0.57..2.93 rows=2 width=7) (actual time=0.018..0.020 rows=1 loops=44,291)

  • Index Cond: ((event_key = ev.event_key) AND ((status_current)::text = 'Y'::text))
  • Filter: (((site)::text = 'RM301'::text) AND ((status_category)::text = 'AT'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=194993 read=35391
14. 910.400 910.400 ↑ 1.0 1 56,900

Index Scan using patient_pkey on patient p (cost=0.43..0.47 rows=1 width=33) (actual time=0.015..0.016 rows=1 loops=56,900)

  • Index Cond: (computer_number = ev.computer_number)
  • Buffers: shared hit=201808 read=26281 dirtied=4
Planning time : 5.886 ms
Execution time : 55,668.797 ms