explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HX5U

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

HashAggregate (cost=33,286.93..33,526.45 rows=23,952 width=34) (actual time=156.113..156.113 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. 28.120 156.089 ↓ 0.0 0 1

Hash Full Join (cost=1,733.95..32,927.65 rows=23,952 width=34) (actual time=156.089..156.089 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.484 94.510 ↓ 1.0 47,665 1

Append (cost=0.00..27,423.77 rows=47,124 width=17) (actual time=0.015..94.510 rows=47,665 loops=1)

4. 0.062 0.162 ↓ 3.0 303 1

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

5. 0.004 0.004 ↓ 3.0 3 1

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

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

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

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

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

  • Group Key: companies_1.id, u.id
8. 0.004 0.052 ↓ 5.0 5 1

Nested Loop (cost=0.30..19.52 rows=1 width=12) (actual time=0.028..0.052 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.002..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. 37.921 37.921 ↑ 1.0 10,794 1

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

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

Hash Join (cost=12,638.37..14,374.84 rows=36,183 width=8) (actual time=33.684..52.793 rows=36,502 loops=1)

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

Hash Join (cost=12,637.10..13,876.05 rows=36,183 width=8) (actual time=33.665..45.705 rows=36,502 loops=1)

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

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

17. 1.376 33.643 ↑ 1.0 10,794 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 508kB
18. 32.267 32.267 ↑ 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.300..32.267 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.003..0.004 rows=12 loops=1)

21. 0.006 0.079 ↑ 7.6 5 1

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

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

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

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

Hash Join (cost=1.24..40.00 rows=71 width=8) (actual time=0.032..0.035 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.006 0.017 ↑ 1.0 7 1

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

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

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

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

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

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

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

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

30. 24.248 33.459 ↑ 1.0 47,665 1

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

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