explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U3q

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 156.193 ↓ 0.0 0 1
→

HashAggregate (cost=33,220.93..33,460.45 rows=23,952 width=34) (actual time=156.193..156.193 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.784 156.171 ↓ 0.0 0 1
→

Hash Full Join (cost=1,733.95..32,861.65 rows=23,952 width=34) (actual time=156.171..156.171 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.359 98.825 ↓ 1.0 47,665 1
→

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

4. 0.062 0.140 ↓ 3.0 303 1
→

Nested Loop (cost=0.00..7.09 rows=101 width=8) (actual time=0.018..0.140 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.004..0.006 rows=3 loops=1)

  • Filter: is_active
6. 0.072 0.072 ↑ 1.0 101 3
→

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

  • Filter: (is_admin AND is_active)
  • Rows Removed by Filter: 9
7. 0.018 0.067 ↓ 3.0 3 1
→

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

  • Group Key: companies_1.id, u.id
8. 0.001 0.049 ↓ 5.0 5 1
→

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

9. 0.013 0.034 ↓ 7.0 7 1
→

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

10. 0.003 0.003 ↓ 1.5 3 1
→

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

11. 0.018 0.018 ↓ 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.004..0.006 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. 40.766 40.766 ↑ 1.0 10,794 1
→

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

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

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

  • Hash Cond: (device_group.group_id = company_device_group.id)
15. 9.206 47.761 ↓ 1.0 36,502 1
→

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

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

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

17. 1.287 35.828 ↑ 1.0 10,794 1
→

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

  • Buckets: 16384 Batches: 1 Memory Usage: 508kB
18. 34.541 34.541 ↑ 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.424..34.541 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.010 0.066 ↑ 7.6 5 1
→

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

  • Group Key: 2, company_device_group_1.id, 4, users_1.id, false
22. 0.006 0.056 ↑ 7.6 5 1
→

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

  • Hash Cond: (user_group.user_id = users_1.id)
23. 0.010 0.021 ↑ 14.2 5 1
→

Hash Join (cost=1.24..40.00 rows=71 width=8) (actual time=0.021..0.021 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.002 0.009 ↑ 1.0 7 1
→

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.007 0.007 ↑ 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.007 rows=7 loops=1)

  • Filter: (group_type = 2)
  • Rows Removed by Filter: 5
27. 0.011 0.029 ↓ 1.0 110 1
→

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

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

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

  • Filter: is_active
29. 0.013 0.013 ↓ 8.3 58 1
→

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

30. 22.932 31.562 ↑ 1.0 47,665 1
→

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2793kB
31. 8.630 8.630 ↑ 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.010..8.630 rows=47,665 loops=1)