explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ukrn6

Settings
# exclusive inclusive rows x rows loops node
1. 11.697 232.448 ↑ 4.9 38,306 1

Hash Right Join (cost=67,062.47..74,825.23 rows=188,188 width=16) (actual time=203.681..232.448 rows=38,306 loops=1)

  • Hash Cond: (f_1.id = f.id)
  • Buffers: shared hit=5738 read=29626
2. 12.642 85.574 ↑ 5.0 37,620 1

GroupAggregate (cost=44,052.24..47,345.53 rows=188,188 width=40) (actual time=68.461..85.574 rows=37,620 loops=1)

  • Group Key: f_1.id, r.trial_manager_id
  • Buffers: shared hit=2832 read=14797
3. 17.644 72.932 ↑ 5.0 37,620 1

Sort (cost=44,052.24..44,522.71 rows=188,188 width=32) (actual time=68.454..72.932 rows=37,620 loops=1)

  • Sort Key: f_1.id, r.trial_manager_id
  • Sort Method: quicksort Memory: 3885kB
  • Buffers: shared hit=2832 read=14797
4. 14.057 55.288 ↑ 5.0 37,620 1

Hash Join (cost=2,046.62..23,060.77 rows=188,188 width=32) (actual time=7.363..55.288 rows=37,620 loops=1)

  • Hash Cond: (f_1.attendance_period_id = ap_1.id)
  • Join Filter: (((racl.form_type_id = f_1.form_type_id) OR (racl.form_type_id IS NULL)) AND ((racl.physician_id = f_1.physician_id) OR (racl.physician_id IS NULL)))
  • Buffers: shared hit=2832 read=14797
5. 33.907 33.907 ↑ 1.0 38,306 1

Seq Scan on forms f_1 (cost=0.00..17,761.06 rows=38,306 width=64) (actual time=0.011..33.907 rows=38,306 loops=1)

  • Buffers: shared hit=2581 read=14797
6. 1.002 7.324 ↑ 5.2 6,097 1

