explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hRXJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 0.774 ↑ 1.0 20 1

Limit (cost=139.40..139.45 rows=20 width=354) (actual time=0.768..0.774 rows=20 loops=1)

  • Buffers: shared hit=22
2.          

CTE _author

3. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.56..16.69 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Buffers: shared hit=2
4. 0.011 0.011 ↓ 0.0 0 1

Index Scan using post_pkey on post p (cost=0.28..8.30 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (id = 758,082)
  • Buffers: shared hit=2
5. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users u_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = p.author_id)
  • Filter: (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text)
6.          

CTE _commenters

7. 0.000 0.007 ↓ 0.0 0 1

Limit (cost=20.57..38.79 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Buffers: shared hit=2
8. 0.001 0.007 ↓ 0.0 0 1

Hash Join (cost=20.57..38.79 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Hash Cond: (c.author_id = u_3.id)
  • Buffers: shared hit=2
9. 0.001 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on comment c (cost=4.32..22.53 rows=5 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: (post_id = 758,082)
  • Buffers: shared hit=2
10. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on index_comment_on_post_id_and_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (post_id = 758,082)
  • Buffers: shared hit=2
11. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.15..16.15 rows=8 width=4) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on users u_3 (cost=4.35..16.15 rows=8 width=4) (never executed)

  • Recheck Cond: ((completed_reg_at IS NOT NULL) AND (account_status = 'active'::user_account_status))
  • Filter: ((id <> 8) AND (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text))
13. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_users_active (cost=0.00..4.34 rows=40 width=0) (never executed)

14.          

CTE _connections

15. 0.001 0.012 ↓ 0.0 0 1

Limit (cost=8.15..21.97 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
16. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=8.15..21.97 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Buffers: shared hit=2
17. 0.001 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on user_connection uc (cost=8.00..12.02 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=2
18. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on "index_user_connection_on_ARRAY_user_a_id_user_b_id_gin__int_ops" (cost=0.00..8.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (ARRAY[user_a_id, user_b_id] @> '{8}'::integer[])
  • Buffers: shared hit=2
19. 0.000 0.000 ↓ 0.0 0

Index Scan using index_users_active on users u_4 (cost=0.15..8.17 rows=1 width=4) (never executed)

  • 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)
20.          

CTE _group_members

21. 0.001 0.009 ↓ 0.0 0 1

Limit (cost=0.42..18.26 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)

  • Buffers: shared hit=2
22. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.42..18.26 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Buffers: shared hit=2
23. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using index_pages_group_memberships_user_status on pages_group_memberships pgm (cost=0.28..8.31 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((group_id = 8,401) AND (status = 'joined'::pages_group_membership_status))
  • Heap Fetches: 0
  • Buffers: shared hit=2
24. 0.000 0.000 ↓ 0.0 0

Index Scan using index_users_active on users u_5 (cost=0.14..8.17 rows=1 width=4) (never executed)

  • Index Cond: (id = pgm.user_id)
  • Filter: ((id <> 8) AND (concat_ws(' '::text, first_name, last_name) ~~* '%Ja%'::text))
25. 0.062 0.769 ↑ 1.0 20 1

Sort (cost=43.69..43.74 rows=20 width=354) (actual time=0.767..0.769 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: 29kB
  • Buffers: shared hit=22
26. 0.156 0.707 ↑ 1.0 20 1

Nested Loop Anti Join (cost=18.64..43.26 rows=20 width=354) (actual time=0.267..0.707 rows=20 loops=1)

  • Join Filter: (user_event_blocks.suggested_user_id = u.id)
  • Buffers: shared hit=22
27. 0.209 0.531 ↑ 1.0 20 1

Hash Join (cost=18.64..41.14 rows=20 width=350) (actual time=0.201..0.531 rows=20 loops=1)

  • Hash Cond: (u.id = _result.id)
  • Buffers: shared hit=21
28. 0.151 0.151 ↑ 1.0 822 1

Seq Scan on users u (cost=0.00..19.22 rows=822 width=346) (actual time=0.014..0.151 rows=822 loops=1)

  • Buffers: shared hit=11
29. 0.010 0.171 ↑ 1.0 20 1

Hash (cost=18.39..18.39 rows=20 width=12) (actual time=0.171..0.171 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=10
30. 0.007 0.161 ↑ 1.0 20 1

Subquery Scan on _result (cost=18.14..18.39 rows=20 width=12) (actual time=0.150..0.161 rows=20 loops=1)

  • Buffers: shared hit=10
31. 0.004 0.154 ↑ 1.0 20 1

Limit (cost=18.14..18.19 rows=20 width=12) (actual time=0.149..0.154 rows=20 loops=1)

  • Buffers: shared hit=10
32. 0.012 0.150 ↑ 1.2 20 1

Sort (cost=18.14..18.20 rows=24 width=12) (actual time=0.148..0.150 rows=20 loops=1)

  • Sort Key: (0)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=10
33. 0.023 0.138 ↑ 1.2 20 1

HashAggregate (cost=17.35..17.59 rows=24 width=12) (actual time=0.132..0.138 rows=20 loops=1)

  • Group Key: ((_author.id)::bigint), (0)
  • Buffers: shared hit=10
34. 0.005 0.115 ↑ 1.2 20 1

Append (cost=0.23..17.23 rows=24 width=12) (actual time=0.090..0.115 rows=20 loops=1)

  • Buffers: shared hit=10
35. 0.001 0.046 ↓ 0.0 0 1

Limit (cost=0.23..0.27 rows=4 width=12) (actual time=0.046..0.046 rows=0 loops=1)

  • Buffers: shared hit=8
36. 0.000 0.045 ↓ 0.0 0 1

HashAggregate (cost=0.23..0.27 rows=4 width=12) (actual time=0.045..0.045 rows=0 loops=1)

  • Group Key: ((_author.id)::bigint), (0)
  • Buffers: shared hit=8
37. 0.002 0.045 ↓ 0.0 0 1

Append (cost=0.12..0.21 rows=4 width=12) (actual time=0.044..0.045 rows=0 loops=1)

  • Buffers: shared hit=8
38. 0.000 0.034 ↓ 0.0 0 1

HashAggregate (cost=0.12..0.16 rows=3 width=12) (actual time=0.034..0.034 rows=0 loops=1)

  • Group Key: _author.id, (0)
  • Buffers: shared hit=6
39. 0.002 0.034 ↓ 0.0 0 1

Append (cost=0.00..0.10 rows=3 width=8) (actual time=0.033..0.034 rows=0 loops=1)

  • Buffers: shared hit=6
40. 0.012 0.012 ↓ 0.0 0 1

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

  • Buffers: shared hit=2
41. 0.008 0.008 ↓ 0.0 0 1

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

  • Buffers: shared hit=2
42. 0.012 0.012 ↓ 0.0 0 1

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

  • Buffers: shared hit=2
43. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on _group_members (cost=0.00..0.02 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=1)

  • Buffers: shared hit=2
44. 0.007 0.064 ↑ 1.0 20 1

Subquery Scan on *SELECT* 2 (cost=4.61..16.76 rows=20 width=12) (actual time=0.043..0.064 rows=20 loops=1)

  • Buffers: shared hit=2
45. 0.006 0.057 ↑ 1.0 20 1

Subquery Scan on _other_users (cost=4.61..16.51 rows=20 width=8) (actual time=0.042..0.057 rows=20 loops=1)

  • Buffers: shared hit=2
46. 0.004 0.051 ↑ 1.0 20 1

Limit (cost=4.61..16.31 rows=20 width=4) (actual time=0.041..0.051 rows=20 loops=1)

  • Buffers: shared hit=2
47. 0.024 0.047 ↑ 1.0 20 1

Bitmap Heap Scan on users u_1 (cost=4.61..16.31 rows=20 width=4) (actual time=0.040..0.047 rows=20 loops=1)

  • Recheck Cond: ((completed_reg_at IS NOT NULL) AND (account_status = 'active'::user_account_status))
  • Filter: ((id <> 8) AND (NOT (hashed SubPlan 5)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1
  • Buffers: shared hit=2
48. 0.019 0.019 ↓ 3.3 131 1

Bitmap Index Scan on index_users_active (cost=0.00..4.34 rows=40 width=0) (actual time=0.019..0.019 rows=131 loops=1)

  • Buffers: shared hit=1
49.          

SubPlan (for Bitmap Heap Scan)

50. 0.000 0.004 ↓ 0.0 0 1

HashAggregate (cost=0.21..0.25 rows=4 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Group Key: ((_author_1.id)::bigint)
51. 0.001 0.004 ↓ 0.0 0 1

Append (cost=0.11..0.20 rows=4 width=8) (actual time=0.004..0.004 rows=0 loops=1)

52. 0.001 0.003 ↓ 0.0 0 1

HashAggregate (cost=0.11..0.15 rows=3 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Group Key: _author_1.id
53. 0.002 0.002 ↓ 0.0 0 1

Append (cost=0.00..0.10 rows=3 width=4) (actual time=0.002..0.002 rows=0 loops=1)

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

55. 0.000 0.000 ↓ 0.0 0 1

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

56. 0.000 0.000 ↓ 0.0 0 1

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

57. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on _group_members _group_members_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

58. 0.006 0.020 ↓ 0.0 0 20

Materialize (cost=0.00..1.72 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=20)

  • Buffers: shared hit=1
59. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on user_event_blocks (cost=0.00..1.71 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: (user_id = 8)
  • Rows Removed by Filter: 57
  • Buffers: shared hit=1
Planning time : 3.946 ms
Execution time : 2.700 ms