explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Smfu

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 7.645 ↑ 1.0 20 1

Limit (cost=4,270.47..4,270.52 rows=20 width=363) (actual time=7.642..7.645 rows=20 loops=1)

  • Buffers: shared hit=9,131
2.          

CTE _author

3. 0.001 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.85..16.89 rows=1 width=4) (actual time=0.024..0.024 rows=0 loops=1)

  • Buffers: shared hit=8
4. 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.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 758,082)
  • Buffers: shared hit=4
5. 0.015 0.015 ↓ 0.0 0 1

Index Scan using users_pkey on users u_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (id = p.author_id)
  • Filter: (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
6.          

CTE _commenters

7. 0.003 1.661 ↑ 1.0 20 1

Limit (cost=0.86..976.12 rows=20 width=4) (actual time=1.619..1.661 rows=20 loops=1)

  • Buffers: shared hit=3,238
8. 0.000 1.658 ↑ 26.4 20 1

Nested Loop (cost=0.86..25,747.74 rows=528 width=4) (actual time=1.619..1.658 rows=20 loops=1)

  • Buffers: shared hit=3,238
9. 0.122 0.122 ↑ 4.8 815 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.012..0.122 rows=815 loops=1)

  • Index Cond: (post_id = 758,082)
  • Heap Fetches: 41
  • Buffers: shared hit=42
10. 1.630 1.630 ↓ 0.0 0 815

