explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XA4Y

Settings
# exclusive inclusive rows x rows loops node
1. 1.966 75,766.932 ↓ 7,509.0 7,509 1

Sort (cost=26,728.62..26,728.62 rows=1 width=22) (actual time=75,766.606..75,766.932 rows=7,509 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: 779kB
2. 3.609 75,764.966 ↓ 7,509.0 7,509 1

GroupAggregate (cost=26,728.61..26,728.62 rows=1 width=22) (actual time=75,760.856..75,764.966 rows=7,509 loops=1)

  • Group Key: user_hubs_1.user_id, user_hubs.import_status, ((user_hubs.max_connect <> 0)), ((user_hubs.max_mentorship <> 0))
3. 9.921 75,761.357 ↓ 11,379.0 11,379 1

Sort (cost=26,728.61..26,728.61 rows=1 width=14) (actual time=75,760.850..75,761.357 rows=11,379 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: 918kB
4. 6,797.558 75,751.436 ↓ 11,379.0 11,379 1

Nested Loop (cost=9,116.10..26,728.61 rows=1 width=14) (actual time=14.875..75,751.436 rows=11,379 loops=1)

  • Join Filter: (user_hubs_1.user_id = user_hubs.user_id)
  • Rows Removed by Join Filter: 104553171
5. 25.808 35.178 ↓ 8,475.0 8,475 1

Index Scan using active_user_hub_signup_stage on user_hubs (cost=9,076.78..9,097.09 rows=1 width=20) (actual time=9.465..35.178 rows=8,475 loops=1)

  • Index Cond: (hub_id = 154)
  • Filter: (((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: 48
6.          

SubPlan (forIndex Scan)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.54..277.25 rows=318 width=4) (never executed)

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

Nested Loop (cost=0.14..14.41 rows=1 width=12) (never executed)

9. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (user_id = 72610)
  • Filter: approved
10. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on groups_users a (cost=2.40..262.03 rows=202 width=8) (never executed)

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

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

  • Index Cond: (group_id = groups.id)
13. 0.157 9.370 ↓ 9.0 9 1

Subquery Scan on b_1 (cost=39.17..8,799.28 rows=1 width=4) (actual time=3.805..9.370 rows=9 loops=1)

  • Filter: ((b_1.item ->> 'id'::text) = 'alumni'::text)
  • Rows Removed by Filter: 38
14. 0.213 9.213 ↑ 2.1 47 1

ProjectSet (cost=39.17..8,798.88 rows=100 width=36) (actual time=3.709..9.213 rows=47 loops=1)

15. 7.530 9.000 ↓ 12.0 12 1

Bitmap Heap Scan on user_hubs user_hubs_3 (cost=39.17..8,798.73 rows=1 width=7) (actual time=3.551..9.000 rows=12 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: (visibility = 'SELECT_USER_TYPES'::text)
  • Rows Removed by Filter: 9774
  • Heap Blocks: exact=4063
16. 1.470 1.470 ↓ 2.1 9,791 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..39.17 rows=4,721 width=0) (actual time=1.470..1.470 rows=9,791 loops=1)

  • Index Cond: (hub_id = 154)
17. 7,212.225 68,918.700 ↓ 4.0 12,338 8,475

Append (cost=39.32..17,620.63 rows=3,111 width=4) (actual time=1.548..8.132 rows=12,338 loops=8,475)

18. 46,146.375 54,528.150 ↓ 2.7 8,349 8,475

Bitmap Heap Scan on user_hubs user_hubs_1 (cost=39.32..8,801.25 rows=3,110 width=4) (actual time=1.548..6.434 rows=8,349 loops=8,475)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: ((user_type = 'alumni'::text) OR (user_type = 'incoming_alum'::text))
  • Rows Removed by Filter: 1437
  • Heap Blocks: exact=34433925
19. 8,381.775 8,381.775 ↓ 2.1 9,791 8,475

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..39.17 rows=4,721 width=0) (actual time=0.989..0.989 rows=9,791 loops=8,475)

  • Index Cond: (hub_id = 154)
20. 5,237.550 7,178.325 ↓ 3,989.0 3,989 8,475

Group (cost=8,810.05..8,810.05 rows=1 width=4) (actual time=0.013..0.847 rows=3,989 loops=8,475)

  • Group Key: user_hubs_2.user_id
21. 1,833.254 1,940.775 ↓ 3,989.0 3,989 8,475

Sort (cost=8,810.05..8,810.05 rows=1 width=4) (actual time=0.013..0.229 rows=3,989 loops=8,475)

  • Sort Key: user_hubs_2.user_id
  • Sort Method: quicksort Memory: 283kB
22. 2.242 107.521 ↓ 3,989.0 3,989 1

Nested Loop (cost=39.25..8,810.05 rows=1 width=4) (actual time=1.830..107.521 rows=3,989 loops=1)

23. 87.633 88.887 ↓ 4,098.0 4,098 1

Bitmap Heap Scan on user_hubs user_hubs_2 (cost=39.17..8,805.81 rows=1 width=4) (actual time=1.819..88.887 rows=4,098 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: (((profile_data ->> 'TFA-Connect-Job-Seeker-Status'::text) ~~* '%""Yes; I am currently looking for a new role and want to be contacted about career opportunities.""%'::text) OR ((profile_data ->> 'TFA-Connect-Job-Seeker-Status'::text) ~~* '%""No; I do not want to be contacted about career opportunities.""%'::text))
  • Rows Removed by Filter: 5688
  • Heap Blocks: exact=4063
24. 1.254 1.254 ↓ 2.1 9,791 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..39.17 rows=4,721 width=0) (actual time=1.254..1.254 rows=9,791 loops=1)

  • Index Cond: (hub_id = 154)
25. 16.392 16.392 ↑ 1.0 1 4,098

Index Scan using user_clusters_user_index on user_clusters (cost=0.09..4.09 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=4,098)

  • Index Cond: (user_id = user_hubs_2.user_id)
  • Filter: ((deleted_at IS NULL) AND (cluster_id = 154) AND (((hidden_fields ->> 'custom.TFA-Connect-Job-Seeker-Status'::text) IS NULL) OR ((hidden_fields ->> 'custom.TFA-Connect-Job-Seeker-Status'::text) ~~ 'false'::text)))
  • Rows Removed by Filter: 0