explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4GbT

Settings
# exclusive inclusive rows x rows loops node
1. 0.066 53,801.112 ↑ 1.0 1 1

Nested Loop Left Join (cost=911,017.27..988,545.11 rows=1 width=998) (actual time=53,801.110..53,801.112 rows=1 loops=1)

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

Nested Loop (cost=911,016.84..988,536.64 rows=1 width=468) (actual time=53,801.019..53,801.020 rows=1 loops=1)

3. 330.989 53,800.971 ↑ 1.0 1 1

Merge Left Join (cost=911,016.41..988,528.19 rows=1 width=369) (actual time=53,800.970..53,800.971 rows=1 loops=1)

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

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

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

Hash Right Join (cost=8.46..15.21 rows=1 width=337) (actual time=0.195..0.232 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.015..0.077 rows=309 loops=1)

7. 0.013 0.049 ↑ 1.0 1 1

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

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

  • Index Cond: (id = 3844228)
  • Filter: (deleted_at IS NULL)
9. 43,020.640 53,469.731 ↓ 1.2 1,715,649 1

GroupAggregate (cost=911,001.20..966,676.06 rows=1,455,793 width=48) (actual time=9,598.206..53,469.731 rows=1,715,649 loops=1)

  • Group Key: user_schools.user_id, user_schools.cluster_id
10. 6,746.447 10,449.091 ↑ 1.5 2,041,263 1

Sort (cost=911,001.20..918,496.69 rows=2,998,196 width=147) (actual time=9,598.099..10,449.091 rows=2,041,263 loops=1)

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

Hash Join (cost=1,441.03..137,555.32 rows=2,998,196 width=147) (actual time=19.946..3,702.644 rows=2,964,505 loops=1)

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

Seq Scan on user_schools (cost=0.00..128,242.53 rows=2,998,196 width=119) (actual time=0.009..2,121.439 rows=2,996,709 loops=1)

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

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

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

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

15. 0.040 0.040 ↑ 1.0 1 1

Index Scan using users_pkey on users (cost=0.43..8.45 rows=1 width=99) (actual time=0.040..0.040 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 : 9.225 ms
Execution time : 53,832.892 ms