explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TOMI

Settings
# exclusive inclusive rows x rows loops node
1. 1.111 162,471.601 ↓ 1,134.5 2,269 1

Sort (cost=250,580,766.63..250,580,766.63 rows=2 width=22) (actual time=162,471.471..162,471.601 rows=2,269 loops=1)

  • Sort Key: ((count(user_help_topics.user_id))::integer) DESC, ((user_hubs.max_connect <> 0)) DESC, ((user_hubs.max_mentorship <> 0)) DESC
  • Sort Method: quicksort Memory: 274kB
2. 1.389 162,470.490 ↓ 1,134.5 2,269 1

GroupAggregate (cost=250,580,766.55..250,580,766.62 rows=2 width=22) (actual time=162,468.921..162,470.490 rows=2,269 loops=1)

  • Group Key: user_help_topics.user_id, user_hubs.import_status, ((user_hubs.max_connect <> 0)), ((user_hubs.max_mentorship <> 0))
3. 3.917 162,469.101 ↓ 1,438.5 2,877 1

Sort (cost=250,580,766.55..250,580,766.56 rows=2 width=14) (actual time=162,468.912..162,469.101 rows=2,877 loops=1)

  • Sort Key: user_help_topics.user_id, ((user_hubs.max_connect <> 0)) DESC, ((user_hubs.max_mentorship <> 0)) DESC
  • Sort Method: quicksort Memory: 231kB
4. 105,755.059 162,465.184 ↓ 1,438.5 2,877 1

Nested Loop (cost=226,400.98..250,580,766.54 rows=2 width=14) (actual time=2,215.490..162,465.184 rows=2,877 loops=1)

  • Join Filter: (user_help_topics.user_id = user_hubs.user_id)
  • Rows Removed by Join Filter: 842179643
5. 51.819 219.933 ↓ 2.1 371,646 1

Append (cost=8,283.45..247,714.84 rows=180,727 width=4) (actual time=58.273..219.933 rows=371,646 loops=1)

6. 0.484 59.750 ↓ 38.8 1,240 1

Group (cost=8,283.45..8,286.58 rows=32 width=4) (actual time=58.272..59.750 rows=1,240 loops=1)

  • Group Key: user_help_topics.user_id
7. 0.000 59.266 ↓ 47.7 1,240 1

