explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M6us

Settings
# exclusive inclusive rows x rows loops node
1. 3.523 8,661.273 ↓ 7,259.0 7,259 1

Sort (cost=164,375.06..164,375.07 rows=1 width=22) (actual time=8,660.846..8,661.273 rows=7,259 loops=1)

  • Sort Key: ((count(user_hubs_1.user_id))::integer) DESC, ((user_hubs.max_connect <> 0)) DESC, ((user_hubs.max_mentorship <> 0)) DESC
  • Sort Method: quicksort Memory: 760kB
2. 4.818 8,657.750 ↓ 7,259.0 7,259 1

GroupAggregate (cost=164,375.02..164,375.05 rows=1 width=22) (actual time=8,652.206..8,657.750 rows=7,259 loops=1)

  • Group Key: user_hubs_1.user_id, user_hubs.import_status, ((user_hubs.max_connect <> 0)), ((user_hubs.max_mentorship <> 0))
3. 7.974 8,652.932 ↓ 7,367.0 7,367 1

Sort (cost=164,375.02..164,375.02 rows=1 width=14) (actual time=8,652.197..8,652.932 rows=7,367 loops=1)

  • Sort Key: user_hubs_1.user_id, ((user_hubs.max_connect <> 0)) DESC, ((user_hubs.max_mentorship <> 0)) DESC
  • Sort Method: quicksort Memory: 538kB
4. 5,626.385 8,644.958 ↓ 7,367.0 7,367 1

Nested Loop (cost=81,121.98..164,375.01 rows=1 width=14) (actual time=128.096..8,644.958 rows=7,367 loops=1)

  • Join Filter: (user_hubs_1.user_id = user_hubs.user_id)
  • Rows Removed by Join Filter: 40787334
5. 1.349 125.473 ↑ 1.0 8,266 1

Append (cost=39,090.69..80,568.79 rows=8,348 width=4) (actual time=108.096..125.473 rows=8,266 loops=1)

6. 0.074 108.192 ↓ 136.0 136 1

Group (cost=39,090.69..39,090.70 rows=1 width=4) (actual time=108.095..108.192 rows=136 loops=1)

  • Group Key: user_hubs_1.user_id
7. 0.118 108.118 ↓ 136.0 136 1

Sort (cost=39,090.69..39,090.69 rows=1 width=4) (actual time=108.093..108.118 rows=136 loops=1)

  • Sort Key: user_hubs_1.user_id
  • Sort Method: quicksort Memory: 31kB
8. 8.613 108.000 ↓ 136.0 136 1

Nested Loop (cost=275.72..39,090.68 rows=1 width=4) (actual time=6.737..108.000 rows=136 loops=1)

9. 19.969 21.803 ↓ 66.0 9,698 1

Bitmap Heap Scan on user_clusters (cost=275.29..37,839.77 rows=147 width=4) (actual time=2.661..21.803 rows=9,698 loops=1)

  • Recheck Cond: ((cluster_id = 154) AND (deleted_at IS NULL))
  • Filter: (((hidden_fields ->> 'custom.TFA-Connect-BETA-SB-Hiring-Campaign-Interest-School-Type'::text) IS NULL) OR ((hidden_fields ->> 'custom.TFA-Connect-BETA-SB-Hiring-Campaign-Interest-School-Type'::text) ~~ 'false'::text))
  • Rows Removed by Filter: 23
  • Heap Blocks: exact=5450
10. 1.834 1.834 ↑ 1.5 9,721 1

Bitmap Index Scan on user_clusters_user_id_hub_id_unique (cost=0.00..275.25 rows=14,776 width=0) (actual time=1.834..1.834 rows=9,721 loops=1)

  • Index Cond: (cluster_id = 154)
11. 77.584 77.584 ↓ 0.0 0 9,698

Index Scan using user_hubs_user_id_hub_id_unique on user_hubs user_hubs_1 (cost=0.43..8.46 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=9,698)

  • Index Cond: ((hub_id = 154) AND (user_id = user_clusters.user_id))
  • Filter: ((profile_data ->> 'TFA-Connect-BETA-SB-Hiring-Campaign-Interest-School-Type'::text) ~~* '%"Traditional District"%'::text)
  • Rows Removed by Filter: 1
