explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C6z

Settings
# exclusive inclusive rows x rows loops node
1. 2,526.431 8,054.216 ↓ 158,622.4 2,220,713 1

HashAggregate (cost=1,843.76..1,843.90 rows=14 width=8) (actual time=6,984.186..8,054.216 rows=2,220,713 loops=1)

  • Group Key: t.obj_id, d.device_id
2.          

CTE all_users

3. 2.568 7.167 ↑ 369.0 1 1

HashAggregate (cost=185.24..188.93 rows=369 width=12) (actual time=7.167..7.167 rows=1 loops=1)

  • Group Key: perm_cache_relations.owner_type, perm_cache_relations.owner_id, perm_cache_relations.obj_id
4. 2.401 4.599 ↓ 17.4 6,408 1

Append (cost=0.29..182.47 rows=369 width=12) (actual time=0.019..4.599 rows=6,408 loops=1)

5. 0.246 0.246 ↑ 1.0 337 1

Index Scan using ix_perm_cache_obj_type on perm_cache_relations (cost=0.29..58.86 rows=337 width=12) (actual time=0.019..0.246 rows=337 loops=1)

  • Index Cond: (obj_type = 4)
6. 1.952 1.952 ↓ 189.7 6,071 1

Seq Scan on tmp_relation_diff (cost=0.00..119.93 rows=32 width=12) (actual time=0.015..1.952 rows=6,071 loops=1)

  • Filter: (obj_type = 4)
7.          

CTE all_devices

8. 12.412 23.599 ↑ 1.0 15,645 1

HashAggregate (cost=735.05..891.82 rows=15,677 width=12) (actual time=16.920..23.599 rows=15,645 loops=1)

  • Group Key: perm_cache_relations_1.owner_type, perm_cache_relations_1.owner_id, perm_cache_relations_1.obj_id
9. 5.621 11.187 ↑ 1.0 15,645 1

Append (cost=0.00..617.47 rows=15,677 width=12) (actual time=0.011..11.187 rows=15,645 loops=1)

10. 5.080 5.080 ↑ 1.0 15,645 1

Seq Scan on perm_cache_relations perm_cache_relations_1 (cost=0.00..340.77 rows=15,645 width=12) (actual time=0.011..5.080 rows=15,645 loops=1)

  • Filter: (obj_type = 3)
  • Rows Removed by Filter: 337
11. 0.486 0.486 ↓ 0.0 0 1

Seq Scan on tmp_relation_diff tmp_relation_diff_1 (cost=0.00..119.93 rows=32 width=12) (actual time=0.486..0.486 rows=0 loops=1)

  • Filter: (obj_type = 3)
  • Rows Removed by Filter: 6071
12. 839.698 5,527.785 ↓ 158,622.4 2,220,713 1

Append (cost=630.28..762.94 rows=14 width=8) (actual time=2,631.822..5,527.785 rows=2,220,713 loops=1)

13. 984.896 4,680.447 ↓ 170,824.1 2,220,713 1

Unique (cost=630.28..630.38 rows=13 width=8) (actual time=2,631.820..4,680.447 rows=2,220,713 loops=1)

14. 2,950.244 3,695.551 ↓ 170,824.1 2,220,713 1

Sort (cost=630.28..630.31 rows=13 width=8) (actual time=2,631.818..3,695.551 rows=2,220,713 loops=1)

  • Sort Key: t.obj_id, d.device_id
  • Sort Method: external merge Disk: 39080kB
15. 709.051 745.307 ↓ 170,824.1 2,220,713 1

Hash Join (cost=120.41..630.04 rows=13 width=8) (actual time=20.673..745.307 rows=2,220,713 loops=1)

  • Hash Cond: ((d.owner_type = t.owner_type) AND (d.owner_id = t.owner_id))
16. 32.517 32.517 ↑ 1.0 15,645 1

CTE Scan on all_devices d (cost=0.00..313.54 rows=15,677 width=12) (actual time=16.923..32.517 rows=15,645 loops=1)

17. 1.863 3.739 ↓ 189.7 6,071 1

Hash (cost=119.93..119.93 rows=32 width=12) (actual time=3.739..3.739 rows=6,071 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 325kB
18. 1.876 1.876 ↓ 189.7 6,071 1

Seq Scan on tmp_relation_diff t (cost=0.00..119.93 rows=32 width=12) (actual time=0.004..1.876 rows=6,071 loops=1)

  • Filter: (obj_type = 4)
19. 0.000 7.640 ↓ 0.0 0 1

Unique (cost=132.42..132.43 rows=1 width=8) (actual time=7.640..7.640 rows=0 loops=1)

20. 0.012 7.640 ↓ 0.0 0 1

Sort (cost=132.42..132.42 rows=1 width=8) (actual time=7.640..7.640 rows=0 loops=1)

  • Sort Key: u.user_id, t_1.obj_id
  • Sort Method: quicksort Memory: 25kB
21. 0.010 7.628 ↓ 0.0 0 1

Hash Join (cost=120.41..132.41 rows=1 width=8) (actual time=7.628..7.628 rows=0 loops=1)

  • Hash Cond: ((u.owner_type = t_1.owner_type) AND (u.owner_id = t_1.owner_id))
22. 7.170 7.170 ↑ 369.0 1 1

CTE Scan on all_users u (cost=0.00..7.38 rows=369 width=12) (actual time=7.170..7.170 rows=1 loops=1)

23. 0.000 0.448 ↓ 0.0 0 1

Hash (cost=119.93..119.93 rows=32 width=12) (actual time=0.448..0.448 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
24. 0.448 0.448 ↓ 0.0 0 1

Seq Scan on tmp_relation_diff t_1 (cost=0.00..119.93 rows=32 width=12) (actual time=0.448..0.448 rows=0 loops=1)

  • Filter: (obj_type = 3)
  • Rows Removed by Filter: 6071