explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l6v

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.998 ↑ 1.0 20 1

Limit (cost=908.28..908.33 rows=20 width=359) (actual time=0.996..0.998 rows=20 loops=1)

  • Buffers: shared hit=373
2.          

CTE _author

3. 0.008 0.008 ↑ 1.0 1 1

Index Scan using post_pkey on post p (cost=0.42..8.44 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (id = 758,082)
  • Buffers: shared hit=4
4.          

CTE _commenters

5. 0.003 0.211 ↑ 1.0 20 1

Limit (cost=1.15..151.56 rows=20 width=4) (actual time=0.048..0.211 rows=20 loops=1)

  • Buffers: shared hit=88
6. 0.080 0.208 ↑ 132.1 20 1

Nested Loop Anti Join (cost=1.15..19,863.15 rows=2,641 width=4) (actual time=0.048..0.208 rows=20 loops=1)

  • Join Filter: (user_event_blocks.suggested_user_id = u.id)
  • Rows Removed by Join Filter: 1,200
  • Buffers: shared hit=88
7. 0.016 0.048 ↑ 132.1 20 1

Nested Loop (cost=0.86..19,696.32 rows=2,641 width=4) (actual time=0.019..0.048 rows=20 loops=1)

  • Buffers: shared hit=85
8. 0.012 0.012 ↑ 194.2 20 1

Index Only Scan using index_comment_on_post_id_and_author_id on comment c (cost=0.43..148.38 rows=3,883 width=4) (actual time=0.010..0.012 rows=20 loops=1)

  • Index Cond: (post_id = 758,082)
  • Heap Fetches: 0
  • Buffers: shared hit=4
9. 0.020 0.020 ↑ 1.0 1 20

Index Only Scan using index_users_active on users u (cost=0.42..5.03 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=20)

  • Index Cond: (id = c.author_id)
  • Filter: (id <> 8)
  • Heap Fetches: 20
  • Buffers: shared hit=81
10. 0.065 0.080 ↓ 15.0 60 20

Materialize (cost=0.29..8.38 rows=4 width=4) (actual time=0.001..0.004 rows=60 loops=20)

  • Buffers: shared hit=3
11. 0.015 0.015 ↓ 15.0 60 1

Index Only Scan using index_user_event_blocks_on_user_id_and_suggested_user_id on user_event_blocks (cost=0.29..8.36 rows=4 width=4) (actual time=0.010..0.015 rows=60 loops=1)

  • Index Cond: (user_id = 8)
  • Heap Fetches: 0
  • Buffers: shared hit=3
12.          

CTE _connections

13. 0.002 0.315 ↑ 1.0 20 1

Limit (cost=47.59..332.03 rows=20 width=4) (actual time=0.268..0.315 rows=20 loops=1)

  • Buffers: shared hit=91
14. 0.003 0.313 ↑ 31.4 20 1

Nested Loop (cost=47.59..8,978.94 rows=628 width=4) (actual time=0.267..0.313 rows=20 loops=1)

  • Buffers: shared hit=91
15. 0.071 0.266 ↑ 42.0 22 1

Bitmap Heap Scan on user_connection uc (cost=47.16..3,238.19 rows=924 width=8) (actual time=0.262..0.266 rows=22 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=1
  • Buffers: shared hit=10
16. 0.195 0.195 ↑ 1.0 887 1

Bitmap Index Scan on "index_user_connection_on_ARRAY_user_a_id_user_b_id_gin__int_ops" (cost=0.00..46.93 rows=924 width=0) (actual time=0.194..0.195 rows=887 loops=1)

  • Index Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=9
17. 0.044 0.044 ↑ 1.0 1 22

Index Only Scan using index_users_active on users u_1 (cost=0.43..6.21 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=22)

  • Index Cond: (id = CASE WHEN (uc.user_a_id = 8) THEN uc.user_b_id ELSE uc.user_a_id END)
  • Heap Fetches: 14
  • Buffers: shared hit=81
18.          

CTE _group_members

19. 0.003 0.277 ↑ 1.0 20 1

Limit (cost=1.15..237.75 rows=20 width=8) (actual time=0.042..0.277 rows=20 loops=1)

  • Buffers: shared hit=110
20. 0.092 0.274 ↑ 197.0 20 1

Nested Loop Anti Join (cost=1.15..46,612.45 rows=3,940 width=8) (actual time=0.042..0.274 rows=20 loops=1)

  • Join Filter: (user_event_blocks_1.suggested_user_id = u_2.id)
  • Rows Removed by Join Filter: 1,200
  • Buffers: shared hit=110
21. 0.019 0.122 ↑ 197.0 20 1

Nested Loop (cost=0.85..46,367.68 rows=3,940 width=12) (actual time=0.020..0.122 rows=20 loops=1)

  • Buffers: shared hit=107
22. 0.034 0.034 ↑ 251.9 23 1

Index Scan using index_pages_group_memberships_on_group_id on pages_group_memberships pgm (cost=0.43..20,025.79 rows=5,793 width=8) (actual time=0.012..0.034 rows=23 loops=1)

  • Index Cond: (group_id = 8,401)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Buffers: shared hit=22
23. 0.069 0.069 ↑ 1.0 1 23

Index Only Scan using index_users_active on users u_2 (cost=0.42..4.55 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: (id = pgm.user_id)
  • Filter: (id <> 8)
  • Heap Fetches: 15
  • Buffers: shared hit=85
24. 0.050 0.060 ↓ 15.0 60 20

Materialize (cost=0.29..8.38 rows=4 width=4) (actual time=0.000..0.003 rows=60 loops=20)

  • Buffers: shared hit=3
25. 0.010 0.010 ↓ 15.0 60 1

Index Only Scan using index_user_event_blocks_on_user_id_and_suggested_user_id on user_event_blocks user_event_blocks_1 (cost=0.29..8.36 rows=4 width=4) (actual time=0.005..0.010 rows=60 loops=1)

  • Index Cond: (user_id = 8)
  • Heap Fetches: 0
  • Buffers: shared hit=3
26.          

CTE _preferred_users

27. 0.002 0.884 ↑ 1.0 20 1

Limit (cost=5.60..5.65 rows=20 width=12) (actual time=0.881..0.884 rows=20 loops=1)

  • Buffers: shared hit=293
28. 0.010 0.882 ↑ 3.0 20 1

Sort (cost=5.60..5.75 rows=61 width=12) (actual time=0.881..0.882 rows=20 loops=1)

  • Sort Key: (0)
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=293
29. 0.016 0.872 ↑ 1.5 42 1

HashAggregate (cost=3.36..3.97 rows=61 width=12) (actual time=0.866..0.872 rows=42 loops=1)

  • Group Key: ((_author.id)::bigint), (0)
  • Buffers: shared hit=293
30. 0.005 0.856 ↑ 1.5 42 1

Append (cost=1.64..3.06 rows=61 width=12) (actual time=0.565..0.856 rows=42 loops=1)

  • Buffers: shared hit=293
31. 0.015 0.568 ↑ 1.9 22 1

HashAggregate (cost=1.64..2.15 rows=41 width=12) (actual time=0.565..0.568 rows=22 loops=1)

  • Group Key: _author.id, (0)
  • Buffers: shared hit=183
32. 0.005 0.553 ↑ 1.0 41 1

Append (cost=0.00..1.44 rows=41 width=8) (actual time=0.009..0.553 rows=41 loops=1)

  • Buffers: shared hit=183
33. 0.010 0.010 ↑ 1.0 1 1

CTE Scan on _author (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Buffers: shared hit=4
34. 0.217 0.217 ↑ 1.0 20 1

CTE Scan on _commenters (cost=0.00..0.40 rows=20 width=8) (actual time=0.049..0.217 rows=20 loops=1)

  • Buffers: shared hit=88
35. 0.321 0.321 ↑ 1.0 20 1

CTE Scan on _connections (cost=0.00..0.40 rows=20 width=8) (actual time=0.268..0.321 rows=20 loops=1)

  • Buffers: shared hit=91
36. 0.283 0.283 ↑ 1.0 20 1

CTE Scan on _group_members (cost=0.00..0.40 rows=20 width=12) (actual time=0.043..0.283 rows=20 loops=1)

  • Buffers: shared hit=110
37.          

CTE _result

38. 0.002 0.918 ↑ 1.0 20 1

Limit (cost=3.06..3.11 rows=20 width=12) (actual time=0.915..0.918 rows=20 loops=1)

  • Buffers: shared hit=293
39. 0.007 0.916 ↑ 2.0 20 1

Sort (cost=3.06..3.16 rows=40 width=12) (actual time=0.915..0.916 rows=20 loops=1)

  • Sort Key: _preferred_users.source_order
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=293
40. 0.014 0.909 ↑ 1.0 40 1

HashAggregate (cost=1.60..2.00 rows=40 width=12) (actual time=0.904..0.909 rows=40 loops=1)

  • Group Key: _preferred_users.id, _preferred_users.source_order
  • Buffers: shared hit=293
41. 0.004 0.895 ↑ 1.0 40 1

Append (cost=0.00..1.40 rows=40 width=12) (actual time=0.882..0.895 rows=40 loops=1)

  • Buffers: shared hit=293
42. 0.888 0.888 ↑ 1.0 20 1

CTE Scan on _preferred_users (cost=0.00..0.40 rows=20 width=12) (actual time=0.881..0.888 rows=20 loops=1)

  • Buffers: shared hit=293
43. 0.003 0.003 ↑ 1.0 20 1

CTE Scan on _preferred_users _preferred_users_1 (cost=0.00..0.40 rows=20 width=12) (actual time=0.001..0.003 rows=20 loops=1)

44. 0.021 0.996 ↑ 1.0 20 1

Sort (cost=169.73..169.78 rows=20 width=359) (actual time=0.995..0.996 rows=20 loops=1)

  • Sort Key: _result.source_order
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=373
45. 0.012 0.975 ↑ 1.0 20 1

Nested Loop (cost=0.43..169.30 rows=20 width=359) (actual time=0.925..0.975 rows=20 loops=1)

  • Buffers: shared hit=373
46. 0.923 0.923 ↑ 1.0 20 1

CTE Scan on _result (cost=0.00..0.40 rows=20 width=12) (actual time=0.916..0.923 rows=20 loops=1)

  • Buffers: shared hit=293
47. 0.040 0.040 ↑ 1.0 1 20

Index Scan using users_pkey on users (cost=0.43..8.45 rows=1 width=355) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (id = _result.id)
  • Buffers: shared hit=80
Planning time : 1.411 ms
Execution time : 1.122 ms