Index Scan using index_users_active on users u_2 (cost=0.42..6.59 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=815)

  • Index Cond: (id = c.author_id)
  • Filter: ((id <> 8) AND (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3,196
11.          

CTE _connections

12. 0.004 3.497 ↑ 1.0 20 1

Limit (cost=51.59..1,707.26 rows=20 width=4) (actual time=0.546..3.497 rows=20 loops=1)

  • Buffers: shared hit=3,698
13. 0.260 3.493 ↑ 6.3 20 1

Nested Loop (cost=51.59..10,482.30 rows=126 width=4) (actual time=0.546..3.493 rows=20 loops=1)

  • Buffers: shared hit=3,698
14. 0.642 0.854 ↑ 1.2 793 1

Bitmap Heap Scan on user_connection uc (cost=51.16..3,242.19 rows=924 width=8) (actual time=0.277..0.854 rows=793 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=592
  • Buffers: shared hit=602
15. 0.212 0.212 ↑ 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..50.93 rows=924 width=0) (actual time=0.211..0.212 rows=887 loops=1)

  • Index Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=10
16. 2.379 2.379 ↓ 0.0 0 793

Index Scan using index_users_active on users u_3 (cost=0.43..7.84 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=793)

  • Index Cond: (id = CASE WHEN (uc.user_a_id = 8) THEN uc.user_b_id ELSE uc.user_a_id END)
  • Filter: (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3,096
17.          

CTE _group_members

18. 0.004 2.035 ↑ 1.0 20 1

Limit (cost=0.85..1,378.82 rows=20 width=8) (actual time=0.145..2.035 rows=20 loops=1)

  • Buffers: shared hit=2,066
19. 0.000 2.031 ↑ 42.0 20 1

Nested Loop (cost=0.85..57,875.38 rows=840 width=8) (actual time=0.144..2.031 rows=20 loops=1)

  • Buffers: shared hit=2,066
20. 0.382 0.382 ↑ 14.7 419 1

Index Scan using index_pages_group_memberships_on_group_id on pages_group_memberships pgm (cost=0.43..21,279.28 rows=6,178 width=8) (actual time=0.013..0.382 rows=419 loops=1)

  • Index Cond: (group_id = 8,401)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=396
21. 1.676 1.676 ↓ 0.0 0 419

Index Scan using index_users_active on users u_4 (cost=0.42..5.92 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=419)

  • Index Cond: (id = pgm.user_id)
  • Filter: ((id <> 8) AND (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,670
22.          

CTE _preferred_users

23. 0.004 7.299 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,010
24. 0.011 7.295 ↑ 3.0 20 1

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

  • Sort Key: (0)
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=9,010
25. 0.018 7.284 ↑ 1.5 41 1

HashAggregate (cost=3.36..3.97 rows=61 width=12) (actual time=7.278..7.284 rows=41 loops=1)

  • Group Key: ((_author.id)::bigint), (0)
  • Buffers: shared hit=9,010
26. 0.006 7.266 ↑ 1.5 41 1

Append (cost=1.64..3.06 rows=61 width=12) (actual time=5.216..7.266 rows=41 loops=1)

  • Buffers: shared hit=9,010
27. 0.017 5.218 ↑ 2.0 21 1

HashAggregate (cost=1.64..2.15 rows=41 width=12) (actual time=5.215..5.218 rows=21 loops=1)

  • Group Key: _author.id, (0)
  • Buffers: shared hit=6,944
28. 0.005 5.201 ↑ 1.0 40 1

Append (cost=0.00..1.44 rows=41 width=8) (actual time=1.645..5.201 rows=40 loops=1)

  • Buffers: shared hit=6,944
29. 0.024 0.024 ↓ 0.0 0 1

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

  • Buffers: shared hit=8
30. 1.668 1.668 ↑ 1.0 20 1

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

  • Buffers: shared hit=3,238
31. 3.504 3.504 ↑ 1.0 20 1

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

  • Buffers: shared hit=3,698
32. 2.042 2.042 ↑ 1.0 20 1

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

  • Buffers: shared hit=2,066
33.          

CTE _other_users

34. 0.004 0.069 ↑ 1.0 20 1

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

  • Buffers: shared hit=1
35. 0.027 0.065 ↑ 21,388.0 20 1

Seq Scan on users u_5 (cost=3.87..55,067.05 rows=427,761 width=4) (actual time=0.058..0.065 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
36.          

SubPlan (for Seq Scan)

37. 0.013 0.038 ↑ 1.5 41 1

HashAggregate (cost=3.11..3.72 rows=61 width=8) (actual time=0.034..0.038 rows=41 loops=1)

  • Group Key: ((_author_1.id)::bigint)
38. 0.003 0.025 ↑ 1.5 41 1

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

39. 0.011 0.020 ↑ 2.0 21 1

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

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

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

41. 0.000 0.000 ↓ 0.0 0 1

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

42. 0.003 0.003 ↑ 1.0 20 1

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

43. 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)

44. 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)

45.          

CTE _result

46. 0.003 7.413 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,011
47. 0.009 7.410 ↑ 2.0 20 1

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

  • Sort Key: _preferred_users.source_order
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=9,011
48. 0.015 7.401 ↑ 1.0 40 1

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

  • Group Key: _preferred_users.id, _preferred_users.source_order
  • Buffers: shared hit=9,011
49. 0.005 7.386 ↑ 1.0 40 1

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

  • Buffers: shared hit=9,011
50. 7.304 7.304 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,010
51. 0.002 0.077 ↑ 1.0 20 1

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

  • Buffers: shared hit=1
52. 0.075 0.075 ↑ 1.0 20 1

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

  • Buffers: shared hit=1
53. 0.042 7.642 ↑ 1.0 20 1

Sort (cost=176.13..176.18 rows=20 width=363) (actual time=7.641..7.642 rows=20 loops=1)

  • Sort Key: _result.source_order, (similarity(concat_ws(' '::text, u.first_name, u.last_name), 'Ja'::text))
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=9,131
54. 0.076 7.600 ↑ 1.0 20 1

Nested Loop Anti Join (cost=0.72..175.69 rows=20 width=363) (actual time=7.441..7.600 rows=20 loops=1)

  • Buffers: shared hit=9,131
55. 0.007 7.504 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,091
56. 7.417 7.417 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,011
57. 0.080 0.080 ↑ 1.0 1 20

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

  • Index Cond: (id = _result.id)
  • Buffers: shared hit=80
58. 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.503 ms
Execution time : 7.818 ms