explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FBjP

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.831 ↑ 1.0 20 1

Limit (cost=918.59..918.64 rows=20 width=359) (actual time=0.828..0.831 rows=20 loops=1)

  • Buffers: shared hit=412
2.          

CTE _author

3. 0.009 0.009 ↑ 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.009 rows=1 loops=1)

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

CTE _commenters

5. 0.003 0.050 ↑ 1.0 20 1

Limit (cost=0.86..150.01 rows=20 width=4) (actual time=0.020..0.050 rows=20 loops=1)

  • Buffers: shared hit=85
6. 0.014 0.047 ↑ 132.1 20 1

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

  • Buffers: shared hit=85
7. 0.013 0.013 ↑ 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.011..0.013 rows=20 loops=1)

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

Index Only Scan using index_users_active on users u_1 (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
9.          

CTE _connections

10. 0.003 0.330 ↑ 1.0 20 1

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

  • Buffers: shared hit=91
11. 0.000 0.327 ↑ 31.4 20 1

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

  • Buffers: shared hit=91
12. 0.071 0.284 ↑ 42.0 22 1

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

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=1
  • Buffers: shared hit=10
13. 0.213 0.213 ↑ 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.213..0.213 rows=887 loops=1)

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

Index Only Scan using index_users_active on users u_2 (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
15.          

CTE _group_members

16. 0.003 0.149 ↑ 1.0 20 1

Limit (cost=0.85..236.83 rows=20 width=8) (actual time=0.040..0.149 rows=20 loops=1)

  • Buffers: shared hit=111
17. 0.000 0.146 ↑ 197.2 20 1

Nested Loop (cost=0.85..46,524.29 rows=3,943 width=8) (actual time=0.039..0.146 rows=20 loops=1)

  • Buffers: shared hit=111
18. 0.037 0.037 ↑ 263.5 22 1

Index Scan using index_pages_group_memberships_on_group_id on pages_group_memberships pgm (cost=0.43..20,172.62 rows=5,797 width=8) (actual time=0.014..0.037 rows=22 loops=1)

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

Index Only Scan using index_users_active on users u_3 (cost=0.42..4.55 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=22)

  • Index Cond: (id = pgm.user_id)
  • Filter: (id <> 8)
  • Heap Fetches: 17
  • Buffers: shared hit=84
20.          

CTE _preferred_users

21. 0.004 0.612 ↑ 1.0 20 1

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

  • Buffers: shared hit=291
22. 0.010 0.608 ↑ 3.0 20 1

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

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

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

  • Group Key: ((_author.id)::bigint), (0)
  • Buffers: shared hit=291
24. 0.004 0.582 ↑ 1.5 42 1

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

  • Buffers: shared hit=291
25. 0.015 0.422 ↑ 1.9 22 1

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

  • Group Key: _author.id, (0)
  • Buffers: shared hit=180
26. 0.004 0.407 ↑ 1.0 41 1

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

  • Buffers: shared hit=180
27. 0.012 0.012 ↑ 1.0 1 1

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

  • Buffers: shared hit=4
28. 0.055 0.055 ↑ 1.0 20 1

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

  • Buffers: shared hit=85
29. 0.336 0.336 ↑ 1.0 20 1

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

  • Buffers: shared hit=91
30. 0.156 0.156 ↑ 1.0 20 1

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

  • Buffers: shared hit=111
31.          

CTE _other_users

32. 0.003 0.065 ↑ 1.0 20 1

Limit (cost=3.87..6.44 rows=20 width=4) (actual time=0.054..0.065 rows=20 loops=1)

  • Buffers: shared hit=1
33. 0.025 0.062 ↑ 21,388.0 20 1

Seq Scan on users u_4 (cost=3.87..55,067.05 rows=427,761 width=4) (actual time=0.054..0.062 rows=20 loops=1)

  • Filter: ((completed_reg_at IS NOT NULL) AND (id <> 8) AND (NOT (hashed SubPlan 6)) AND (account_status = 'active'::user_account_status))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
34.          

SubPlan (for Seq Scan)

35. 0.012 0.037 ↑ 1.5 42 1

HashAggregate (cost=3.11..3.72 rows=61 width=8) (actual time=0.032..0.037 rows=42 loops=1)

  • Group Key: ((_author_1.id)::bigint)
36. 0.004 0.025 ↑ 1.5 42 1

Append (cost=1.54..2.96 rows=61 width=8) (actual time=0.017..0.025 rows=42 loops=1)

37. 0.010 0.019 ↑ 1.9 22 1

HashAggregate (cost=1.54..2.05 rows=41 width=8) (actual time=0.017..0.019 rows=22 loops=1)

  • Group Key: _author_1.id
38. 0.004 0.009 ↑ 1.0 41 1

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

39. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _author _author_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

40. 0.002 0.002 ↑ 1.0 20 1

CTE Scan on _commenters _commenters_1 (cost=0.00..0.40 rows=20 width=4) (actual time=0.000..0.002 rows=20 loops=1)

41. 0.002 0.002 ↑ 1.0 20 1

CTE Scan on _connections _connections_1 (cost=0.00..0.40 rows=20 width=4) (actual time=0.000..0.002 rows=20 loops=1)

42. 0.002 0.002 ↑ 1.0 20 1

CTE Scan on _group_members _group_members_1 (cost=0.00..0.40 rows=20 width=8) (actual time=0.000..0.002 rows=20 loops=1)

43.          

CTE _result

44. 0.002 0.717 ↑ 1.0 20 1

Limit (cost=3.11..3.16 rows=20 width=12) (actual time=0.714..0.717 rows=20 loops=1)

  • Buffers: shared hit=292
45. 0.008 0.715 ↑ 2.0 20 1

Sort (cost=3.11..3.21 rows=40 width=12) (actual time=0.714..0.715 rows=20 loops=1)

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

HashAggregate (cost=1.65..2.05 rows=40 width=12) (actual time=0.703..0.707 rows=40 loops=1)

  • Group Key: _preferred_users.id, _preferred_users.source_order
  • Buffers: shared hit=292
47. 0.004 0.693 ↑ 1.0 40 1

Append (cost=0.00..1.45 rows=40 width=12) (actual time=0.608..0.693 rows=40 loops=1)

  • Buffers: shared hit=292
48. 0.616 0.616 ↑ 1.0 20 1

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

  • Buffers: shared hit=291
49. 0.003 0.073 ↑ 1.0 20 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.65 rows=20 width=12) (actual time=0.056..0.073 rows=20 loops=1)

  • Buffers: shared hit=1
