explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iX7q

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2.830 ↓ 0.0 0 1

Limit (cost=110.06..110.08 rows=8 width=350) (actual time=2.830..2.830 rows=0 loops=1)

  • Buffers: shared hit=4 read=8
2.          

CTE _preferred_users

3. 0.001 2.470 ↓ 0.0 0 1

Limit (cost=84.99..85.00 rows=4 width=12) (actual time=2.469..2.470 rows=0 loops=1)

  • Buffers: shared hit=4 read=7
4. 0.007 2.469 ↓ 0.0 0 1

Sort (cost=84.99..85.00 rows=4 width=12) (actual time=2.469..2.469 rows=0 loops=1)

  • Sort Key: (0)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4 read=7
5. 0.001 2.462 ↓ 0.0 0 1

HashAggregate (cost=84.91..84.95 rows=4 width=12) (actual time=2.462..2.462 rows=0 loops=1)

  • Group Key: ((p.author_id)::bigint), (0)
  • Buffers: shared hit=4 read=7
6. 0.001 2.461 ↓ 0.0 0 1

Append (cost=67.95..84.89 rows=4 width=12) (actual time=2.461..2.461 rows=0 loops=1)

  • Buffers: shared hit=4 read=7
7. 0.001 2.178 ↓ 0.0 0 1

HashAggregate (cost=67.95..67.99 rows=3 width=12) (actual time=2.178..2.178 rows=0 loops=1)

  • Group Key: p.author_id, (0)
  • Buffers: shared hit=4 read=5
8. 0.002 2.177 ↓ 0.0 0 1

Append (cost=0.28..67.93 rows=3 width=8) (actual time=2.177..2.177 rows=0 loops=1)

  • Buffers: shared hit=4 read=5
9. 0.646 0.646 ↓ 0.0 0 1

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

  • Index Cond: (id = 758,082)
  • Buffers: shared hit=3 read=2
10. 0.000 0.442 ↓ 0.0 0 1

Subquery Scan on _commenters (cost=20.78..39.01 rows=1 width=8) (actual time=0.442..0.442 rows=0 loops=1)

  • Buffers: shared read=2
11. 0.001 0.442 ↓ 0.0 0 1

Limit (cost=20.78..39.00 rows=1 width=4) (actual time=0.442..0.442 rows=0 loops=1)

  • Buffers: shared read=2
12. 0.001 0.441 ↓ 0.0 0 1

Hash Join (cost=20.78..39.00 rows=1 width=4) (actual time=0.441..0.441 rows=0 loops=1)

  • Hash Cond: (c.author_id = u.id)
  • Buffers: shared read=2
13. 0.004 0.440 ↓ 0.0 0 1

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

  • Recheck Cond: (post_id = 758,082)
  • Buffers: shared read=2
14. 0.436 0.436 ↓ 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.436..0.436 rows=0 loops=1)

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

Hash (cost=15.95..15.95 rows=40 width=4) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on users u (cost=4.35..15.95 rows=40 width=4) (never executed)

  • Recheck Cond: ((completed_reg_at IS NOT NULL) AND (account_status = 'active'::user_account_status))
  • Filter: (id <> 8)
17. 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)

18. 0.001 1.087 ↓ 0.0 0 1

Subquery Scan on _connections (cost=8.15..20.59 rows=1 width=8) (actual time=1.087..1.087 rows=0 loops=1)

  • Buffers: shared hit=1 read=1
19. 0.001 1.086 ↓ 0.0 0 1

Limit (cost=8.15..20.58 rows=1 width=4) (actual time=1.086..1.086 rows=0 loops=1)

  • Buffers: shared hit=1 read=1
20. 0.000 1.085 ↓ 0.0 0 1

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

  • Buffers: shared hit=1 read=1
21. 0.004 1.085 ↓ 0.0 0 1

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

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=1 read=1
22. 1.081 1.081 ↓ 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=1.080..1.081 rows=0 loops=1)

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

