#	exclusive	inclusive	rows x	rows	loops	node
1.	0.024	31.757	↓ 1.3	90	1	→ Hash Join (cost=118.78..122.93 rows=70 width=259) (actual time=31.731..31.757 rows=90 loops=1) Hash Cond: (institutes.id = institutes_1.id)
2.	0.011	0.011	↑ 1.0	90	1	→ Seq Scan on institutes (cost=0.00..3.90 rows=90 width=259) (actual time=0.006..0.011 rows=90 loops=1)
3.	0.013	31.722	↓ 1.3	90	1	→ Hash (cost=117.91..117.91 rows=70 width=16) (actual time=31.722..31.722 rows=90 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 13kB
4.	1.158	31.709	↓ 1.3	90	1	→ HashAggregate (cost=116.51..117.21 rows=70 width=16) (actual time=31.700..31.709 rows=90 loops=1) Group Key: institutes_1.id
5.	3.901	30.551	↓ 152.0	10,638	1	→ Nested Loop (cost=15.57..116.33 rows=70 width=16) (actual time=8.278..30.551 rows=10,638 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: 48492
6.	0.010	0.010	↑ 1.0	90	1	→ Seq Scan on institutes institutes_1 (cost=0.00..3.90 rows=90 width=16) (actual time=0.001..0.010 rows=90 loops=1)
7.	2.161	26.640	↓ 164.2	657	90	→ Materialize (cost=15.57..106.14 rows=4 width=32) (actual time=0.001..0.296 rows=657 loops=90)
8.	6.073	24.479	↓ 164.2	657	1	→ Nested Loop (cost=15.57..106.12 rows=4 width=32) (actual time=0.123..24.479 rows=657 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: 49068
9.	0.216	6.355	↓ 117.0	117	1	→ Nested Loop (cost=0.00..44.80 rows=1 width=64) (actual time=0.088..6.355 rows=117 loops=1)
10.	3.331	3.331	↓ 117.0	117	1	→ Seq Scan on biobank_acl bacl (cost=0.00..37.91 rows=1 width=32) (actual time=0.082..3.331 rows=117 loops=1) Filter: ('{read_institute_bio_sample,read_admin_bio_sample}'::text[] && to_biobank_perm_flags(permission_mask))
11.	2.808	2.808	↑ 1.0	1	117	→ Seq Scan on users_user_groups uug (cost=0.00..6.88 rows=1 width=32) (actual time=0.005..0.024 rows=1 loops=117) Filter: (user_id = 'a342fa7e-4300-11e8-b98e-c708e2493908'::uuid) Rows Removed by Filter: 309
12.	8.775	12.051	↑ 1.0	425	117	→ Bitmap Heap Scan on registry_acl (cost=15.57..53.89 rows=425 width=32) (actual time=0.032..0.103 rows=425 loops=117) Recheck Cond: (permission = 'frm_create'::text) Heap Blocks: exact=3861
13.	3.276	3.276	↑ 1.0	425	117	→ Bitmap Index Scan on registry_acl_permission_idx (cost=0.00..15.47 rows=425 width=0) (actual time=0.028..0.028 rows=425 loops=117) Index Cond: (permission = 'frm_create'::text)

Planning time	:	0.411 ms
Execution time	:	31.799 ms

QUERY PLAN
Hash Join  (cost=118.78..122.93 rows=70 width=259) (actual time=31.731..31.757 rows=90 loops=1)
  Hash Cond: (institutes.id = institutes_1.id)
  ->  Seq Scan on institutes  (cost=0.00..3.90 rows=90 width=259) (actual time=0.006..0.011 rows=90 loops=1)
  ->  Hash  (cost=117.91..117.91 rows=70 width=16) (actual time=31.722..31.722 rows=90 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 13kB
        ->  HashAggregate  (cost=116.51..117.21 rows=70 width=16) (actual time=31.700..31.709 rows=90 loops=1)
              Group Key: institutes_1.id
              ->  Nested Loop  (cost=15.57..116.33 rows=70 width=16) (actual time=8.278..30.551 rows=10638 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: 48492
                    ->  Seq Scan on institutes institutes_1  (cost=0.00..3.90 rows=90 width=16) (actual time=0.001..0.010 rows=90 loops=1)
                    ->  Materialize  (cost=15.57..106.14 rows=4 width=32) (actual time=0.001..0.296 rows=657 loops=90)
                          ->  Nested Loop  (cost=15.57..106.12 rows=4 width=32) (actual time=0.123..24.479 rows=657 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: 49068
                                ->  Nested Loop  (cost=0.00..44.80 rows=1 width=64) (actual time=0.088..6.355 rows=117 loops=1)
                                      ->  Seq Scan on biobank_acl bacl  (cost=0.00..37.91 rows=1 width=32) (actual time=0.082..3.331 rows=117 loops=1)
"                                            Filter: ('{read_institute_bio_sample,read_admin_bio_sample}'::text[] && to_biobank_perm_flags(permission_mask))"
                                      ->  Seq Scan on users_user_groups uug  (cost=0.00..6.88 rows=1 width=32) (actual time=0.005..0.024 rows=1 loops=117)
                                            Filter: (user_id = 'a342fa7e-4300-11e8-b98e-c708e2493908'::uuid)
                                            Rows Removed by Filter: 309
                                ->  Bitmap Heap Scan on registry_acl  (cost=15.57..53.89 rows=425 width=32) (actual time=0.032..0.103 rows=425 loops=117)
                                      Recheck Cond: (permission = 'frm_create'::text)
                                      Heap Blocks: exact=3861
                                      ->  Bitmap Index Scan on registry_acl_permission_idx  (cost=0.00..15.47 rows=425 width=0) (actual time=0.028..0.028 rows=425 loops=117)
                                            Index Cond: (permission = 'frm_create'::text)
Planning Time: 0.411 ms
Execution Time: 31.799 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	8.775 ms	27.6 %
Bitmap Index Scan	1	3.276 ms	10.3 %
Hash	1	0.013 ms	0.0 %
Hash Join	1	0.024 ms	0.1 %
HashAggregate	1	1.158 ms	3.6 %
Materialize	1	2.161 ms	6.8 %
Nested Loop	3	10.190 ms	32.1 %
Seq Scan	4	6.160 ms	19.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
biobank_acl	1	3.331 ms	10.5 %
Seq Scan	1	3.331 ms	100.0 %
institutes	2	0.021 ms	0.1 %
Seq Scan	2	0.021 ms	100.0 %
registry_acl	1	8.775 ms	27.6 %
Bitmap Heap Scan	1	8.775 ms	100.0 %
users_user_groups	1	2.808 ms	8.8 %
Seq Scan	1	2.808 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWLG

Color mode:

Visible columns:

Per node type stats

Per table stats