50. 0.070 0.070 ↑ 1.0 20 1

CTE Scan on _other_users (cost=0.00..0.40 rows=20 width=8) (actual time=0.055..0.070 rows=20 loops=1)

  • Buffers: shared hit=1
51. 0.019 0.827 ↑ 1.0 20 1

Sort (cost=176.03..176.08 rows=20 width=359) (actual time=0.826..0.827 rows=20 loops=1)

  • Sort Key: _result.source_order
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=412
52. 0.010 0.808 ↑ 1.0 20 1

Nested Loop Anti Join (cost=0.72..175.59 rows=20 width=359) (actual time=0.731..0.808 rows=20 loops=1)

  • Buffers: shared hit=412
53. 0.016 0.778 ↑ 1.0 20 1

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

  • Buffers: shared hit=372
54. 0.722 0.722 ↑ 1.0 20 1

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

  • Buffers: shared hit=292
55. 0.040 0.040 ↑ 1.0 1 20

Index Scan using users_pkey on users u (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
56. 0.020 0.020 ↓ 0.0 0 20

Index Only Scan using index_user_event_blocks_on_user_id_and_suggested_user_id on user_event_blocks (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=20)

  • Index Cond: ((user_id = 8) AND (suggested_user_id = u.id))
  • Heap Fetches: 0
  • Buffers: shared hit=40
Planning time : 1.363 ms
Execution time : 0.987 ms