Index Only Scan using index_users_active on users u_1 (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)
  • Heap Fetches: 0
24. 0.000 0.282 ↓ 0.0 0 1

Subquery Scan on _group_members (cost=0.42..16.87 rows=1 width=12) (actual time=0.282..0.282 rows=0 loops=1)

  • Buffers: shared read=2
25. 0.001 0.282 ↓ 0.0 0 1

Limit (cost=0.42..16.86 rows=1 width=8) (actual time=0.282..0.282 rows=0 loops=1)

  • Buffers: shared read=2
26. 0.001 0.281 ↓ 0.0 0 1

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

  • Buffers: shared read=2
27. 0.280 0.280 ↓ 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.280..0.280 rows=0 loops=1)

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

Index Only Scan using index_users_active on users u_2 (cost=0.14..8.16 rows=1 width=4) (never executed)

  • Index Cond: (id = pgm.user_id)
  • Filter: (id <> 8)
  • Heap Fetches: 0
29. 0.024 2.829 ↓ 0.0 0 1

Sort (cost=25.06..25.08 rows=8 width=350) (actual time=2.828..2.829 rows=0 loops=1)

  • Sort Key: _result.source_order
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4 read=8
30. 0.000 2.805 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.72..24.94 rows=8 width=350) (actual time=2.805..2.805 rows=0 loops=1)

  • Join Filter: (user_event_blocks.suggested_user_id = _result.id)
  • Buffers: shared hit=4 read=8
31. 0.018 2.805 ↓ 0.0 0 1

Hash Join (cost=0.72..23.10 rows=8 width=358) (actual time=2.805..2.805 rows=0 loops=1)

  • Hash Cond: (users.id = _result.id)
  • Buffers: shared hit=4 read=8
32. 0.304 0.304 ↑ 822.0 1 1

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

  • Buffers: shared read=1
33. 0.001 2.483 ↓ 0.0 0 1

Hash (cost=0.62..0.62 rows=8 width=12) (actual time=2.483..2.483 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=4 read=7
34. 0.000 2.482 ↓ 0.0 0 1

Subquery Scan on _result (cost=0.52..0.62 rows=8 width=12) (actual time=2.482..2.482 rows=0 loops=1)

  • Buffers: shared hit=4 read=7
35. 0.001 2.482 ↓ 0.0 0 1

Limit (cost=0.52..0.54 rows=8 width=12) (actual time=2.482..2.482 rows=0 loops=1)

  • Buffers: shared hit=4 read=7
36. 0.008 2.481 ↓ 0.0 0 1

Sort (cost=0.52..0.54 rows=8 width=12) (actual time=2.481..2.481 rows=0 loops=1)

  • Sort Key: _preferred_users.source_order
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4 read=7
37. 0.001 2.473 ↓ 0.0 0 1

HashAggregate (cost=0.32..0.40 rows=8 width=12) (actual time=2.473..2.473 rows=0 loops=1)

  • Group Key: _preferred_users.id, _preferred_users.source_order
  • Buffers: shared hit=4 read=7
38. 0.000 2.472 ↓ 0.0 0 1

Append (cost=0.00..0.28 rows=8 width=12) (actual time=2.472..2.472 rows=0 loops=1)

  • Buffers: shared hit=4 read=7
39. 2.471 2.471 ↓ 0.0 0 1

CTE Scan on _preferred_users (cost=0.00..0.08 rows=4 width=12) (actual time=2.470..2.471 rows=0 loops=1)

  • Buffers: shared hit=4 read=7
40. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on _preferred_users _preferred_users_1 (cost=0.00..0.08 rows=4 width=12) (actual time=0.001..0.001 rows=0 loops=1)

41. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.72 rows=1 width=4) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_event_blocks (cost=0.00..1.71 rows=1 width=4) (never executed)

  • Filter: (user_id = 8)
Planning time : 93.057 ms
Execution time : 3.980 ms