explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rMog

Settings
# exclusive inclusive rows x rows loops node
1. 1.828 23,771.947 ↑ 1.0 1 1

Aggregate (cost=504,358,733.36..504,358,733.37 rows=1 width=8) (actual time=23,771.947..23,771.947 rows=1 loops=1)

2. 404.275 23,770.119 ↓ 288.0 8,064 1

Index Scan using user_hubs_hub_import_batch_idx on user_hubs (cost=202,968.90..504,358,733.29 rows=28 width=4) (actual time=14.810..23,770.119 rows=8,064 loops=1)

  • Index Cond: (hub_id = 92)
  • Filter: ((signup_stage IS NULL) AND approved AND (import_status = 'joined'::text) AND ((user_type ~~ 'alumni'::text) OR (user_type ~~ 'facultystaff'::text) OR (user_type ~~ 'friends'::text)) AND ((visibility = 'PUBLIC'::text) OR (SubPlan 1) OR (hashed SubPlan 2)))
  • Rows Removed by Filter: 128590
3.          

SubPlan (forIndex Scan)

4. 113.168 23,238.116 ↑ 416.2 1,533 517

Result (cost=210.86..5,783.45 rows=638,013 width=4) (actual time=0.109..44.948 rows=1,533 loops=517)

  • One-Time Filter: (user_hubs.visibility = 'GROUPS_PROGRAMS'::text)
5. 13,505.911 23,124.948 ↑ 372.0 1,715 462

Hash Join (cost=210.86..5,783.45 rows=638,013 width=4) (actual time=0.121..50.054 rows=1,715 loops=462)

  • Hash Cond: (groups_users.group_id = groups_users_1.group_id)
6. 9,618.840 9,618.840 ↑ 1.0 188,914 462

Seq Scan on groups_users (cost=0.00..4,933.14 rows=188,914 width=8) (actual time=0.003..20.820 rows=188,914 loops=462)

7. 0.034 0.197 ↓ 1.6 89 1

Hash (cost=210.17..210.17 rows=55 width=4) (actual time=0.197..0.197 rows=89 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
8. 0.101 0.163 ↓ 1.6 89 1

Bitmap Heap Scan on groups_users groups_users_1 (cost=4.86..210.17 rows=55 width=4) (actual time=0.075..0.163 rows=89 loops=1)

  • Recheck Cond: (user_id = 84375)
  • Filter: approved
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=48
9. 0.062 0.062 ↓ 1.6 90 1

Bitmap Index Scan on groups_users_user_index (cost=0.00..4.85 rows=57 width=0) (actual time=0.062..0.062 rows=90 loops=1)

  • Index Cond: (user_id = 84375)
10. 6.142 127.728 ↓ 6.0 6 1

Gather (cost=3,456.12..202,968.47 rows=1 width=4) (actual time=90.742..127.728 rows=6 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 0.031 121.586 ↓ 2.0 2 3

Subquery Scan on b (cost=2,456.12..201,968.37 rows=1 width=4) (actual time=93.340..121.586 rows=2 loops=3)

  • Filter: ((b.item ->> 'id'::text) = 'alumni'::text)
  • Rows Removed by Filter: 8
12. 0.109 121.555 ↑ 20.0 10 3

ProjectSet (cost=2,456.12..201,968.31 rows=200 width=36) (actual time=93.327..121.555 rows=10 loops=3)

13. 100.042 121.446 ↑ 1.0 2 3

Parallel Bitmap Heap Scan on user_hubs user_hubs_1 (cost=2,456.12..201,967.29 rows=2 width=7) (actual time=93.249..121.446 rows=2 loops=3)

  • Recheck Cond: ((hub_id = 92) AND (deleted_at IS NULL))
  • Filter: (visibility = 'SELECT_USER_TYPES'::text)
  • Rows Removed by Filter: 45549
  • Heap Blocks: exact=11328
14. 21.404 21.404 ↓ 1.0 136,654 1

Bitmap Index Scan on user_hubs_user_id_hub_id_unique (cost=0.00..2,456.11 rows=132,758 width=0) (actual time=21.404..21.404 rows=136,654 loops=1)

  • Index Cond: (hub_id = 92)