explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sh0f

Settings
# exclusive inclusive rows x rows loops node
1. 828.353 6,692.299 ↓ 1.6 2,266,705 1

Append (cost=1,231.35..433,231.18 rows=1,446,042 width=8) (actual time=11.373..6,692.299 rows=2,266,705 loops=1)

2. 1,128.116 2,905.046 ↓ 2.3 2,220,713 1

Hash Join (cost=1,231.35..65,438.04 rows=982,666 width=8) (actual time=11.371..2,905.046 rows=2,220,713 loops=1)

  • Hash Cond: (t.user_id = users.id)
3. 1,220.955 1,776.770 ↓ 2.0 2,220,713 1

Hash Semi Join (cost=1,208.36..51,424.57 rows=1,110,356 width=8) (actual time=11.204..1,776.770 rows=2,220,713 loops=1)

  • Hash Cond: (t.device_id = devices.id)
4. 544.647 544.647 ↑ 1.0 2,220,713 1

Seq Scan on tmp_user_device t (cost=0.00..32,034.13 rows=2,220,713 width=8) (actual time=0.013..544.647 rows=2,220,713 loops=1)

5. 4.220 11.168 ↑ 1.0 14,608 1

Hash (cost=1,025.76..1,025.76 rows=14,608 width=4) (actual time=11.168..11.168 rows=14,608 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 642kB
6. 6.948 6.948 ↑ 1.0 14,608 1

Seq Scan on devices (cost=0.00..1,025.76 rows=14,608 width=4) (actual time=0.007..6.948 rows=14,608 loops=1)

  • Filter: (activation_status < 9999)
  • Rows Removed by Filter: 5213
7. 0.046 0.160 ↑ 1.0 177 1

Hash (cost=20.77..20.77 rows=177 width=4) (actual time=0.160..0.160 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
8. 0.114 0.114 ↑ 1.0 177 1

Seq Scan on users (cost=0.00..20.77 rows=177 width=4) (actual time=0.007..0.114 rows=177 loops=1)

  • Filter: (is_active AND is_admin)
9. 330.385 2,958.342 ↑ 7.6 45,992 1

Hash Join (cost=90,478.59..252,976.21 rows=350,120 width=8) (actual time=2,628.493..2,958.342 rows=45,992 loops=1)

  • Hash Cond: ((cu.company_id = devices_1.company_id) AND (cu.user_id = t_1.user_id))
10. 0.220 0.220 ↑ 1.0 369 1

Seq Scan on company_user cu (cost=0.00..16.69 rows=369 width=8) (actual time=0.021..0.220 rows=369 loops=1)

  • Filter: (permission = ANY ('{8003,8004,8005}'::integer[]))
  • Rows Removed by Filter: 481
11. 741.171 2,627.737 ↓ 1.4 2,220,713 1

Hash (cost=57,936.74..57,936.74 rows=1,636,657 width=12) (actual time=2,627.737..2,627.737 rows=2,220,713 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 128 (originally 32) Memory Usage: 4062kB
12. 1,312.594 1,886.566 ↓ 1.4 2,220,713 1

Hash Join (cost=1,208.36..57,936.74 rows=1,636,657 width=12) (actual time=11.752..1,886.566 rows=2,220,713 loops=1)

  • Hash Cond: (t_1.device_id = devices_1.id)
13. 562.256 562.256 ↑ 1.0 2,220,713 1

Seq Scan on tmp_user_device t_1 (cost=0.00..32,034.13 rows=2,220,713 width=8) (actual time=0.010..562.256 rows=2,220,713 loops=1)

14. 4.338 11.716 ↑ 1.0 14,608 1

Hash (cost=1,025.76..1,025.76 rows=14,608 width=8) (actual time=11.716..11.716 rows=14,608 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 699kB
15. 7.378 7.378 ↑ 1.0 14,608 1

Seq Scan on devices devices_1 (cost=0.00..1,025.76 rows=14,608 width=8) (actual time=0.010..7.378 rows=14,608 loops=1)

  • Filter: (activation_status < 9999)
  • Rows Removed by Filter: 5213
16. 0.018 0.558 ↓ 0.0 0 1

Hash Join (cost=568.41..100,356.51 rows=113,256 width=8) (actual time=0.558..0.558 rows=0 loops=1)

  • Hash Cond: ((t_2.device_id = dg.device_id) AND (t_2.user_id = ug.user_id))
17. 0.016 0.016 ↑ 2,220,713.0 1 1

Seq Scan on tmp_user_device t_2 (cost=0.00..32,034.13 rows=2,220,713 width=8) (actual time=0.016..0.016 rows=1 loops=1)

18. 0.000 0.524 ↓ 0.0 0 1

Hash (cost=397.20..397.20 rows=11,414 width=8) (actual time=0.524..0.524 rows=0 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 128kB
19. 0.002 0.524 ↓ 0.0 0 1

Merge Join (cost=220.40..397.20 rows=11,414 width=8) (actual time=0.524..0.524 rows=0 loops=1)

  • Merge Cond: (dg.group_id = ug.group_id)
20. 0.261 0.518 ↑ 1,119.0 1 1

Sort (cost=77.86..80.65 rows=1,119 width=8) (actual time=0.518..0.518 rows=1 loops=1)

  • Sort Key: dg.group_id
  • Sort Method: quicksort Memory: 101kB
21. 0.257 0.257 ↑ 1.0 1,119 1

Seq Scan on device_group dg (cost=0.00..21.19 rows=1,119 width=8) (actual time=0.006..0.257 rows=1,119 loops=1)

22. 0.002 0.004 ↓ 0.0 0 1

Sort (cost=142.54..147.64 rows=2,040 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Sort Key: ug.group_id
  • Sort Method: quicksort Memory: 25kB
23. 0.002 0.002 ↓ 0.0 0 1

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