12. 14.577 15.932 ↑ 1.0 8,130 1

Bitmap Heap Scan on user_hubs user_hubs_2 (cost=242.97..41,352.87 rows=8,347 width=4) (actual time=1.855..15.932 rows=8,130 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: ((user_type ~~ 'corps_member'::text) OR (user_type ~~ 'alumni'::text))
  • Rows Removed by Filter: 1583
  • Heap Blocks: exact=3306
13. 1.355 1.355 ↑ 1.3 9,713 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..240.88 rows=12,860 width=0) (actual time=1.355..1.355 rows=9,713 loops=1)

  • Index Cond: (hub_id = 154)
14. 2,874.780 2,893.100 ↓ 1,645.0 4,935 8,266

Materialize (cost=42,031.29..83,430.56 rows=3 width=20) (actual time=0.000..0.350 rows=4,935 loops=8,266)

15. 9.388 18.320 ↓ 2,808.3 8,425 1

Bitmap Heap Scan on user_hubs (cost=42,031.29..83,430.55 rows=3 width=20) (actual time=1.703..18.320 rows=8,425 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: ((signup_stage IS NULL) AND approved AND (import_status = 'joined'::text) AND ((visibility = 'PUBLIC'::text) OR ((visibility = 'GROUPS_PROGRAMS'::text) AND (hashed SubPlan 1)) OR (hashed SubPlan 2)) AND ((user_type ~~ 'corps_member'::text) OR (user_type ~~ 'alumni'::text) OR (user_type ~~ 'facultystaff'::text) OR (user_type ~~ 'employer'::text) OR (user_type ~~ 'tfa_guest'::text) OR (user_type ~~ 'incoming_alum'::text)))
  • Rows Removed by Filter: 1288
  • Heap Blocks: exact=3306
16. 1.242 1.242 ↑ 1.3 9,713 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..240.88 rows=12,860 width=0) (actual time=1.242..1.242 rows=9,713 loops=1)

  • Index Cond: (hub_id = 154)
17.          

SubPlan (forBitmap Heap Scan)

18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.74..469.05 rows=286 width=4) (never executed)

  • Join Filter: ((a.user_id <> b.user_id) AND (b.group_id = a.group_id))
19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..29.50 rows=1 width=12) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using groups_users_user_index on groups_users b (cost=0.42..12.21 rows=2 width=8) (never executed)

  • Index Cond: (user_id = 73206)
  • Filter: approved
21. 0.000 0.000 ↓ 0.0 0

Index Scan using groups_pkey on groups (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = b.group_id)
  • Filter: (hub_id = 154)
22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on groups_users a (cost=6.05..436.47 rows=205 width=8) (never executed)

  • Recheck Cond: (group_id = groups.id)
  • Filter: approved
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on groups_users_group_id_user_id_key (cost=0.00..6.00 rows=210 width=0) (never executed)

  • Index Cond: (group_id = groups.id)
24. 0.245 7.690 ↓ 9.0 9 1

Subquery Scan on b_1 (cost=240.88..41,320.64 rows=1 width=4) (actual time=3.027..7.690 rows=9 loops=1)

  • Filter: ((b_1.item ->> 'id'::text) = 'alumni'::text)
  • Rows Removed by Filter: 38
25. 0.248 7.445 ↑ 2.1 47 1

ProjectSet (cost=240.88..41,319.14 rows=100 width=36) (actual time=2.882..7.445 rows=47 loops=1)

26. 5.899 7.197 ↓ 12.0 12 1

Bitmap Heap Scan on user_hubs user_hubs_3 (cost=240.88..41,318.64 rows=1 width=7) (actual time=2.693..7.197 rows=12 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: (visibility = 'SELECT_USER_TYPES'::text)
  • Rows Removed by Filter: 9701
  • Heap Blocks: exact=3306
27. 1.298 1.298 ↑ 1.3 9,713 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..240.88 rows=12,860 width=0) (actual time=1.298..1.298 rows=9,713 loops=1)

  • Index Cond: (hub_id = 154)