explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kBlY

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 324.388 ↑ 1.0 200 1

Limit (cost=43,579.68..43,708.50 rows=200 width=2,136) (actual time=319.735..324.388 rows=200 loops=1)

2. 3.076 324.361 ↑ 911.6 200 1

Hash Left Join (cost=43,579.68..159,189.02 rows=182,322 width=2,136) (actual time=319.734..324.361 rows=200 loops=1)

  • Hash Cond: (f.form_type_id = ftc.form_type_id)
3. 0.125 321.214 ↑ 911.6 200 1

Hash Left Join (cost=43,574.75..111,276.09 rows=182,322 width=2,115) (actual time=319.399..321.214 rows=200 loops=1)

  • Hash Cond: ((u.id = bs.user_id) AND (f.id = bs.form_id))
4. 0.061 298.516 ↑ 911.6 200 1

Nested Loop (cost=42,602.32..109,346.43 rows=182,322 width=2,083) (actual time=296.816..298.516 rows=200 loops=1)

5. 0.017 0.017 ↑ 1.0 1 1

Index Only Scan using users_id_institute_id_idx on users u (cost=0.28..4.29 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (id = '1304ff22-3368-11e8-ba9d-43a62f2ef9fd'::uuid)
  • Heap Fetches: 0
6. 0.471 298.438 ↑ 911.6 200 1

Hash Right Join (cost=42,602.05..107,518.92 rows=182,322 width=2,067) (actual time=296.795..298.438 rows=200 loops=1)

  • Hash Cond: (fp.id = f.id)
7. 0.072 72.194 ↑ 238.3 765 1

Subquery Scan on fp (cost=17,696.32..66,923.26 rows=182,322 width=24) (actual time=70.913..72.194 rows=765 loops=1)

8. 21.803 72.122 ↑ 238.3 765 1

HashAggregate (cost=17,696.32..65,100.04 rows=182,322 width=40) (actual time=70.912..72.122 rows=765 loops=1)

  • Group Key: f_1.id, r.trial_manager_id
9. 31.953 50.319 ↑ 2.2 83,591 1

Hash Join (cost=2,055.82..15,873.10 rows=182,322 width=68) (actual time=11.804..50.319 rows=83,591 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)))
  • Rows Removed by Join Filter: 32,300
10. 6.682 6.682 ↑ 1.0 43,330 1

Seq Scan on forms f_1 (cost=0.00..10,676.30 rows=43,330 width=80) (actual time=0.101..6.682 rows=43,330 loops=1)

11. 2.465 11.684 ↑ 2.1 15,173 1