Hash (cost=1,648.30..1,648.30 rows=31,866 width=64) (actual time=7.324..7.324 rows=6,097 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 638kB
  • Buffers: shared hit=251
7. 1.818 6.322 ↑ 5.2 6,097 1

Hash Join (cost=287.85..1,648.30 rows=31,866 width=64) (actual time=2.056..6.322 rows=6,097 loops=1)

  • Hash Cond: (a.registry_id = r.id)
  • Join Filter: ((racl.institute_id = ap_1.institute_id) OR (racl.institute_id IS NULL))
  • Buffers: shared hit=251
8. 1.834 4.052 ↑ 1.0 6,275 1

Hash Join (cost=241.19..440.42 rows=6,275 width=48) (actual time=1.599..4.052 rows=6,275 loops=1)

  • Hash Cond: (ap_1.attendance_id = a.id)
  • Buffers: shared hit=220
9. 0.632 0.632 ↑ 1.0 6,275 1

Seq Scan on attendance_periods ap_1 (cost=0.00..182.75 rows=6,275 width=48) (actual time=0.004..0.632 rows=6,275 loops=1)

  • Buffers: shared hit=120
10. 0.873 1.586 ↑ 1.0 6,275 1

Hash (cost=162.75..162.75 rows=6,275 width=32) (actual time=1.586..1.586 rows=6,275 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 457kB
  • Buffers: shared hit=100
11. 0.713 0.713 ↑ 1.0 6,275 1

Seq Scan on attendances a (cost=0.00..162.75 rows=6,275 width=32) (actual time=0.004..0.713 rows=6,275 loops=1)

  • Buffers: shared hit=100
12. 0.004 0.452 ↑ 14.6 21 1

Hash (cost=42.83..42.83 rows=306 width=96) (actual time=0.452..0.452 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=31
13. 0.009 0.448 ↑ 14.6 21 1

Hash Join (cost=18.77..42.83 rows=306 width=96) (actual time=0.335..0.448 rows=21 loops=1)

  • Hash Cond: (racl.registry_id = r.id)
  • Buffers: shared hit=31
14. 0.064 0.426 ↑ 14.6 21 1

Hash Join (cost=17.25..40.37 rows=306 width=64) (actual time=0.319..0.426 rows=21 loops=1)

  • Hash Cond: (racl.trustee_id = get_trustee_list.trustee_id)
  • Buffers: shared hit=30
15. 0.052 0.052 ↑ 1.0 611 1

Seq Scan on registry_acl racl (cost=0.00..18.11 rows=611 width=80) (actual time=0.004..0.052 rows=611 loops=1)

  • Buffers: shared hit=12
16. 0.002 0.310 ↑ 100.0 2 1

Hash (cost=14.75..14.75 rows=200 width=16) (actual time=0.310..0.310 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=18
17. 0.004 0.308 ↑ 100.0 2 1

HashAggregate (cost=12.75..14.75 rows=200 width=16) (actual time=0.307..0.308 rows=2 loops=1)

  • Group Key: get_trustee_list.trustee_id
  • Buffers: shared hit=18
18. 0.304 0.304 ↑ 500.0 2 1

Function Scan on get_trustee_list (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.303..0.304 rows=2 loops=1)

  • Buffers: shared hit=18
19. 0.004 0.013 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=32) (actual time=0.013..0.013 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
20. 0.009 0.009 ↑ 1.0 23 1

Seq Scan on registries r (cost=0.00..1.23 rows=23 width=32) (actual time=0.006..0.009 rows=23 loops=1)

  • Buffers: shared hit=1
21. 5.438 135.177 ↑ 1.0 38,306 1

Hash (cost=22,531.41..22,531.41 rows=38,306 width=16) (actual time=135.177..135.177 rows=38,306 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2308kB
  • Buffers: shared hit=2906 read=14829
22. 10.761 129.739 ↑ 1.0 38,306 1

Merge Left Join (cost=22,227.03..22,531.41 rows=38,306 width=16) (actual time=121.751..129.739 rows=38,306 loops=1)

  • Merge Cond: ((att.registry_id = tbp.registry_id) AND (ap.institute_id = tbp.institute_id))
  • Buffers: shared hit=2906 read=14829
23. 14.201 118.978 ↑ 1.0 38,306 1

Sort (cost=22,166.95..22,262.72 rows=38,306 width=48) (actual time=115.260..118.978 rows=38,306 loops=1)

  • Sort Key: att.registry_id, ap.institute_id
  • Sort Method: quicksort Memory: 3938kB
  • Buffers: shared hit=2878 read=14829
24. 8.814 104.777 ↑ 1.0 38,306 1

Hash Join (cost=775.86..19,250.85 rows=38,306 width=48) (actual time=6.595..104.777 rows=38,306 loops=1)

  • Hash Cond: (f.physician_id = ph.id)
  • Buffers: shared hit=2878 read=14829
25. 15.113 95.963 ↑ 1.0 38,306 1

Hash Join (cost=735.39..19,108.99 rows=38,306 width=64) (actual time=6.448..95.963 rows=38,306 loops=1)

  • Hash Cond: ((att.patient_id = rp.patient_id) AND (att.registry_id = rp.registry_id))
  • Buffers: shared hit=2849 read=14829
26. 13.636 80.850 ↑ 1.0 38,306 1

Hash Join (cost=512.76..18,685.24 rows=38,306 width=80) (actual time=4.187..80.850 rows=38,306 loops=1)

  • Hash Cond: (ap.attendance_id = att.id)
  • Buffers: shared hit=2775 read=14829
27. 8.287 67.214 ↑ 1.0 38,306 1

Hash Join (cost=271.58..18,343.43 rows=38,306 width=64) (actual time=2.036..67.214 rows=38,306 loops=1)

  • Hash Cond: (ap.institute_id = i.id)
  • Buffers: shared hit=2675 read=14829
28. 11.290 58.894 ↑ 1.0 38,306 1

Hash Join (cost=265.97..18,233.59 rows=38,306 width=64) (actual time=1.998..58.894 rows=38,306 loops=1)

  • Hash Cond: (f.attendance_period_id = ap.id)
  • Buffers: shared hit=2672 read=14829
29. 10.457 45.648 ↑ 1.0 38,306 1

Hash Join (cost=4.78..17,871.79 rows=38,306 width=48) (actual time=0.033..45.648 rows=38,306 loops=1)

  • Hash Cond: (f.form_type_id = ft.id)
  • Buffers: shared hit=2552 read=14829
30. 35.168 35.168 ↑ 1.0 38,306 1

Seq Scan on forms f (cost=0.00..17,761.06 rows=38,306 width=64) (actual time=0.004..35.168 rows=38,306 loops=1)

  • Buffers: shared hit=2549 read=14829
31. 0.011 0.023 ↑ 1.0 79 1

Hash (cost=3.79..3.79 rows=79 width=16) (actual time=0.023..0.023 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=3
32. 0.012 0.012 ↑ 1.0 79 1

Seq Scan on form_types ft (cost=0.00..3.79 rows=79 width=16) (actual time=0.004..0.012 rows=79 loops=1)

  • Buffers: shared hit=3
33. 1.094 1.956 ↑ 1.0 6,275 1

Hash (cost=182.75..182.75 rows=6,275 width=48) (actual time=1.956..1.956 rows=6,275 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 555kB
  • Buffers: shared hit=120
34. 0.862 0.862 ↑ 1.0 6,275 1

Seq Scan on attendance_periods ap (cost=0.00..182.75 rows=6,275 width=48) (actual time=0.004..0.862 rows=6,275 loops=1)

  • Buffers: shared hit=120
35. 0.033 0.033 ↑ 1.0 116 1

Hash (cost=4.16..4.16 rows=116 width=16) (actual time=0.032..0.033 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=3