explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vrsi

Settings
# exclusive inclusive rows x rows loops node
1. 4.230 163.863 ↓ 8,143.0 8,143 1

Sort (cost=122,049.85..122,049.85 rows=1 width=22) (actual time=163.273..163.863 rows=8,143 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: 829kB
2. 5.899 159.633 ↓ 8,143.0 8,143 1

GroupAggregate (cost=122,049.80..122,049.84 rows=1 width=22) (actual time=152.880..159.633 rows=8,143 loops=1)

  • Group Key: user_hubs_1.user_id, user_hubs.import_status, ((user_hubs.max_connect <> 0)), ((user_hubs.max_mentorship <> 0))
3. 6.066 153.734 ↓ 8,254.0 8,254 1

Sort (cost=122,049.80..122,049.81 rows=1 width=14) (actual time=152.871..153.734 rows=8,254 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: 771kB
4. 4.138 147.668 ↓ 8,254.0 8,254 1

Hash Join (cost=80,576.31..122,049.79 rows=1 width=14) (actual time=132.203..147.668 rows=8,254 loops=1)

  • Hash Cond: (user_hubs_1.user_id = user_hubs.user_id)
5. 1.080 128.197 ↑ 1.0 8,266 1

Append (cost=39,090.69..80,542.26 rows=8,342 width=4) (actual time=116.857..128.197 rows=8,266 loops=1)

6. 0.051 116.921 ↓ 136.0 136 1

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

  • Group Key: user_hubs_1.user_id
7. 0.103 116.870 ↓ 136.0 136 1

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

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

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

9. 21.697 23.910 ↓ 66.0 9,698 1

Bitmap Heap Scan on user_clusters (cost=275.29..37,839.77 rows=147 width=4) (actual time=3.426..23.910 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. 2.213 2.213 ↑ 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=2.213..2.213 rows=9,721 loops=1)

  • Index Cond: (cluster_id = 154)
11. 87.282 87.282 ↓ 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.009..0.009 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. 8.617 10.196 ↑ 1.0 8,130 1

Bitmap Heap Scan on user_hubs user_hubs_2 (cost=242.90..41,326.44 rows=8,341 width=4) (actual time=2.152..10.196 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.579 1.579 ↑ 1.3 9,713 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..240.81 rows=12,851 width=0) (actual time=1.579..1.579 rows=9,713 loops=1)

  • Index Cond: (hub_id = 154)
14. 3.384 15.333 ↓ 187.4 9,368 1

Hash (cost=41,485.00..41,485.00 rows=50 width=20) (actual time=15.333..15.333 rows=9,368 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 596kB
15. 10.425 11.949 ↓ 187.4 9,368 1

Bitmap Heap Scan on user_hubs (cost=240.82..41,485.00 rows=50 width=20) (actual time=2.097..11.949 rows=9,368 loops=1)

  • Recheck Cond: ((hub_id = 154) AND (deleted_at IS NULL))
  • Filter: (approved AND (import_status = 'joined'::text) 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: 345
  • Heap Blocks: exact=3306
16. 1.524 1.524 ↑ 1.3 9,713 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..240.81 rows=12,851 width=0) (actual time=1.524..1.524 rows=9,713 loops=1)

  • Index Cond: (hub_id = 154)