explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h9eH

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 146.981 ↓ 0.0 0 1

HashAggregate (cost=33,220.93..33,460.45 rows=23,952 width=34) (actual time=146.981..146.981 rows=0 loops=1)

  • Group Key: COALESCE((1), p.owner_type), COALESCE(companies.id, p.owner_id), COALESCE((4), p.obj_type), COALESCE(users.id, p.obj_id), COALESCE((false), p.read_only), CASE WHEN (p.obj_id IS NULL) THEN 1 WHEN (users.id IS NULL) THEN 3 ELSE 2 END
2. 25.044 146.957 ↓ 0.0 0 1

Hash Full Join (cost=1,733.95..32,861.65 rows=23,952 width=34) (actual time=146.957..146.957 rows=0 loops=1)

  • Hash Cond: (((1) = p.owner_type) AND (companies.id = p.owner_id) AND ((4) = p.obj_type) AND (users.id = p.obj_id))
  • Filter: ((users.id IS NULL) OR (p.obj_id IS NULL) OR (p.read_only <> (false)))
  • Rows Removed by Filter: 47665
3. 3.313 95.776 ↓ 1.0 47,665 1

Append (cost=0.00..27,357.77 rows=47,124 width=17) (actual time=0.021..95.776 rows=47,665 loops=1)

4. 0.080 0.257 ↓ 3.0 303 1

Nested Loop (cost=0.00..7.09 rows=101 width=8) (actual time=0.021..0.257 rows=303 loops=1)

5. 0.006 0.006 ↓ 3.0 3 1

Seq Scan on companies (cost=0.00..1.02 rows=1 width=4) (actual time=0.005..0.006 rows=3 loops=1)

  • Filter: is_active
6. 0.171 0.171 ↑ 1.0 101 3

Seq Scan on users (cost=0.00..5.06 rows=101 width=4) (actual time=0.004..0.057 rows=101 loops=3)

  • Filter: (is_admin AND is_active)
  • Rows Removed by Filter: 9
7. 0.022 0.092 ↓ 3.0 3 1

HashAggregate (cost=19.53..19.54 rows=1 width=12) (actual time=0.090..0.092 rows=3 loops=1)

  • Group Key: companies_1.id, u.id
8. 0.009 0.070 ↓ 5.0 5 1

Nested Loop (cost=0.30..19.52 rows=1 width=12) (actual time=0.037..0.070 rows=5 loops=1)

9. 0.021 0.047 ↓ 7.0 7 1

Nested Loop (cost=0.15..18.57 rows=1 width=12) (actual time=0.025..0.047 rows=7 loops=1)

10. 0.002 0.002 ↓ 1.5 3 1

Seq Scan on companies companies_1 (cost=0.00..1.02 rows=2 width=4) (actual time=0.002..0.002 rows=3 loops=1)

11. 0.024 0.024 ↓ 2.0 2 3

Index Only Scan using company_user_pkey on company_user cu (cost=0.15..8.77 rows=1 width=12) (actual time=0.005..0.008 rows=2 loops=3)

  • Index Cond: ((company_id = companies_1.id) AND (permission = ANY ('{8003,8004,8005}'::integer[])))
  • Heap Fetches: 7
12. 0.014 0.014 ↑ 1.0 1 7

Index Scan using pk_users on users u (cost=0.14..0.93 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=7)

  • Index Cond: (id = cu.user_id)
  • Filter: (NOT is_admin)
  • Rows Removed by Filter: 0
13. 37.991 37.991 ↑ 1.0 10,794 1

Seq Scan on devices (cost=0.00..12,502.17 rows=10,794 width=8) (actual time=9.387..37.991 rows=10,794 loops=1)

  • Filter: ((company_id IS NOT NULL) AND (activation_status < 9999))
  • Rows Removed by Filter: 100
14. 6.997 54.037 ↓ 1.0 36,502 1