Gather Merge (cost=8,283.45..8,286.51 rows=26 width=4) (actual time=58.270..59.266 rows=1,240 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 0.717 157.539 ↓ 31.8 413 3

Group (cost=7,283.42..7,283.49 rows=13 width=4) (actual time=52.210..52.513 rows=413 loops=3)

  • Group Key: user_help_topics.user_id
9. 1.698 156.822 ↓ 31.8 413 3

Sort (cost=7,283.42..7,283.45 rows=13 width=4) (actual time=52.207..52.274 rows=413 loops=3)

  • Sort Key: user_help_topics.user_id
  • Sort Method: quicksort Memory: 45kB
  • Worker 0: Sort Method: quicksort Memory: 44kB
  • Worker 1: Sort Method: quicksort Memory: 43kB
10. 155.124 155.124 ↓ 31.8 413 3

Parallel Seq Scan on user_help_topics (cost=0.00..7,283.18 rows=13 width=4) (actual time=0.265..51.708 rows=413 loops=3)

  • Filter: ((help_topic_id = 297) AND (hub_id = 39))
  • Rows Removed by Filter: 166050
11. 0.000 108.364 ↓ 2.0 370,406 1

Gather (cost=1,533.57..236,717.36 rows=180,695 width=4) (actual time=8.405..108.364 rows=370,406 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 757.888 764.388 ↓ 1.6 123,469 3

Parallel Seq Scan on user_hubs user_hubs_1 (cost=533.57..217,647.86 rows=75,290 width=4) (actual time=8.526..764.388 rows=123,469 loops=3)

  • Filter: ((deleted_at IS NULL) AND (NOT (hashed SubPlan 3)) AND (hub_id = 39))
  • Rows Removed by Filter: 1198374
13.          

SubPlan (forParallel Seq Scan)

14. 6.500 6.500 ↑ 1.0 4,169 3

Seq Scan on user_files (cost=0.00..522.98 rows=4,235 width=4) (actual time=0.042..6.500 rows=4,169 loops=3)

  • Filter: (is_public AND (hub_id = 39) AND (file_category = 'Resume'::text))
  • Rows Removed by Filter: 8363
15. 54,335.816 56,490.192 ↓ 59.6 2,266 371,646

Materialize (cost=218,117.54..250,230,037.40 rows=38 width=20) (actual time=0.003..0.152 rows=2,266 loops=371,646)

16. 928.403 2,154.376 ↓ 59.9 2,275 1

Index Scan using user_hubs_user_id_hub_id_unique on user_hubs (cost=218,117.54..250,230,037.21 rows=38 width=20) (actual time=1,215.184..2,154.376 rows=2,275 loops=1)

  • Index Cond: (hub_id = 39)
  • Filter: ((signup_stage IS NULL) AND approved AND ((max_connect <> 0) OR (max_connect IS NULL)) AND (import_status = 'joined'::text) AND ((user_type ~~ 'alumni'::text) OR (user_type ~~ 'FacultyandStaff'::text) OR (user_type ~~ 'nonalumnimentor'::text)) AND ((user_type = 'alumni'::text) OR (user_type = 'FacultyandStaff'::text) OR (user_type = 'nonalumnimentor'::text)) AND ((visibility = 'PUBLIC'::text) OR (SubPlan 1) OR (hashed SubPlan 2)))
  • Rows Removed by Filter: 368592
17.          

SubPlan (forIndex Scan)

18. 0.953 11.186 ↑ 191.7 121 34

Result (cost=6.48..1,315.05 rows=23,200 width=4) (actual time=0.016..0.329 rows=121 loops=34)

  • One-Time Filter: (user_hubs.visibility = 'GROUPS_PROGRAMS'::text)
19. 1.773 10.233 ↑ 50.9 456 9

Nested Loop (cost=6.48..1,315.05 rows=23,200 width=4) (actual time=0.059..1.137 rows=456 loops=9)

20. 0.360 0.360 ↓ 6.0 12 9

Index Scan using groups_users_user_index on groups_users groups_users_1 (cost=0.42..12.43 rows=2 width=4) (actual time=0.016..0.040 rows=12 loops=9)

  • Index Cond: (user_id = 72610)
  • Filter: approved
21. 6.588 8.100 ↑ 5.6 38 108

Bitmap Heap Scan on groups_users (cost=6.06..649.20 rows=211 width=8) (actual time=0.020..0.075 rows=38 loops=108)

  • Recheck Cond: (group_id = groups_users_1.group_id)
  • Heap Blocks: exact=2160
22. 1.512 1.512 ↑ 5.6 38 108

Bitmap Index Scan on groups_users_group_id_user_id_key (cost=0.00..6.00 rows=211 width=0) (actual time=0.014..0.014 rows=38 loops=108)

  • Index Cond: (group_id = groups_users_1.group_id)
23. 7.526 1,214.787 ↓ 3.0 3 1

Gather (cost=1,000.00..218,117.11 rows=1 width=4) (actual time=652.238..1,214.787 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
24. 0.031 1,207.261 ↑ 1.0 1 3

Subquery Scan on b (cost=0.00..217,117.01 rows=1 width=4) (actual time=791.786..1,207.261 rows=1 loops=3)

  • Filter: ((b.item ->> 'id'::text) = 'alumni'::text)
  • Rows Removed by Filter: 7
25. 0.140 1,207.230 ↑ 62.5 8 3

ProjectSet (cost=0.00..217,116.83 rows=500 width=36) (actual time=471.494..1,207.230 rows=8 loops=3)

26. 1,207.090 1,207.090 ↑ 1.7 3 3

Parallel Seq Scan on user_hubs user_hubs_2 (cost=0.00..217,114.29 rows=5 width=7) (actual time=471.398..1,207.090 rows=3 loops=3)

  • Filter: ((deleted_at IS NULL) AND (hub_id = 39) AND (visibility = 'SELECT_USER_TYPES'::text))
  • Rows Removed by Filter: 1321840