Hash (cost=1,661.17..1,661.17 rows=31,572 width=68) (actual time=11.684..11.684 rows=15,173 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,365kB
12. 3.912 9.219 ↑ 2.1 15,173 1

Hash Join (cost=328.86..1,661.17 rows=31,572 width=68) (actual time=2.681..9.219 rows=15,173 loops=1)

  • Hash Cond: (a.registry_id = r.id)
  • Join Filter: ((racl.institute_id = ap_1.institute_id) OR (racl.institute_id IS NULL))
  • Rows Removed by Join Filter: 11,292
13. 2.140 4.859 ↑ 1.0 7,287 1

Hash Join (cost=277.96..506.97 rows=7,287 width=48) (actual time=2.227..4.859 rows=7,287 loops=1)

  • Hash Cond: (ap_1.attendance_id = a.id)
14. 0.513 0.513 ↑ 1.0 7,287 1

Seq Scan on attendance_periods ap_1 (cost=0.00..209.87 rows=7,287 width=48) (actual time=0.005..0.513 rows=7,287 loops=1)

15. 1.180 2.206 ↑ 1.0 7,287 1

Hash (cost=186.87..186.87 rows=7,287 width=32) (actual time=2.206..2.206 rows=7,287 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 520kB
16. 1.026 1.026 ↑ 1.0 7,287 1

Seq Scan on attendances a (cost=0.00..186.87 rows=7,287 width=32) (actual time=0.004..1.026 rows=7,287 loops=1)

17. 0.013 0.448 ↑ 8.4 41 1

Hash (cost=46.57..46.57 rows=346 width=100) (actual time=0.448..0.448 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
18. 0.016 0.435 ↑ 8.4 41 1

Hash Join (cost=19.97..46.57 rows=346 width=100) (actual time=0.273..0.435 rows=41 loops=1)

  • Hash Cond: (racl.registry_id = r.id)
19. 0.095 0.406 ↑ 8.4 41 1

Hash Join (cost=17.25..42.82 rows=346 width=68) (actual time=0.256..0.406 rows=41 loops=1)

  • Hash Cond: (racl.trustee_id = get_trustee_list.trustee_id)
20. 0.063 0.063 ↑ 1.0 691 1

Seq Scan on registry_acl racl (cost=0.00..19.91 rows=691 width=84) (actual time=0.002..0.063 rows=691 loops=1)

21. 0.002 0.248 ↑ 200.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.004 0.246 ↑ 200.0 1 1

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

  • Group Key: get_trustee_list.trustee_id
23. 0.242 0.242 ↑ 1,000.0 1 1

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

24. 0.004 0.013 ↑ 1.0 32 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
25. 0.009 0.009 ↑ 1.0 32 1

Seq Scan on registries r (cost=0.00..2.32 rows=32 width=32) (actual time=0.005..0.009 rows=32 loops=1)

26. 63.102 225.773 ↑ 1.0 43,330 1

Hash (cost=13,319.10..13,319.10 rows=43,330 width=2,059) (actual time=225.773..225.773 rows=43,330 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 13,439kB
27. 16.577 162.671 ↑ 1.0 43,330 1

Hash Left Join (cost=1,495.75..13,319.10 rows=43,330 width=2,059) (actual time=15.831..162.671 rows=43,330 loops=1)

  • Hash Cond: ((att.registry_id = tbp.registry_id) AND (ap.institute_id = tbp.institute_id))
28. 15.150 137.628 ↑ 1.0 43,330 1

Hash Join (cost=905.30..12,501.15 rows=43,330 width=2,055) (actual time=7.355..137.628 rows=43,330 loops=1)

  • Hash Cond: (f.owner_id = o.id)
29. 15.328 122.336 ↑ 1.0 43,330 1

Hash Join (cost=878.44..12,359.64 rows=43,330 width=2,037) (actual time=7.206..122.336 rows=43,330 loops=1)

  • Hash Cond: (f.physician_id = ph.id)
30. 18.433 106.859 ↑ 1.0 43,330 1

Hash Join (cost=851.58..12,218.13 rows=43,330 width=1,967) (actual time=7.052..106.859 rows=43,330 loops=1)

  • Hash Cond: ((att.patient_id = rp.patient_id) AND (att.registry_id = rp.registry_id))
31. 19.699 86.426 ↑ 1.0 43,330 1

Hash Join (cost=594.61..11,733.66 rows=43,330 width=1,957) (actual time=5.044..86.426 rows=43,330 loops=1)

  • Hash Cond: (ap.attendance_id = att.id)
32. 11.622 64.311 ↑ 1.0 43,330 1

Hash Join (cost=316.65..11,341.89 rows=43,330 width=1,802) (actual time=2.617..64.311 rows=43,330 loops=1)

  • Hash Cond: (ap.institute_id = i.id)
33. 18.121 52.639 ↑ 1.0 43,330 1

Hash Join (cost=308.00..11,215.42 rows=43,330 width=1,787) (actual time=2.560..52.639 rows=43,330 loops=1)

  • Hash Cond: (f.attendance_period_id = ap.id)
34. 22.288 32.135 ↑ 1.0 43,330 1

Hash Join (cost=7.04..10,800.65 rows=43,330 width=1,670) (actual time=0.168..32.135 rows=43,330 loops=1)

  • Hash Cond: (f.form_type_id = ft.id)
35. 9.799 9.799 ↑ 1.0 43,330 1

Seq Scan on forms f (cost=0.00..10,676.30 rows=43,330 width=1,638) (actual time=0.113..9.799 rows=43,330 loops=1)

36. 0.018 0.048 ↑ 1.0 135 1

Hash (cost=5.35..5.35 rows=135 width=32) (actual time=0.048..0.048 rows=135 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
37. 0.030 0.030 ↑ 1.0 135 1

Seq Scan on form_types ft (cost=0.00..5.35 rows=135 width=32) (actual time=0.003..0.030 rows=135 loops=1)

38. 1.304 2.383 ↑ 1.0 7,287 1

Hash (cost=209.87..209.87 rows=7,287 width=133) (actual time=2.383..2.383 rows=7,287 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 1,159kB
39. 1.079 1.079 ↑ 1.0 7,287 1

Seq Scan on attendance_periods ap (cost=0.00..209.87 rows=7,287 width=133) (actual time=0.003..1.079 rows=7,287 loops=1)

40. 0.018 0.050 ↑ 1.0 118 1

Hash (cost=7.18..7.18 rows=118 width=31) (actual time=0.050..0.050 rows=118 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
41. 0.032 0.032 ↑ 1.0 118 1

Seq Scan on institutes i (cost=0.00..7.18 rows=118 width=31) (actual time=0.008..0.032 rows=118 loops=1)

42. 1.320 2.416 ↑ 1.0 7,287 1

Hash (cost=186.87..186.87 rows=7,287 width=171) (actual time=2.416..2.416 rows=7,287 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 989kB
43. 1.096 1.096 ↑ 1.0 7,287 1

Seq Scan on attendances att (cost=0.00..186.87 rows=7,287 width=171) (actual time=0.007..1.096 rows=7,287 loops=1)

44. 1.225 2.000 ↑ 1.0 6,879 1

Hash (cost=153.79..153.79 rows=6,879 width=42) (actual time=2.000..2.000 rows=6,879 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 562kB
45. 0.775 0.775 ↑ 1.0 6,879 1

Seq Scan on registries_patients rp (cost=0.00..153.79 rows=6,879 width=42) (actual time=0.004..0.775 rows=6,879 loops=1)

46. 0.084 0.149 ↑ 1.0 527 1

Hash (cost=20.27..20.27 rows=527 width=86) (actual time=0.149..0.149 rows=527 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 62kB
47. 0.065 0.065 ↑ 1.0 527 1

Seq Scan on users ph (cost=0.00..20.27 rows=527 width=86) (actual time=0.002..0.065 rows=527 loops=1)

48. 0.064 0.142 ↑ 1.0 527 1

Hash (cost=20.27..20.27 rows=527 width=34) (actual time=0.142..0.142 rows=527 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
49. 0.078 0.078 ↑ 1.0 527 1

Seq Scan on users o (cost=0.00..20.27 rows=527 width=34) (actual time=0.011..0.078 rows=527 loops=1)

50. 0.322 8.466 ↓ 6.1 2,360 1

Hash (cost=584.62..584.62 rows=389 width=36) (actual time=8.466..8.466 rows=2,360 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 189kB
51. 0.161 8.144 ↓ 6.1 2,360 1

Subquery Scan on tbp (cost=576.84..584.62 rows=389 width=36) (actual time=7.649..8.144 rows=2,360 loops=1)

52. 0.869 7.983 ↓ 6.1 2,360 1

HashAggregate (cost=576.84..580.73 rows=389 width=36) (actual time=7.648..7.983 rows=2,360 loops=1)

  • Group Key: ba.registry_id, i_1.id
53. 0.742 7.114 ↓ 1.2 2,360 1

Hash Join (cost=452.48..562.24 rows=1,947 width=36) (actual time=4.783..7.114 rows=2,360 loops=1)

  • Hash Cond: (ba.trustee_id = get_trustee_list_1.trustee_id)
54. 3.702 6.104 ↓ 2.4 9,311 1

HashAggregate (cost=435.23..474.17 rows=3,894 width=52) (actual time=4.508..6.104 rows=9,311 loops=1)

  • Group Key: ba.trustee_id, ba.registry_id, i_1.id
55. 1.640 2.402 ↓ 1.0 9,312 1

Nested Loop Left Join (cost=0.00..342.56 rows=9,267 width=52) (actual time=0.011..2.402 rows=9,312 loops=1)

  • Join Filter: ((ba.institute_id = i_1.id) OR (ba.institute_id IS NULL))
  • Rows Removed by Join Filter: 12,636
56. 0.018 0.018 ↑ 1.0 186 1

Seq Scan on biobank_acl ba (cost=0.00..5.86 rows=186 width=52) (actual time=0.004..0.018 rows=186 loops=1)

57. 0.728 0.744 ↑ 1.0 118 186

Materialize (cost=0.00..7.77 rows=118 width=16) (actual time=0.000..0.004 rows=118 loops=186)

58. 0.016 0.016 ↑ 1.0 118 1

Seq Scan on institutes i_1 (cost=0.00..7.18 rows=118 width=16) (actual time=0.004..0.016 rows=118 loops=1)

59. 0.001 0.268 ↑ 200.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.005 0.267 ↑ 200.0 1 1

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

  • Group Key: get_trustee_list_1.trustee_id
61. 0.262 0.262 ↑ 1,000.0 1 1

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

62. 0.427 22.573 ↑ 1.0 1,641 1

Hash (cost=947.81..947.81 rows=1,641 width=64) (actual time=22.573..22.573 rows=1,641 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 458kB
63. 0.142 22.146 ↑ 1.0 1,641 1

Subquery Scan on bs (cost=910.88..947.81 rows=1,641 width=64) (actual time=20.343..22.146 rows=1,641 loops=1)

64. 9.710 22.004 ↑ 1.0 1,641 1

HashAggregate (cost=910.88..931.40 rows=1,641 width=64) (actual time=20.342..22.004 rows=1,641 loops=1)

  • Group Key: u_1.id, fbs.form_id
65. 0.224 12.294 ↑ 1.0 2,903 1

Nested Loop (cost=757.90..881.85 rows=2,903 width=63) (actual time=10.349..12.294 rows=2,903 loops=1)

66. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using users_id_institute_id_idx on users u_1 (cost=0.28..4.29 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=1)

  • Index Cond: (id = '1304ff22-3368-11e8-ba9d-43a62f2ef9fd'::uuid)
  • Heap Fetches: 0
67. 0.573 12.058 ↑ 1.0 2,903 1

Hash Left Join (cost=757.63..848.53 rows=2,903 width=47) (actual time=10.336..12.058 rows=2,903 loops=1)

  • Hash Cond: ((bs_1.registry_id = tbp_1.registry_id) AND (bs_1.institute_id = tbp_1.institute_id))
68. 0.854 2.131 ↑ 1.0 2,903 1

Hash Join (cost=167.18..242.84 rows=2,903 width=75) (actual time=0.976..2.131 rows=2,903 loops=1)

  • Hash Cond: (fbs.bio_sample_id = bs_1.id)
69. 0.310 0.310 ↑ 1.0 2,903 1

Seq Scan on forms_bio_samples fbs (cost=0.00..68.03 rows=2,903 width=32) (actual time=0.004..0.310 rows=2,903 loops=1)

70. 0.509 0.967 ↑ 1.0 3,430 1

Hash (cost=124.30..124.30 rows=3,430 width=59) (actual time=0.967..0.967 rows=3,430 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 337kB
71. 0.458 0.458 ↑ 1.0 3,430 1

Seq Scan on bio_samples bs_1 (cost=0.00..124.30 rows=3,430 width=59) (actual time=0.003..0.458 rows=3,430 loops=1)

72. 0.316 9.354 ↓ 6.1 2,360 1

Hash (cost=584.62..584.62 rows=389 width=36) (actual time=9.354..9.354 rows=2,360 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 189kB
73. 0.155 9.038 ↓ 6.1 2,360 1

Subquery Scan on tbp_1 (cost=576.84..584.62 rows=389 width=36) (actual time=8.554..9.038 rows=2,360 loops=1)

74. 0.965 8.883 ↓ 6.1 2,360 1

HashAggregate (cost=576.84..580.73 rows=389 width=36) (actual time=8.546..8.883 rows=2,360 loops=1)

  • Group Key: ba_1.registry_id, i_2.id
75. 0.893 7.918 ↓ 1.2 2,360 1

Hash Join (cost=452.48..562.24 rows=1,947 width=36) (actual time=5.087..7.918 rows=2,360 loops=1)

  • Hash Cond: (ba_1.trustee_id = get_trustee_list_2.trustee_id)
76. 4.161 6.805 ↓ 2.4 9,311 1

HashAggregate (cost=435.23..474.17 rows=3,894 width=52) (actual time=4.860..6.805 rows=9,311 loops=1)

  • Group Key: ba_1.trustee_id, ba_1.registry_id, i_2.id
77. 1.682 2.644 ↓ 1.0 9,312 1

Nested Loop Left Join (cost=0.00..342.56 rows=9,267 width=52) (actual time=0.010..2.644 rows=9,312 loops=1)

  • Join Filter: ((ba_1.institute_id = i_2.id) OR (ba_1.institute_id IS NULL))
  • Rows Removed by Join Filter: 12,636
78. 0.032 0.032 ↑ 1.0 186 1

Seq Scan on biobank_acl ba_1 (cost=0.00..5.86 rows=186 width=52) (actual time=0.003..0.032 rows=186 loops=1)

79. 0.914 0.930 ↑ 1.0 118 186

Materialize (cost=0.00..7.77 rows=118 width=16) (actual time=0.000..0.005 rows=118 loops=186)

80. 0.016 0.016 ↑ 1.0 118 1

Seq Scan on institutes i_2 (cost=0.00..7.18 rows=118 width=16) (actual time=0.004..0.016 rows=118 loops=1)

81. 0.001 0.220 ↑ 200.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
82. 0.004 0.219 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=16) (actual time=0.218..0.219 rows=1 loops=1)

  • Group Key: get_trustee_list_2.trustee_id
83. 0.215 0.215 ↑ 1,000.0 1 1

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

84. 0.008 0.071 ↑ 1.0 34 1

Hash (cost=4.51..4.51 rows=34 width=24) (actual time=0.071..0.071 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
85. 0.003 0.063 ↑ 1.0 34 1

Subquery Scan on ftc (cost=3.83..4.51 rows=34 width=24) (actual time=0.055..0.063 rows=34 loops=1)

86. 0.039 0.060 ↑ 1.0 34 1

HashAggregate (cost=3.83..4.17 rows=34 width=24) (actual time=0.054..0.060 rows=34 loops=1)

  • Group Key: ftbst.form_type_id
87. 0.021 0.021 ↑ 1.0 122 1

Seq Scan on form_types_bio_sample_types ftbst (cost=0.00..3.22 rows=122 width=16) (actual time=0.011..0.021 rows=122 loops=1)

Planning time : 19.987 ms
Execution time : 330.600 ms