explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lHhq

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

Limit (cost=81,642.07..81,642.12 rows=20 width=359) (actual time=1,154.995..1,154.998 rows=20 loops=1)

  • Buffers: shared hit=40,274 read=42
2.          

CTE _commenters

3. 0.002 6.844 ↑ 1.0 20 1

Limit (cost=1.15..88.55 rows=20 width=4) (actual time=0.068..6.844 rows=20 loops=1)

  • Buffers: shared hit=9,370 read=30
4. 0.111 6.842 ↑ 67.0 20 1

Group (cost=1.15..5,856.86 rows=1,340 width=4) (actual time=0.068..6.842 rows=20 loops=1)

  • Group Key: c.author_id
  • Buffers: shared hit=9,370 read=30
5. 0.547 6.731 ↑ 1.2 1,115 1

Nested Loop Anti Join (cost=1.15..5,853.51 rows=1,340 width=4) (actual time=0.067..6.731 rows=1,115 loops=1)

  • Buffers: shared hit=9,370 read=30
6. 0.244 3.954 ↑ 1.2 1,115 1

Nested Loop (cost=0.86..5,433.57 rows=1,340 width=8) (actual time=0.056..3.954 rows=1,115 loops=1)

  • Buffers: shared hit=5,464 read=7
7. 1.352 1.352 ↑ 1.7 1,179 1

Index Only Scan using index_comment_on_post_id_and_author_id on comment c (cost=0.43..1,223.02 rows=1,990 width=4) (actual time=0.045..1.352 rows=1,179 loops=1)

  • Index Cond: (post_id = 758,082)
  • Heap Fetches: 1,179
  • Buffers: shared hit=812 read=7
8. 2.358 2.358 ↑ 1.0 1 1,179

Index Only Scan using index_users_active on users u (cost=0.42..2.12 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,179)

  • Index Cond: (id = c.author_id)
  • Filter: (id <> 8)
  • Heap Fetches: 1,115
  • Buffers: shared hit=4,652
9. 2.230 2.230 ↓ 0.0 0 1,115

Index Scan using index_user_event_blocks_on_suggested_user_id on user_event_blocks (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,115)

  • Index Cond: (suggested_user_id = u.id)
  • Filter: (user_id = 8)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=3,906 read=23
10.          

CTE _author

11. 0.014 0.014 ↑ 1.0 1 1

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

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

CTE _group_members

13. 0.002 0.492 ↑ 1.0 20 1

Limit (cost=1.15..85.79 rows=20 width=8) (actual time=0.033..0.492 rows=20 loops=1)

  • Buffers: shared hit=152 read=12
14. 0.016 0.490 ↑ 196.8 20 1

Nested Loop Anti Join (cost=1.15..16,658.49 rows=3,936 width=8) (actual time=0.033..0.490 rows=20 loops=1)

  • Buffers: shared hit=152 read=12
15. 0.002 0.254 ↑ 196.8 20 1

Nested Loop (cost=0.85..15,425.05 rows=3,936 width=12) (actual time=0.028..0.254 rows=20 loops=1)

  • Buffers: shared hit=122
16. 0.084 0.084 ↑ 278.4 21 1

Index Scan using index_pages_group_memberships_on_group_id on pages_group_memberships pgm (cost=0.43..5,144.58 rows=5,846 width=8) (actual time=0.015..0.084 rows=21 loops=1)

  • Index Cond: (group_id = 8,401)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Rows Removed by Filter: 15
  • Buffers: shared hit=39
17. 0.168 0.168 ↑ 1.0 1 21

Index Only Scan using index_users_active on users u_1 (cost=0.42..1.76 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=21)

  • Index Cond: (id = pgm.user_id)
  • Filter: (id <> 8)
  • Heap Fetches: 20
  • Buffers: shared hit=83
18. 0.220 0.220 ↓ 0.0 0 20

Index Scan using index_user_event_blocks_on_suggested_user_id on user_event_blocks user_event_blocks_1 (cost=0.29..0.31 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=20)

  • Index Cond: (suggested_user_id = u_1.id)
  • Filter: (user_id = 8)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=30 read=12
19.          

CTE _connections

20. 0.003 0.338 ↑ 1.0 20 1

