explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9MCi

Settings
# exclusive inclusive rows x rows loops node
1. 0.201 3,133.204 ↑ 1.0 90 1

Nested Loop (cost=10.12..86,964.62 rows=90 width=259) (actual time=3,083.535..3,133.204 rows=90 loops=1)

  • Join Filter: (institutes.id = institutes_1.id)
  • Rows Removed by Join Filter: 4005
2. 0.013 0.013 ↑ 1.0 90 1

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

3. 0.184 3,132.990 ↑ 2.0 46 90

Materialize (cost=10.12..86,840.77 rows=90 width=16) (actual time=0.100..34.811 rows=46 loops=90)

4. 5.857 3,132.806 ↑ 1.0 90 1

Group (cost=10.12..86,839.42 rows=90 width=16) (actual time=9.003..3,132.806 rows=90 loops=1)

  • Group Key: institutes_1.id
5. 32.334 3,126.949 ↓ 4.1 119,907 1

Nested Loop (cost=10.12..86,765.60 rows=29,527 width=16) (actual time=9.002..3,126.949 rows=119,907 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) AND (bacl.trustee_id = users_user_groups.user_id))))
  • Rows Removed by Join Filter: 315492
6. 0.055 0.055 ↑ 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.006..0.055 rows=90 loops=1)

  • Heap Fetches: 90
7. 16.764 3,094.560 ↓ 2.8 4,838 90

Materialize (cost=9.97..83,638.18 rows=1,728 width=64) (actual time=0.003..34.384 rows=4,838 loops=90)

8. 3,047.449 3,077.796 ↓ 2.8 4,892 1

Nested Loop (cost=9.97..83,629.54 rows=1,728 width=64) (actual time=0.234..3,077.796 rows=4,892 loops=1)

  • Join Filter: (((users_user_groups.user_id = 'a342fa7e-4300-11e8-b98e-c708e2493908'::uuid) AND (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: 303003
9. 2.016 2.567 ↑ 1.0 2,315 1

Hash Join (cost=9.97..98.22 rows=2,326 width=42) (actual time=0.106..2.567 rows=2,315 loops=1)

  • Hash Cond: (registry_acl.user_group_id = users_user_groups.user_group_id)
10. 0.454 0.454 ↑ 1.0 2,326 1

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

11. 0.052 0.097 ↑ 1.0 310 1

Hash (cost=6.10..6.10 rows=310 width=32) (actual time=0.096..0.097 rows=310 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
12. 0.045 0.045 ↑ 1.0 310 1

Seq Scan on users_user_groups (cost=0.00..6.10 rows=310 width=32) (actual time=0.005..0.045 rows=310 loops=1)

13. 27.752 27.780 ↑ 1.0 133 2,315

Materialize (cost=0.00..5.00 rows=133 width=36) (actual time=0.000..0.012 rows=133 loops=2,315)

14. 0.028 0.028 ↑ 1.0 133 1

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

Planning time : 0.755 ms
Execution time : 3,133.276 ms