explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hq5K

Settings
# exclusive inclusive rows x rows loops node
1. 733.349 1,325.119 ↑ 4.9 38,306 1

Hash Right Join (cost=87,267.71..203,842.28 rows=188,188 width=2,062) (actual time=488.215..1,325.119 rows=38,306 loops=1)

  • Hash Cond: (fp.id = f.id)
  • Functions: 180
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 18.685 ms, Inlining 0.000 ms, Optimization 5.699 ms, Emission 145.826 ms, Total 170.211 ms
2. 5.846 168.302 ↑ 5.0 37,620 1

Subquery Scan on fp (cost=47,268.74..100,431.85 rows=188,188 width=24) (actual time=64.153..168.302 rows=37,620 loops=1)

3. 89.618 162.456 ↑ 5.0 37,620 1

GroupAggregate (cost=47,268.74..98,549.97 rows=188,188 width=40) (actual time=64.151..162.456 rows=37,620 loops=1)

  • Group Key: f_1.id, r.trial_manager_id
4. 28.292 72.838 ↑ 5.0 37,620 1

Sort (cost=47,268.74..47,739.21 rows=188,188 width=68) (actual time=64.106..72.838 rows=37,620 loops=1)

  • Sort Key: f_1.id, r.trial_manager_id
  • Sort Method: external merge Disk: 2880kB
5. 23.286 44.546 ↑ 5.0 37,620 1

Hash Join (cost=2,046.62..23,060.77 rows=188,188 width=68) (actual time=6.560..44.546 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)))
6. 14.893 14.893 ↑ 1.0 38,306 1

Seq Scan on forms f_1 (cost=0.00..17,761.06 rows=38,306 width=80) (actual time=0.162..14.893 rows=38,306 loops=1)

7. 0.918 6.367 ↑ 5.2 6,097 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 661kB
8. 1.518 5.449 ↑ 5.2 6,097 1

Hash Join (cost=287.85..1,648.30 rows=31,866 width=68) (actual time=1.849..5.449 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))
9. 1.519 3.448 ↑ 1.0 6,275 1

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

  • Hash Cond: (ap_1.attendance_id = a.id)
10. 0.583 0.583 ↑ 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.583 rows=6,275 loops=1)

11. 0.730 1.346 ↑ 1.0 6,275 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 457kB
12. 0.616 0.616 ↑ 1.0 6,275 1

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

13. 0.006 0.483 ↑ 14.6 21 1

