explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VCy

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 55,254.802 ↑ 1.0 1 1

Nested Loop Left Join (cost=910,964.44..988,487.28 rows=1 width=998) (actual time=55,254.800..55,254.802 rows=1 loops=1)

  • Join Filter: (user_clusters.cluster_id = ch.cluster_id)
2. 0.009 55,254.730 ↑ 1.0 1 1

Nested Loop (cost=910,964.01..988,478.81 rows=1 width=468) (actual time=55,254.729..55,254.730 rows=1 loops=1)

3. 336.979 55,254.684 ↑ 1.0 1 1

Merge Left Join (cost=910,963.58..988,470.36 rows=1 width=369) (actual time=55,254.683..55,254.684 rows=1 loops=1)

  • Merge Cond: ((user_hubs.user_id = user_schools.user_id) AND (ch.cluster_id = user_schools.cluster_id))
4. 0.029 0.252 ↑ 1.0 1 1

Sort (cost=15.22..15.22 rows=1 width=337) (actual time=0.252..0.252 rows=1 loops=1)

  • Sort Key: user_hubs.user_id, ch.cluster_id
  • Sort Method: quicksort Memory: 25kB
5. 0.100 0.223 ↑ 1.0 1 1

Hash Right Join (cost=8.46..15.21 rows=1 width=337) (actual time=0.188..0.223 rows=1 loops=1)

  • Hash Cond: (ch.hub_id = user_hubs.hub_id)
6. 0.077 0.077 ↓ 1.0 309 1

Seq Scan on cluster_hubs ch (cost=0.00..5.96 rows=296 width=8) (actual time=0.016..0.077 rows=309 loops=1)

7. 0.011 0.046 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=333) (actual time=0.046..0.046 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.035 0.035 ↑ 1.0 1 1

Index Scan using user_hubs_pkey on user_hubs (cost=0.43..8.45 rows=1 width=333) (actual time=0.034..0.035 rows=1 loops=1)

  • Index Cond: (id = 3844228)
  • Filter: (deleted_at IS NULL)
9. 43,404.261 54,917.453 ↓ 1.2 1,715,649 1

GroupAggregate (cost=910,948.36..966,619.64 rows=1,455,699 width=48) (actual time=10,560.977..54,917.453 rows=1,715,649 loops=1)

  • Group Key: user_schools.user_id, user_schools.cluster_id
10. 7,648.933 11,513.192 ↑ 1.5 2,041,263 1

Sort (cost=910,948.36..918,443.37 rows=2,998,003 width=147) (actual time=10,560.853..11,513.192 rows=2,041,263 loops=1)

  • Sort Key: user_schools.user_id, user_schools.cluster_id
  • Sort Method: external merge Disk: 401488kB
11. 1,611.677 3,864.259 ↑ 1.0 2,964,505 1

Hash Join (cost=1,441.03..137,552.64 rows=2,998,003 width=147) (actual time=19.369..3,864.259 rows=2,964,505 loops=1)

  • Hash Cond: (user_schools.school_id = sc.id)
12. 2,233.458 2,233.458 ↑ 1.0 2,996,709 1

Seq Scan on user_schools (cost=0.00..128,240.36 rows=2,998,003 width=119) (actual time=0.063..2,233.458 rows=2,996,709 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 378244
13. 9.807 19.124 ↓ 1.0 22,359 1

Hash (cost=1,161.57..1,161.57 rows=22,357 width=32) (actual time=19.124..19.124 rows=22,359 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1697kB
14. 9.317 9.317 ↓ 1.0 22,359 1

Seq Scan on schools sc (cost=0.00..1,161.57 rows=22,357 width=32) (actual time=0.006..9.317 rows=22,359 loops=1)

15. 0.037 0.037 ↑ 1.0 1 1

Index Scan using users_pkey on users (cost=0.43..8.45 rows=1 width=99) (actual time=0.037..0.037 rows=1 loops=1)

  • Index Cond: (id = user_hubs.user_id)
  • Filter: (deleted_at IS NULL)
16. 0.026 0.026 ↑ 1.0 1 1

Index Scan using user_clusters_user_index on user_clusters (cost=0.43..8.45 rows=1 width=593) (actual time=0.026..0.026 rows=1 loops=1)

  • Index Cond: (user_id = user_hubs.user_id)
  • Filter: (deleted_at IS NULL)
Planning time : 8.774 ms
Execution time : 55,288.243 ms