explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HmFJ

Settings
# exclusive inclusive rows x rows loops node
1. 2.007 75,532.927 ↓ 7,321.0 7,321 1

Sort (cost=17,549.38..17,549.38 rows=1 width=22) (actual time=75,532.608..75,532.927 rows=7,321 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: 764kB
2. 2.771 75,530.920 ↓ 7,321.0 7,321 1

GroupAggregate (cost=17,549.37..17,549.38 rows=1 width=22) (actual time=75,527.831..75,530.920 rows=7,321 loops=1)

  • Group Key: user_hubs_1.user_id, user_hubs.import_status, ((user_hubs.max_connect <> 0)), ((user_hubs.max_mentorship <> 0))
3. 8.357 75,528.149 ↓ 7,438.0 7,438 1

Sort (cost=17,549.37..17,549.37 rows=1 width=14) (actual time=75,527.824..75,528.149 rows=7,438 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: 541kB
4. 5,193.747 75,519.792 ↓ 7,438.0 7,438 1

Nested Loop (cost=8,758.57..17,549.37 rows=1 width=14) (actual time=98.150..75,519.792 rows=7,438 loops=1)

  • Join Filter: (user_hubs_1.user_id = user_hubs.user_id)
  • Rows Removed by Join Filter: 70525962
5. 22.091 22.091 ↓ 8,498.0 8,498 1

Index Scan using active_user_hub_signup_stage on user_hubs (cost=0.08..16.38 rows=1 width=20) (actual time=0.053..22.091 rows=8,498 loops=1)

  • Index Cond: (hub_id = 154)
  • Filter: ((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: 5
6. 4,860.856 70,303.954 ↓ 2.7 8,300 8,498

Append (cost=8,758.48..17,522.16 rows=3,094 width=4) (actual time=0.010..8.273 rows=8,300 loops=8,498)

7. 220.948 373.912 ↓ 145.0 145 8,498

Group (cost=8,758.48..8,758.48 rows=1 width=4) (actual time=0.010..0.044 rows=145 loops=8,498)

  • Group Key: user_hubs_1.user_id
8. 77.802 152.964 ↓ 145.0 145 8,498

Sort (cost=8,758.48..8,758.48 rows=1 width=4) (actual time=0.009..0.018 rows=145 loops=8,498)

  • Sort Key: user_hubs_1.user_id
  • Sort Method: quicksort Memory: 31kB
9. 0.209 75.162 ↓ 145.0 145 1

Nested Loop (cost=39.22..8,758.48 rows=1 width=4) (actual time=4.326..75.162 rows=145 loops=1)

10. 70.321 72.867 ↓ 149.0 149 1

Bitmap Heap Scan on user_hubs user_hubs_1 (cost=39.13..8,754.24 rows=1 width=4) (actual time=4.280..72.867 rows=149 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: ((profile_data ->> 'TFA-Connect-BETA-SB-Hiring-Campaign-Interest-School-Type'::text) ~~* '%"Traditional District"%'::text)
  • Rows Removed by Filter: 9607
  • Heap Blocks: exact=4022
11. 2.546 2.546 ↓ 2.1 9,756 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..39.13 rows=4,696 width=0) (actual time=2.546..2.546 rows=9,756 loops=1)

  • Index Cond: (hub_id = 154)
12. 2.086 2.086 ↑ 1.0 1 149

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

  • Index Cond: (user_id = user_hubs_1.user_id)
  • Filter: ((deleted_at IS NULL) AND (cluster_id = 154) AND (((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: 0
13. 55,050.044 65,069.186 ↓ 2.6 8,155 8,498

Bitmap Heap Scan on user_hubs user_hubs_2 (cost=39.29..8,754.40 rows=3,093 width=4) (actual time=1.754..7.657 rows=8,155 loops=8,498)

  • 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: 1601
  • Heap Blocks: exact=34178956
14. 10,019.142 10,019.142 ↓ 2.1 9,756 8,498

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..39.13 rows=4,696 width=0) (actual time=1.179..1.179 rows=9,756 loops=8,498)

  • Index Cond: (hub_id = 154)