Hash (cost=42.83..42.83 rows=306 width=100) (actual time=0.483..0.483 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.009 0.477 ↑ 14.6 21 1

Hash Join (cost=18.77..42.83 rows=306 width=100) (actual time=0.348..0.477 rows=21 loops=1)

  • Hash Cond: (racl.registry_id = r.id)
15. 0.079 0.447 ↑ 14.6 21 1

Hash Join (cost=17.25..40.37 rows=306 width=68) (actual time=0.323..0.447 rows=21 loops=1)

  • Hash Cond: (racl.trustee_id = get_trustee_list.trustee_id)
16. 0.056 0.056 ↑ 1.0 611 1

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

17. 0.002 0.312 ↑ 100.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.008 0.310 ↑ 100.0 2 1

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

  • Group Key: get_trustee_list.trustee_id
19. 0.302 0.302 ↑ 500.0 2 1

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

20. 0.005 0.021 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.016 0.016 ↑ 1.0 23 1

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

22. 72.906 423.468 ↑ 1.0 38,306 1

Hash (cost=29,831.14..29,831.14 rows=38,306 width=2,041) (actual time=423.468..423.468 rows=38,306 loops=1)

  • Buckets: 2048 Batches: 32 Memory Usage: 1576kB
23. 11.924 350.562 ↑ 1.0 38,306 1

Hash Left Join (cost=1,370.54..29,831.14 rows=38,306 width=2,041) (actual time=183.154..350.562 rows=38,306 loops=1)

  • Hash Cond: (f.form_type_id = ftc.form_type_id)
24. 11.766 338.600 ↑ 1.0 38,306 1

Hash Left Join (cost=1,367.81..29,720.98 rows=38,306 width=2,033) (actual time=183.109..338.600 rows=38,306 loops=1)

  • Hash Cond: ((att.registry_id = tbp.registry_id) AND (ap.institute_id = tbp.institute_id))
25. 11.810 320.954 ↑ 1.0 38,306 1

Hash Left Join (cost=1,342.56..19,918.12 rows=38,306 width=2,029) (actual time=177.217..320.954 rows=38,306 loops=1)

  • Hash Cond: (f.id = bs.form_id)
26. 15.318 291.419 ↑ 1.0 38,306 1

Hash Join (cost=775.86..19,250.85 rows=38,306 width=1,997) (actual time=159.483..291.419 rows=38,306 loops=1)

  • Hash Cond: (f.physician_id = ph.id)
27. 17.089 122.980 ↑ 1.0 38,306 1

Hash Join (cost=735.39..19,108.99 rows=38,306 width=1,928) (actual time=6.355..122.980 rows=38,306 loops=1)

  • Hash Cond: ((att.patient_id = rp.patient_id) AND (att.registry_id = rp.registry_id))
28. 19.815 104.325 ↑ 1.0 38,306 1

Hash Join (cost=512.76..18,685.24 rows=38,306 width=1,919) (actual time=4.777..104.325 rows=38,306 loops=1)

  • Hash Cond: (ap.attendance_id = att.id)
29. 12.139 82.338 ↑ 1.0 38,306 1

Hash Join (cost=271.58..18,343.43 rows=38,306 width=1,765) (actual time=2.594..82.338 rows=38,306 loops=1)

  • Hash Cond: (ap.institute_id = i.id)
30. 19.072 70.155 ↑ 1.0 38,306 1

Hash Join (cost=265.97..18,233.59 rows=38,306 width=1,750) (actual time=2.544..70.155 rows=38,306 loops=1)

  • Hash Cond: (f.attendance_period_id = ap.id)
31. 28.783 48.819 ↑ 1.0 38,306 1

Hash Join (cost=4.78..17,871.79 rows=38,306 width=1,630) (actual time=0.267..48.819 rows=38,306 loops=1)

  • Hash Cond: (f.form_type_id = ft.id)
32. 20.003 20.003 ↑ 1.0 38,306 1

Seq Scan on forms f (cost=0.00..17,761.06 rows=38,306 width=1,598) (actual time=0.226..20.003 rows=38,306 loops=1)

33. 0.014 0.033 ↑ 1.0 79 1

Hash (cost=3.79..3.79 rows=79 width=32) (actual time=0.033..0.033 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
34. 0.019 0.019 ↑ 1.0 79 1

Seq Scan on form_types ft (cost=0.00..3.79 rows=79 width=32) (actual time=0.006..0.019 rows=79 loops=1)

35. 1.274 2.264 ↑ 1.0 6,275 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 1012kB
36. 0.990 0.990 ↑ 1.0 6,275 1

Seq Scan on attendance_periods ap (cost=0.00..182.75 rows=6,275 width=136) (actual time=0.005..0.990 rows=6,275 loops=1)

37. 0.016 0.044 ↑ 1.0 116 1

Hash (cost=4.16..4.16 rows=116 width=31) (actual time=0.044..0.044 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
38. 0.028 0.028 ↑ 1.0 116 1

Seq Scan on institutes i (cost=0.00..4.16 rows=116 width=31) (actual time=0.007..0.028 rows=116 loops=1)

39. 1.225 2.172 ↑ 1.0 6,275 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 870kB
40. 0.947 0.947 ↑ 1.0 6,275 1

Seq Scan on attendances att (cost=0.00..162.75 rows=6,275 width=170) (actual time=0.007..0.947 rows=6,275 loops=1)

41. 0.951 1.566 ↑ 1.0 5,945 1

Hash (cost=133.45..133.45 rows=5,945 width=41) (actual time=1.565..1.566 rows=5,945 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 494kB
42. 0.615 0.615 ↑ 1.0 5,945 1

Seq Scan on registries_patients rp (cost=0.00..133.45 rows=5,945 width=41) (actual time=0.009..0.615 rows=5,945 loops=1)

43. 0.103 153.121 ↑ 1.0 510 1

Hash (cost=34.10..34.10 rows=510 width=85) (actual time=153.121..153.121 rows=510 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
44. 153.018 153.018 ↑ 1.0 510 1

Seq Scan on users ph (cost=0.00..34.10 rows=510 width=85) (actual time=152.927..153.018 rows=510 loops=1)

45. 0.258 17.725 ↑ 1.0 1,209 1

Hash (cost=551.58..551.58 rows=1,209 width=48) (actual time=17.725..17.725 rows=1,209 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 321kB
46. 0.124 17.467 ↑ 1.0 1,209 1

Subquery Scan on bs (cost=502.98..551.58 rows=1,209 width=48) (actual time=8.844..17.467 rows=1,209 loops=1)

47. 8.364 17.343 ↑ 1.0 1,209 1

GroupAggregate (cost=502.98..539.49 rows=1,209 width=48) (actual time=8.843..17.343 rows=1,209 loops=1)

  • Group Key: fbs.form_id
48. 0.728 8.979 ↑ 1.0 2,140 1

Sort (cost=502.98..508.33 rows=2,140 width=47) (actual time=8.814..8.979 rows=2,140 loops=1)

  • Sort Key: fbs.form_id
  • Sort Method: quicksort Memory: 264kB
49. 0.229 8.251 ↑ 1.0 2,140 1

Merge Left Join (cost=360.51..384.60 rows=2,140 width=47) (actual time=7.919..8.251 rows=2,140 loops=1)

  • Merge Cond: ((bs_1.registry_id = tbp_1.registry_id) AND (bs_1.institute_id = tbp_1.institute_id))
50. 0.857 2.370 ↑ 1.0 2,140 1

Sort (cost=300.43..305.78 rows=2,140 width=75) (actual time=2.259..2.370 rows=2,140 loops=1)

  • Sort Key: bs_1.registry_id, bs_1.institute_id
  • Sort Method: quicksort Memory: 397kB
51. 0.537 1.513 ↑ 1.0 2,140 1

Hash Join (cost=126.03..182.06 rows=2,140 width=75) (actual time=0.785..1.513 rows=2,140 loops=1)

  • Hash Cond: (fbs.bio_sample_id = bs_1.id)
52. 0.206 0.206 ↑ 1.0 2,140 1

Seq Scan on forms_bio_samples fbs (cost=0.00..50.40 rows=2,140 width=32) (actual time=0.005..0.206 rows=2,140 loops=1)

53. 0.411 0.770 ↑ 1.0 2,579 1

Hash (cost=93.79..93.79 rows=2,579 width=59) (actual time=0.769..0.770 rows=2,579 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 262kB
54. 0.359 0.359 ↑ 1.0 2,579 1

Seq Scan on bio_samples bs_1 (cost=0.00..93.79 rows=2,579 width=59) (actual time=0.005..0.359 rows=2,579 loops=1)

55. 0.003 5.652 ↓ 0.0 0 1

Sort (cost=60.08..62.58 rows=1,000 width=36) (actual time=5.652..5.652 rows=0 loops=1)

  • Sort Key: tbp_1.registry_id, tbp_1.institute_id
  • Sort Method: quicksort Memory: 25kB
56. 5.649 5.649 ↓ 0.0 0 1

Function Scan on trustee_biobank_permissions tbp_1 (cost=0.25..10.25 rows=1,000 width=36) (actual time=5.649..5.649 rows=0 loops=1)

57. 0.001 5.880 ↓ 0.0 0 1

Hash (cost=10.25..10.25 rows=1,000 width=36) (actual time=5.880..5.880 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
58. 5.879 5.879 ↓ 0.0 0 1

Function Scan on trustee_biobank_permissions tbp (cost=0.25..10.25 rows=1,000 width=36) (actual time=5.879..5.879 rows=0 loops=1)

59. 0.005 0.038 ↑ 1.0 22 1

Hash (cost=2.46..2.46 rows=22 width=24) (actual time=0.038..0.038 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
60. 0.003 0.033 ↑ 1.0 22 1

Subquery Scan on ftc (cost=2.02..2.46 rows=22 width=24) (actual time=0.028..0.033 rows=22 loops=1)

61. 0.020 0.030 ↑ 1.0 22 1

HashAggregate (cost=2.02..2.24 rows=22 width=24) (actual time=0.027..0.030 rows=22 loops=1)

  • Group Key: ftbst.form_type_id
62. 0.010 0.010 ↑ 1.0 68 1

Seq Scan on form_types_bio_sample_types ftbst (cost=0.00..1.68 rows=68 width=16) (actual time=0.007..0.010 rows=68 loops=1)

Execution time : 1,348.184 ms