explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Djdv

Settings
# exclusive inclusive rows x rows loops node
1. 155.166 58,612.364 ↑ 1.5 44,281 1

Unique (cost=3,696,543.43..3,700,434.74 rows=67,675 width=546) (actual time=58,354.135..58,612.364 rows=44,281 loops=1)

  • Buffers: shared hit=3868790 read=1485538 dirtied=112 written=143, temp read=8871 written=8871
2. 749.779 58,457.198 ↑ 1.2 56,883 1

Sort (cost=3,696,543.43..3,696,712.61 rows=67,675 width=546) (actual time=58,354.132..58,457.198 rows=56,883 loops=1)

  • Sort 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,
  • Sort Method: external merge Disk: 70952kB
  • Buffers: shared hit=3865791 read=1485331 dirtied=112 written=143, temp read=8871 written=8871
3. 25,440.253 57,707.419 ↑ 1.2 56,883 1

Nested Loop (cost=162.44..3,679,216.25 rows=67,675 width=546) (actual time=122.164..57,707.419 rows=56,883 loops=1)

  • Join Filter: (e.event_key = status.event_key)
  • Buffers: shared hit=3862410 read=1484925 dirtied=112 written=143
4. 46.356 31,204.398 ↓ 2.9 44,282 1

Nested Loop (cost=161.87..3,599,436.39 rows=15,140 width=547) (actual time=110.891..31,204.398 rows=44,282 loops=1)

  • Buffers: shared hit=3248774 read=1417031 dirtied=109 written=137
5. 128.716 30,139.556 ↓ 2.9 44,282 1

Nested Loop (cost=161.44..3,592,236.42 rows=15,140 width=514) (actual time=110.762..30,139.556 rows=44,282 loops=1)

  • Buffers: shared hit=3099144 read=1389167 dirtied=76 written=136
6. 481.050 29,169.482 ↓ 2.9 44,282 1

Nested Loop (cost=160.87..3,557,657.71 rows=15,164 width=330) (actual time=110.661..29,169.482 rows=44,282 loops=1)

  • Buffers: shared hit=2901288 read=1365206 dirtied=75 written=135
7. 1,006.954 15,509.500 ↓ 1.9 693,628 1

Hash Join (cost=160.31..1,481,887.88 rows=362,110 width=56) (actual time=5.495..15,509.500 rows=693,628 loops=1)

  • Hash Cond: ((e.examination)::text = (examcd.code)::text)
  • Buffers: shared hit=61689 read=873656 dirtied=75 written=73
8. 14,497.425 14,497.425 ↑ 1.0 1,496,439 1

Seq Scan on exams e (cost=0.00..1,472,475.56 rows=1,501,576 width=26) (actual time=0.340..14,497.425 rows=1,496,439 loops=1)

  • Filter: (((appt_site)::text = 'RM301'::text) AND ((deleted)::text = 'N'::text))
  • Rows Removed by Filter: 33660059
  • Buffers: shared hit=61595 read=873656 dirtied=75 written=73
9. 1.053 5.121 ↑ 1.0 1,397 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
  • Buffers: shared hit=94
10. 3.333 4.068 ↑ 1.0 1,397 1

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

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

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

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

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

  • 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=2839599 read=491550 written=62
13. 841.358 841.358 ↑ 1.0 1 44,282

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

  • Index Cond: (event_key = e.event_key)
  • Filter: ((deleted)::text = 'N'::text)
  • Buffers: shared hit=197856 read=23961 dirtied=1 written=1
14. 1,018.486 1,018.486 ↑ 1.0 1 44,282

Index Scan using patient_pkey on patient p (cost=0.43..0.47 rows=1 width=33) (actual time=0.022..0.023 rows=1 loops=44,282)

  • Index Cond: (computer_number = ev.computer_number)
  • Buffers: shared hit=149630 read=27864 dirtied=33 written=1
15. 1,062.768 1,062.768 ↑ 23.0 1 44,282

Index Scan using status_idx09 on status (cost=0.57..2.74 rows=23 width=7) (actual time=0.021..0.024 rows=1 loops=44,282)

  • 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=196878 read=33461 dirtied=3 written=2
Planning time : 21.832 ms
Execution time : 58,649.437 ms