Hash Join (cost=12,638.37..14,308.84 rows=36,183 width=8) (actual time=35.184..54.037 rows=36,502 loops=1)

  • Hash Cond: (device_group.group_id = company_device_group.id)
15. 9.256 47.032 ↓ 1.0 36,502 1

Hash Join (cost=12,637.10..13,810.05 rows=36,183 width=8) (actual time=35.165..47.032 rows=36,502 loops=1)

  • Hash Cond: (device_group.device_id = devices_1.id)
16. 2.651 2.651 ↑ 1.0 36,518 1

Seq Scan on device_group (cost=0.00..674.18 rows=36,518 width=8) (actual time=0.002..2.651 rows=36,518 loops=1)

17. 1.302 35.125 ↑ 1.0 10,794 1

Hash (cost=12,502.17..12,502.17 rows=10,794 width=4) (actual time=35.125..35.125 rows=10,794 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 508kB
18. 33.823 33.823 ↑ 1.0 10,794 1

Seq Scan on devices devices_1 (cost=0.00..12,502.17 rows=10,794 width=4) (actual time=7.468..33.823 rows=10,794 loops=1)

  • Filter: (activation_status < 9999)
  • Rows Removed by Filter: 100
19. 0.004 0.008 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=4) (actual time=0.008..0.008 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.004 0.004 ↑ 1.0 12 1

Seq Scan on company_device_group (cost=0.00..1.12 rows=12 width=4) (actual time=0.004..0.004 rows=12 loops=1)

21. 0.005 0.079 ↑ 7.6 5 1

HashAggregate (cost=47.50..47.88 rows=38 width=8) (actual time=0.079..0.079 rows=5 loops=1)

  • Group Key: 2, company_device_group_1.id, 4, users_1.id, false
22. 0.010 0.074 ↑ 7.6 5 1

Hash Join (cost=7.62..47.03 rows=38 width=8) (actual time=0.073..0.074 rows=5 loops=1)

  • Hash Cond: (user_group.user_id = users_1.id)
23. 0.014 0.029 ↑ 14.2 5 1

Hash Join (cost=1.24..40.00 rows=71 width=8) (actual time=0.028..0.029 rows=5 loops=1)

  • Hash Cond: (user_group.group_id = company_device_group_1.id)
24. 0.002 0.002 ↑ 408.0 5 1

Seq Scan on user_group (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.002..0.002 rows=5 loops=1)

25. 0.005 0.013 ↑ 1.0 7 1

Hash (cost=1.15..1.15 rows=7 width=4) (actual time=0.013..0.013 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.008 0.008 ↑ 1.0 7 1

Seq Scan on company_device_group company_device_group_1 (cost=0.00..1.15 rows=7 width=4) (actual time=0.006..0.008 rows=7 loops=1)

  • Filter: (group_type = 2)
  • Rows Removed by Filter: 5
27. 0.013 0.035 ↓ 1.0 110 1

Hash (cost=5.06..5.06 rows=106 width=4) (actual time=0.035..0.035 rows=110 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
28. 0.022 0.022 ↓ 1.0 110 1

Seq Scan on users users_1 (cost=0.00..5.06 rows=106 width=4) (actual time=0.007..0.022 rows=110 loops=1)

  • Filter: is_active
29. 0.007 0.007 ↓ 8.3 58 1

Seq Scan on group_permissions (cost=0.00..1.07 rows=7 width=17) (actual time=0.004..0.007 rows=58 loops=1)

30. 19.271 26.137 ↑ 1.0 47,665 1

Hash (cost=780.65..780.65 rows=47,665 width=17) (actual time=26.137..26.137 rows=47,665 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2793kB
31. 6.866 6.866 ↑ 1.0 47,665 1

Seq Scan on perm_cache_relations p (cost=0.00..780.65 rows=47,665 width=17) (actual time=0.007..6.866 rows=47,665 loops=1)