Limit (cost=7.81..108.52 rows=20 width=4) (actual time=0.228..0.338 rows=20 loops=1)

  • Buffers: shared hit=91
21. 0.030 0.335 ↑ 10.3 20 1

Nested Loop (cost=7.81..1,050.12 rows=207 width=4) (actual time=0.227..0.335 rows=20 loops=1)

  • Buffers: shared hit=91
22. 0.098 0.239 ↑ 14.0 22 1

Bitmap Heap Scan on user_connection uc (cost=7.38..317.22 rows=307 width=8) (actual time=0.214..0.239 rows=22 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=1
  • Buffers: shared hit=5
23. 0.141 0.141 ↓ 2.9 887 1

Bitmap Index Scan on "index_user_connection_on_ARRAY_user_a_id_user_b_id_gin__int_ops" (cost=0.00..7.31 rows=307 width=0) (actual time=0.141..0.141 rows=887 loops=1)

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

Index Only Scan using index_users_active on users u_2 (cost=0.43..2.39 rows=1 width=4) (actual time=0.003..0.003 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)
  • Filter: (id <> 8)
  • Heap Fetches: 20
  • Buffers: shared hit=86
25.          

CTE _combined

26. 0.018 7.782 ↑ 1.0 61 1

Sort (cost=4.96..5.11 rows=61 width=12) (actual time=7.779..7.782 rows=61 loops=1)

  • Sort Key: (3)
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=9,617 read=42
27. 0.032 7.764 ↑ 1.0 61 1

HashAggregate (cost=2.54..3.15 rows=61 width=12) (actual time=7.753..7.764 rows=61 loops=1)

  • Group Key: _group_members.id, (3)
  • Buffers: shared hit=9,617 read=42
28. 0.007 7.732 ↑ 1.0 61 1

Append (cost=0.00..2.24 rows=61 width=12) (actual time=0.035..7.732 rows=61 loops=1)

  • Buffers: shared hit=9,617 read=42
29. 0.501 0.501 ↑ 1.0 20 1

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

  • Buffers: shared hit=152 read=12
30. 0.005 6.857 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,370 read=30
31. 6.852 6.852 ↑ 1.0 20 1

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

  • Buffers: shared hit=9,370 read=30
32. 0.001 0.018 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.03 rows=1 width=12) (actual time=0.017..0.018 rows=1 loops=1)

  • Buffers: shared hit=4
33. 0.017 0.017 ↑ 1.0 1 1

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

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

Subquery Scan on *SELECT* 4 (cost=0.00..0.65 rows=20 width=12) (actual time=0.230..0.349 rows=20 loops=1)

  • Buffers: shared hit=91
35. 0.345 0.345 ↑ 1.0 20 1

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

  • Buffers: shared hit=91
36. 420.074 1,154.995 ↑ 62,800.4 20 1

Sort (cost=81,351.66..84,491.69 rows=1,256,009 width=359) (actual time=1,154.994..1,154.995 rows=20 loops=1)

  • Sort Key: _combined.source_order
  • Sort Method: top-N heapsort Memory: 31kB
  • Buffers: shared hit=40,274 read=42
37. 498.682 734.921 ↑ 1.0 1,255,991 1

Hash Left Join (cost=1.98..47,929.72 rows=1,256,009 width=359) (actual time=7.833..734.921 rows=1,255,991 loops=1)

  • Hash Cond: (users.id = _combined.user_id)
  • Buffers: shared hit=40,274 read=42
38. 228.423 228.423 ↑ 1.0 1,255,991 1

Seq Scan on users (cost=0.00..43,217.09 rows=1,256,009 width=355) (actual time=0.009..228.423 rows=1,255,991 loops=1)

  • Buffers: shared hit=30,657
39. 0.016 7.816 ↑ 1.0 61 1

Hash (cost=1.22..1.22 rows=61 width=12) (actual time=7.816..7.816 rows=61 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=9,617 read=42
40. 7.800 7.800 ↑ 1.0 61 1

CTE Scan on _combined (cost=0.00..1.22 rows=61 width=12) (actual time=7.781..7.800 rows=61 loops=1)

  • Buffers: shared hit=9,617 read=42
Planning time : 1.726 ms
Execution time : 1,155.415 ms