explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 73RE

Settings
# exclusive inclusive rows x rows loops node
1. 25,210.777 25,893.325 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,148.94..8,187.77 rows=5 width=611) (actual time=853.733..25,893.325 rows=124 loops=1)

  • Buffers: shared hit=25,698 read=15,470 dirtied=48 written=2
  • loops=1)
2. 0.000 677.836 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,148.51..8,168.60 rows=5 width=598) (actual time=650.753..677.836 rows=124 loops=1)

  • Buffers: shared hit=7,224 read=4,722 dirtied=1
3. 3.121 670.592 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,148.22..8,167.05 rows=5 width=582) (actual time=650.460..670.592 rows=124 loops=1)

  • Join Filter: ((rooms.hospital)::text = (events.site)::text)
  • Rows Removed by Join Filter: 4
  • Buffers: shared hit=6,887 read=4,687 dirtied=1
4. 3.621 667.471 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,148.08..8,166.19 rows=5 width=565) (actual time=650.410..667.471 rows=124 loops=1)

  • Buffers: shared hit=6,661 read=4,687 dirtied=1
5. 1.865 663.850 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,147.81..8,152.68 rows=5 width=575) (actual time=649.780..663.850 rows=124 loops=1)

  • Buffers: shared hit=6,292 read=4,684 dirtied=1
6. 3.652 661.985 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,147.53..8,151.17 rows=5 width=560) (actual time=649.770..661.985 rows=124 loops=1)

  • Buffers: shared hit=6,193 read=4,683 dirtied=1
7. 3.488 658.333 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,147.25..8,149.62 rows=5 width=544) (actual time=649.123..658.333 rows=124 loops=1)

  • Buffers: shared hit=5,822 read=4,682 dirtied=1
8. 3.421 654.845 ↓ 24.8 124 1

Nested Loop Left Join (cost=8,146.97..8,147.81 rows=5 width=528) (actual time=649.095..654.845 rows=124 loops=1)

  • Buffers: shared hit=5,450 read=4,682 dirtied=1
9. 2.179 651.424 ↓ 24.8 124 1

Merge Left Join (cost=8,146.83..8,146.95 rows=5 width=508) (actual time=649.074..651.424 rows=124 loops=1)

  • Merge Cond: ((events.site)::text = (sites.code)::text)
  • Buffers: shared hit=5,206 read=4,682 dirtied=1
10. 1.846 649.245 ↓ 24.8 124 1

Sort (cost=8,145.56..8,145.57 rows=5 width=368) (actual time=648.990..649.245 rows=124 loops=1)

  • Sort Key: events.site
  • Sort Method: quicksort Memory: 66kB
  • Buffers: shared hit=5,205 read=4,682 dirtied=1
  • -> Hash Right Join (cost=8,134.40..8145.50 rows=5 width=368) (actual time=647.133..648.276rows=124 loops=1)
  • Hash Cond: ((verifier.code)::text = (min((reports.last_verify_by)::text)))
  • Buffers: shared hit=5,205 read=4,682 dirtied=1
11. 0.477 0.477 ↑ 1.0 323 1

Seq Scan on radiol verifier (cost=0.00..8.23 rows=323 width=24) (actual time=0.006..0.477 rows=323 loops=1)

  • Buffers: shared hit=5
12. 0.340 646.922 ↓ 24.8 124 1

