explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mcvx

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 158.633 ↓ 0.0 0 1

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

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

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

4. 0.116 0.229 ↓ 3.0 303 1

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

5. 0.008 0.008 ↓ 3.0 3 1

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

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

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

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

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

  • Group Key: companies_1.id, u.id
8. 0.007 0.057 ↓ 5.0 5 1

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

9. 0.016 0.036 ↓ 7.0 7 1

Nested Loop (cost=0.15..18.57 rows=1 width=12) (actual time=0.023..0.036 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.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. 44.473 44.473 ↑ 1.0 10,794 1

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

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

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

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

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

  • Hash Cond: (device_group.device_id = devices_1.id)
16. 2.885 2.885 ↑ 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.885 rows=36,518 loops=1)

17. 1.380 38.784 ↑ 1.0 10,794 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 508kB
18. 37.404 37.404 ↑ 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.476..37.404 rows=10,794 loops=1)

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

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

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

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

21. 0.006 0.059 ↑ 7.6 5 1

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

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

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

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

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

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

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

25. 0.003 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.006 0.006 ↑ 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.004..0.006 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.005 0.005 ↓ 8.3 58 1

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

30. 18.510 25.436 ↑ 1.0 47,665 1

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

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