explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T8jLm

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 2,373.443 ↑ 45.0 2 1
→

Nested Loop (cost=0.14..84,839.98 rows=90 width=259) (actual time=2,373.421..2,373.443 rows=2 loops=1)

  • Join Filter: (institutes.id = institutes_1.id)
  • Rows Removed by Join Filter: 177
2. 0.015 0.015 ↑ 1.0 90 1
→

Seq Scan on institutes (cost=0.00..3.90 rows=90 width=259) (actual time=0.007..0.015 rows=90 loops=1)

3. 0.000 2,373.390 ↑ 45.0 2 90
→

Materialize (cost=0.14..84,716.12 rows=90 width=16) (actual time=26.245..26.371 rows=2 loops=90)

4. 0.062 2,373.395 ↑ 45.0 2 1
→

Group (cost=0.14..84,714.77 rows=90 width=16) (actual time=2,362.089..2,373.395 rows=2 loops=1)

  • Group Key: institutes_1.id
5. 17.676 2,373.333 ↑ 6.8 1,370 1
→

Nested Loop (cost=0.14..84,691.43 rows=9,335 width=16) (actual time=2,362.087..2,373.333 rows=1,370 loops=1)

  • Join Filter: (((registry_acl.institute_id IS NULL) OR (registry_acl.institute_id = institutes_1.id)) AND ((bacl.institute_id IS NULL) OR (bacl.institute_id = institutes_1.id)))
  • Rows Removed by Join Filter: 280150
6. 0.087 0.087 ↑ 1.0 90 1
→

Index Only Scan using institutes_pkey on institutes institutes_1 (cost=0.14..21.34 rows=90 width=16) (actual time=0.008..0.087 rows=90 loops=1)

  • Heap Fetches: 90
7. 11.160 2,355.570 ↓ 5.7 3,128 90
→

Materialize (cost=0.00..83,813.08 rows=545 width=32) (actual time=0.008..26.173 rows=3,128 loops=90)

8. 17.676 2,344.410 ↓ 5.7 3,128 1
→

Nested Loop (cost=0.00..83,810.36 rows=545 width=32) (actual time=0.685..2,344.410 rows=3,128 loops=1)

  • Join Filter: ((registry_acl.user_group_id = uug.user_group_id) OR (bacl.trustee_id = uug.user_group_id) OR (bacl.trustee_id = uug.user_id))
  • Rows Removed by Join Filter: 156855
9. 0.022 0.022 ↑ 1.0 1 1
→

Seq Scan on users_user_groups uug (cost=0.00..6.88 rows=1 width=32) (actual time=0.008..0.022 rows=1 loops=1)

  • Filter: (user_id = 'a342fa7e-4300-11e8-b98e-c708e2493908'::uuid)
  • Rows Removed by Filter: 309
10. 2,307.683 2,326.712 ↓ 2.8 159,983 1
→

Nested Loop (cost=0.00..82,814.19 rows=56,531 width=64) (actual time=0.107..2,326.712 rows=159,983 loops=1)

  • Join Filter: ((registry_acl.permission = 'frm_create'::text) OR ('{read_institute_bio_sample,read_admin_bio_sample}'::text[] <@ to_biobank_perm_flags(bacl.permission_mask)))
  • Rows Removed by Join Filter: 112159
11. 0.421 0.421 ↑ 1.0 2,326 1
→

Seq Scan on registry_acl (cost=0.00..56.26 rows=2,326 width=42) (actual time=0.004..0.421 rows=2,326 loops=1)

12. 18.575 18.608 ↑ 1.1 117 2,326
→

Materialize (cost=0.00..5.00 rows=133 width=36) (actual time=0.000..0.008 rows=117 loops=2,326)

13. 0.033 0.033 ↑ 1.1 117 1
→

Seq Scan on biobank_acl bacl (cost=0.00..4.33 rows=133 width=36) (actual time=0.003..0.033 rows=117 loops=1)

Planning time : 0.573 ms
Execution time : 2,373.497 ms