Hash (cost=8,134.33..8,134.33 rows=5 width=352) (actual time=646.922..646.922 rows=124 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=5,200 read=4,682 dirtied=1
13. 0.928 646.582 ↓ 24.8 124 1

Hash Right Join (cost=8,123.23..8,134.33 rows=5 width=352) (actual time=646.125..646.582 rows=124 loops=1)

  • Hash Cond: ((reporter.code)::text = (min((reports.reported_by)::text)))
  • Buffers: shared hit=5,200 read=4,682 dirtied=1
14. 0.670 0.670 ↑ 1.0 323 1

Seq Scan on radiol reporter (cost=0.00..8.23 rows=323 width=24) (actual time=0.017..0.670 rows=323 loops=1)

  • Buffers: shared hit=3 read=2
15. 0.607 644.984 ↓ 24.8 124 1

Hash (cost=8,123.17..8,123.17 rows=5 width=336) (actual time=644.984..644.984 rows=124 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=5,197 read=4,680 dirtied=1
16. 24.325 644.377 ↓ 24.8 124 1

Nested Loop (cost=8,114.33..8,123.17 rows=5 width=336) (actual time=597.393..644.377 rows=124 loops=1)

  • Join Filter: ((min(exams_1.exam_key)) = status.exam_key)
  • Buffers: shared hit=5,197 read=4,680 dirtied=1
17. 1.106 620.052 ↓ 124.0 124 1

Nested Loop Left Join (cost=8,113.89..8,122.09 rows=1 width=328) (actual time=597.184..620.052 rows=124 loops=1)

  • Buffers: shared hit=4,541 read=4,572 dirtied=1
18. 7.586 618.946 ↓ 124.0 124 1

Nested Loop Left Join (cost=8,113.62..8,121.79 rows=1 width=312) (actual time=597.174..618.946 rows=124 loops=1)

  • Buffers: shared hit=4,494 read=4,571 dirtied=1
19. 4.004 611.360 ↓ 0.0 124 1

Nested Loop Left Join (cost=8113.20..8121.34rows=1 width=302) (cost=0..0 rows=0 width=0) (actual time=596.705..611.360 rows=124 loops=1)

  • Buffers: shared hit=4,033 read=4,535 dirtied=1
20. 2.425 607.356 ↓ 124.0 124 1

Nested Loop Left Join (cost=8,112.92..8,121.03 rows=1 width=274) (actual time=596.686..607.356 rows=124 loops=1)

  • Buffers: shared hit=3,667 read=4,529 dirtied=1
21. 4.601 604.931 ↓ 124.0 124 1

Nested Loop (cost=8,112.65..8,120.73 rows=1 width=253) (actual time=596.656..604.931 rows=124 loops=1)

  • Buffers: shared hit=3,295 read=4,529 dirtied=1
22. 3.327 600.330 ↓ 124.0 124 1

Nested Loop (cost=8,112.22..8,116.27 rows=1 width=217) (actual time=596.627..600.330 rows=124 loops=1)

  • Buffers: shared hit=2,802 read=4,526 dirtied=1
23. 45.299 597.003 ↓ 124.0 124 1

HashAggregate (cost=8,111.79..8,111.80 rows=1 width=87) (actual time=596.606..597.003 rows=124 loops=1)

  • Group Key: status_2.event_key
  • Buffers: shared hit=2,305 read=4,526 dirtied=1
24. 139.162 551.704 ↓ 14.2 696 1

Nested Loop Left Join (cost=7,978.79..8,036.70 rows=49 width=87) (actual time=385.867..551.704 rows=696 loops=1)

  • Filter: ((COALESCE(status_1.deleted, 'N'::character varying))::text <> 'Y'::text)
  • Buffers: shared hit=2,305 read=4,526 dirtied=1
  • Filter:((COALESCE(reports.deleted, 'N'::character varying))::text <> 'Y'::text)
25. 13.960 412.542 ↓ 70.5 141 1

Nested Loop Left Join (cost=7,978.36..7,983.95 rows=2 width=84) (actual time=385.655..412.542 rows=141 loops=1)

  • Buffers: shared hit=1,635 read=4,316 dirtied=1
26. 398.582 398.582 ↓ 65.5 131 1

Nested Loop (cost=7,977.92..7,982.12 rows=2 width=26) (actual time=385.635..398.582 rows=131 loops=1)

  • Join Filter: (status_2.event_key = exams_1.event_key)
  • Buffers: shared hit=1,260 read=4,258 dirtied=1
  • -> Nested Loop (cost=7,977.49..7981.55 rows=1 width=8) (actual time=385.608..392.612 rows=124 loops=1) Buffers: shared hit=777 read=4,241 dirtied=1
27. 0.740 385.894 ↓ 124.0 124 1

HashAggregate (cost=7,977.06..7,977.07 rows=1 width=4) (actual time=385.573..385.894 rows=124 loops=1)

  • Group Key: status_2.event_key
  • Buffers: shared hit=300 read=4,221 dirtied=1
28. 25.219 385.154 ↓ 64.5 129 1

Bitmap Heap Scan on status status_2 (cost=7,141.93..7,977.06 rows=2 width=4) (actual time=360.107..385.154 rows=129 loops=1)

  • Recheck Cond: (((status_category)::text = ANY ('{C,W,AS,AT,V,R,ST,D,AP,P,E}'::text[])) AND ((status_current)::text = 'Y'::text) AND (start_date >= LEAST('2019-12-30 00:00:00'::timestamp without time zone, '2020-01-13 00:00:00'::timestamp without time zone)) AND (start_date <= GREATEST('2019-12-30 00:00:00'::timestamp without time zone, '2020-01-13 00:00:00'::timestamp without time zone)) AND (status_key >= 12,382,089) AND (status_key <= 13,192,558))
  • Filter: (((COALESCE(deleted, 'N'::character varying))::text <> 'Y'::text) AND (cris_analytics.get_timestamp(start_date, (start_time)::text) >= LEAST('2019-12-30 14:34:36.586508'::timestamp without time zone, '2020-01-13 14:34:36.586508'::timestamp without time zone)) AND (cris_analytics.get_timestamp(start_date, (start_time)::text) <= GREATEST('2019-12-30 14:34:36.586508'::timestamp without time zone, '2020-01-13 14:34:36.586508'::timestamp without time zone)))
  • Rows Removed by Filter: 1,059
  • Heap Blocks: exact=366
  • Buffers: shared hit=300 read=4,221 dirtied=1
29. 1.286 359.935 ↓ 0.0 0 1

BitmapAnd (cost=7,141.93..7,141.93 rows=333 width=0) (actual time=359.935..359.935 rows=0 loops=1)

  • Buffers: shared hit=83 read=4,072
30. 16.878 16.878 ↓ 1.8 20,711 1

Bitmap Index Scan on status_idx05 (cost=0.00..284.47 rows=11,217 width=0) (actual time=16.878..16.878 rows=20,711 loops=1)

  • Index Cond: (((status_category)::text = ANY ('{C,W,AS,AT,V,R,ST,D,AP,P,E}'::text[])) AND ((status_current)::text = 'Y'::text) AND (start_date >= LEAST('2019-12-30 00:00:00'::timestamp without time zone, '2020-01-13 00:00:00'::timestamp without time zone)) AND (start_date <= GREATEST('2019-12-30 00:00:00'::timestamp without time zone, '2020-01-13 00:00:00'::timestamp without time zone)))
  • Buffers: shared hit=81 read=88
31. 341.771 341.771 ↓ 2.1 822,897 1

Bitmap Index Scan on status_pkey (cost=0.00..6,857.21 rows=391,477 width=0) (actual time=341.771..341.771 rows=822,897 loops=1)

  • Index Cond: ((status_key >= 12,382,089) AND (status_key <= 13,192,558))
  • Buffers: shared hit=2 read=3,984
32. 5.828 5.828 ↑ 1.0 1 124

Index Scan using events_pkey on events events_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.044..0.047 rows=1 loops=124)

  • Index Cond: (event_key = status_2.event_key)
  • Filter: ((COALESCE(deleted, 'N'::character varying))::text <> 'Y'::text)
  • Buffers: shared hit=477 read=20 ->
33. 0.000 4.712 ↑ 2.0 1 124

Index Scan using exams_idx02 on exams exams_1 (cost=0.43..0.55 rows=2 width=26) (actual time=0.035..0.038 rows=1 loops=124)

  • Index Cond: (event_key = events_1.event_key)
  • Filter: ((COALESCE(deleted, 'N'::character varying))::text <> 'Y'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=483 read=17
34. 12.969 12.969 ↓ 0.0 0 131

Index Scan using reports_udx01 on reports (cost=0.43..0.79 rows=10 width=64) (actual time=0.096..0.099 rows=0 loops=131)

  • Index Cond: (exams_1.exam_key = exam_key)
  • Buffers: shared hit=375 read=58
35. 135.501 135.501 ↑ 5.0 5 141

Index Scan using status_idx10 on status status_1 (cost=0.43..26.06 rows=25 width=9) (actual time=0.817..0.961 rows=5 loops=141)

  • Index Cond: (status_2.event_key = event_key)
  • Buffers: shared hit=670 read=210
36. 1.860 1.860 ↑ 1.0 1 124

Index Scan using events_pkey on events (cost=0.43..4.45 rows=1 width=85) (actual time=0.011..0.015 rows=1 loops=124)

  • Index Cond: (event_key = status_2.event_key)
  • Buffers: shared hit497
37. 3.472 3.472 ↑ 1.0 1 124

Index Scan using exams_pkeyon exams (cost=0.43..4.45 rows=1 width=36) (actual time=0.024..0.028 rows=1 loops=124)

  • Index Cond: (exam_key = (min(exams_1.exam_key)))
  • Buffers: shared hit=493 read=3
38. 1.240 1.240 ↑ 1.0 1 124

Index Scan using wards_udx01 on wards (cost=0.27..0.29 rows=1 width=29) (actual time=0.008..0.010 rows=1 loops=124)

  • Index Cond: ((events.ward)::text = (code)::text)
  • Buffers: shared hit=372
39. 2.356 2.356 ↑ 1.0 1 124

Index Scan using examcd_pkey on examcd procedure (cost=0.28..0.30 rows=1 width=34) (actual time=0.016..0.019 rows=1 loops=124)

  • Index Cond: ((code)::text = (exams.examination)::text)
  • Buffers: shared hit=366 read=6
40. 6.696 6.696 ↑ 1.0 1 124

Index Scan using referer_udx01 on referer (cost=0.42..0.44 rows=1 width=19) (actual time=0.052..0.054 rows=1 loops=124)

  • Index Cond: ((events.referrer)::text = (code)::text)
  • Buffers: shared hit=461 read=36
41. 0.372 0.372 ↓ 0.0 0 124

Index Scan using radiol_udx01 on radiol intended_radiologist_lookup (cost=0.27..0.29 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=124)

  • Index Cond: ((code)::text = (events.intended_radiol)::text)
  • Buffers: shared hit=47 read=1
42. 23.312 23.312 ↓ 0.0 1 124

Index Scan using status_idx07 on status (cost=0.43..1.02rows=5 width=12) (cost=0..0 rows=0 width=0) (actual time=0.174..0.188 rows=1 loops=124)

  • Index Cond: (exam_key = exams.exam_key)
  • Filter: ((status_current)::text = 'Y'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=656 read=108
43. 0.344 0.344 ↓ 12.6 126 1

Sort (cost=1.27..1.29 rows=10 width=182) (actual time=0.068..0.344 rows=126 loops=1)

  • Sort Key: sites.code
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
  • -> Seq Scan on sites (cost=0.00..1.10 rows=10 width=182) (actual time=0.007..0.030 rows=10
  • Buffers: shared hit=1
44. 1.860 1.860 ↑ 1.0 1 124

Index Scan using special_udx01 on special speciality_lookup (cost=0.14..0.16 rows=1 width=24) (actual time=0.011..0.015 rows=1 loops=124)

  • Index Cond: ((code)::text = (events.speciality)::text)
  • Buffers: shared hit=244
45. 2.232 2.232 ↑ 1.0 1 124

Index Scan using hdstbc_idx01 on hdstbc modality_lookup (cost=0.28..0.35 rows=1 width=21) (actual time=0.014..0.018 rows=1 loops=124)

  • Index Cond: (((table_number)::text = 'CRISMODL'::text) AND ((return_value)::text = (procedure.modality)::text))
  • Buffers: shared hit=372
46. 2.480 2.480 ↑ 1.0 1 124

Index Scan using hdstbc_idx01 on hdstbc patient_type_lookup (cost=0.28..0.30 rows=1 width=21) (actual time=0.015..0.020 rows=1 loops=124)

  • Index Cond: (((table_number)::text = 'CRISPATT'::text) AND ((return_value)::text = (events.patient_type)::text))
  • Buffers: shared hit=371 read=1
47. 0.744 0.744 ↓ 0.0 0 124

Index Scan using radiog_udx01 on radiog radiographer_lookup (cost=0.28..0.29 rows=1 width=23) (actual time=0.005..0.006 rows=0 loops=124)

  • Index Cond: ((exams.radiographer_1)::text = (code)::text)
  • Buffers: shared hit=99 read=1
48. 2.356 2.356 ↑ 1.0 1 124

Index Scan using trusts_pkey on trusts (cost=0.28..2.69 rows=1 width=32) (actual time=0.015..0.019 rows=1 loops=124)

  • Index Cond: ((sites.trust)::text = (code)::text)
  • Buffers: shared hit=369 read=3
49. 1.736 1.736 ↑ 1.0 1 124

Index Scan using rooms_idx01 on rooms (cost=0.14..0.16 rows=1 width=27) (actual time=0.010..0.014 rows=1 loops=124)

  • Index Cond: ((exams.room)::text = (code)::text)
  • Buffers: shared hit=226
50. 5.952 5.952 ↑ 1.0 1 124

Index Scan using hdsuse_pkey on hdsuse event_booked_by (cost=0.28..0.30 rows=1 width=24) (actual time=0.044..0.048 rows=1 loops=124)

  • Index Cond: ((user_id)::text = (events.booked_by)::text)
  • Buffers: shared hit=337 read=35
51. 312.232 312.232 ↑ 1.0 1 124

Index Scan using exam_summary_udx1 on exam_summary (cost=0.43..0.48 rows=1 width=17) (actual time=2.436..2.518 rows=1 loops=124)

  • Index Cond: (exam_key = exams.exam_key)
  • Buffers: shared hit=357 read=142 dirtied=2
Planning time : 11.956 ms
Execution time : 25,894.870 ms