explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ql5Y

Settings
# exclusive inclusive rows x rows loops node
1. 4.488 11,226.133 ↓ 6,717.0 6,717 1

Sort (cost=144,055.77..144,055.77 rows=1 width=22) (actual time=11,225.549..11,226.133 rows=6,717 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: 717kB
2. 6.106 11,221.645 ↓ 6,717.0 6,717 1

GroupAggregate (cost=144,055.72..144,055.76 rows=1 width=22) (actual time=11,214.664..11,221.645 rows=6,717 loops=1)

  • Group Key: user_hubs_1.user_id, user_hubs.import_status, ((user_hubs.max_connect <> 0)), ((user_hubs.max_mentorship <> 0))
3. 17.802 11,215.539 ↓ 10,095.0 10,095 1

Sort (cost=144,055.72..144,055.73 rows=1 width=14) (actual time=11,214.652..11,215.539 rows=10,095 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: 858kB
4. 7,258.185 11,197.737 ↓ 10,095.0 10,095 1

Nested Loop (cost=38,117.67..144,055.71 rows=1 width=14) (actual time=7.955..11,197.737 rows=10,095 loops=1)

  • Join Filter: (user_hubs_1.user_id = user_hubs.user_id)
  • Rows Removed by Join Filter: 46689739
5. 2.552 148.776 ↓ 1.5 10,956 1

Append (cost=217.21..68,632.42 rows=7,521 width=4) (actual time=3.727..148.776 rows=10,956 loops=1)

6. 25.348 28.001 ↑ 1.0 7,489 1

Bitmap Heap Scan on user_hubs user_hubs_1 (cost=217.21..37,253.70 rows=7,520 width=4) (actual time=3.726..28.001 rows=7,489 loops=1)

  • 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: 1245
  • Heap Blocks: exact=5445
7. 2.653 2.653 ↑ 1.3 8,734 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..215.33 rows=11,587 width=0) (actual time=2.653..2.653 rows=8,734 loops=1)

  • Index Cond: (hub_id = 154)
8. 2.780 118.223 ↓ 3,467.0 3,467 1

Group (cost=31,265.89..31,265.90 rows=1 width=4) (actual time=114.426..118.223 rows=3,467 loops=1)

  • Group Key: user_hubs_2.user_id
9. 3.063 115.443 ↓ 3,467.0 3,467 1

Sort (cost=31,265.89..31,265.90 rows=1 width=4) (actual time=114.423..115.443 rows=3,467 loops=1)

  • Sort Key: user_hubs_2.user_id
  • Sort Method: quicksort Memory: 259kB
10. 5.957 112.380 ↓ 3,467.0 3,467 1

Nested Loop (cost=209.41..31,265.88 rows=1 width=4) (actual time=2.984..112.380 rows=3,467 loops=1)

11. 19.433 21.563 ↓ 75.8 8,486 1

Bitmap Heap Scan on user_clusters (cost=208.98..30,313.01 rows=112 width=4) (actual time=2.912..21.563 rows=8,486 loops=1)

  • Recheck Cond: ((cluster_id = 154) AND (deleted_at IS NULL))
  • Filter: (((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: 256
  • Heap Blocks: exact=4505
12. 2.130 2.130 ↑ 1.3 8,742 1

Bitmap Index Scan on user_clusters_user_id_hub_id_unique (cost=0.00..208.95 rows=11,270 width=0) (actual time=2.130..2.130 rows=8,742 loops=1)

  • Index Cond: (cluster_id = 154)
13. 84.860 84.860 ↓ 0.0 0 8,486

Index Scan using user_hubs_user_type_index on user_hubs user_hubs_2 (cost=0.43..8.46 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=8,486)

  • Index Cond: (user_id = user_clusters.user_id)
  • Filter: ((deleted_at IS NULL) AND (hub_id = 154) AND (((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: 1
14. 3,759.272 3,790.776 ↓ 2,131.0 4,262 10,956

Materialize (cost=37,900.46..75,197.66 rows=2 width=20) (actual time=0.001..0.346 rows=4,262 loops=10,956)

15. 14.781 31.504 ↓ 3,783.5 7,567 1

Bitmap Heap Scan on user_hubs (cost=37,900.46..75,197.65 rows=2 width=20) (actual time=4.207..31.504 rows=7,567 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: 1167
  • Heap Blocks: exact=5445
16. 2.882 2.882 ↑ 1.3 8,734 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..215.33 rows=11,587 width=0) (actual time=2.882..2.882 rows=8,734 loops=1)

  • Index Cond: (hub_id = 154)
17.          

SubPlan (forBitmap Heap Scan)

18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.77..459.42 rows=336 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.75 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.43 rows=2 width=8) (never executed)

  • Index Cond: (user_id = 72610)
  • 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.07..426.56 rows=207 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.02 rows=213 width=0) (never executed)

  • Index Cond: (group_id = groups.id)
24. 0.541 13.841 ↓ 8.0 8 1

Subquery Scan on b_1 (cost=215.33..37,224.86 rows=1 width=4) (actual time=4.083..13.841 rows=8 loops=1)

  • Filter: ((b_1.item ->> 'id'::text) = 'alumni'::text)
  • Rows Removed by Filter: 35
25. 0.621 13.300 ↑ 2.3 43 1

ProjectSet (cost=215.33..37,223.36 rows=100 width=36) (actual time=4.074..13.300 rows=43 loops=1)

26. 10.033 12.679 ↓ 11.0 11 1

Bitmap Heap Scan on user_hubs user_hubs_3 (cost=215.33..37,222.86 rows=1 width=7) (actual time=4.031..12.679 rows=11 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: (visibility = 'SELECT_USER_TYPES'::text)
  • Rows Removed by Filter: 8723
  • Heap Blocks: exact=5445
27. 2.646 2.646 ↑ 1.3 8,734 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..215.33 rows=11,587 width=0) (actual time=2.646..2.646 rows=8,734 loops=1)

  • Index Cond: (hub_id = 154)