explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eams : Optimization for: plan #R6AQ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.065 5.726 ↓ 1.6 1,367 1

Nested Loop Left Join (cost=55.53..14,378.29 rows=858 width=374) (actual time=2.305..5.726 rows=1,367 loops=1)

  • Filter: ((pages_groups.status IS NULL) OR (pages_groups.status = 'active'::group_status) OR (post.author_id = 8))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=1,636
2. 0.447 4.661 ↓ 1.6 1,383 1

Nested Loop Anti Join (cost=55.25..13,705.70 rows=859 width=374) (actual time=2.299..4.661 rows=1,383 loops=1)

  • Join Filter: (pe_1.suggested_user_id = post.author_id)
  • Buffers: shared hit=298
3. 0.438 4.214 ↓ 1.6 1,383 1

Nested Loop Anti Join (cost=55.25..13,690.79 rows=882 width=374) (actual time=2.292..4.214 rows=1,383 loops=1)

  • Join Filter: (pe.suggested_user_id = post.author_id)
  • Buffers: shared hit=297
4. 1.550 3.776 ↓ 1.5 1,383 1

Bitmap Heap Scan on post (cost=55.25..13,675.52 rows=906 width=374) (actual time=2.275..3.776 rows=1,383 loops=1)

  • Recheck Cond: ((category_id = ANY ('{23,25,26,1,16,6,15,9,10,8,2,3,5,7,4,18,19,20,21,22,17,24}'::integer[])) OR (category_id IS NULL))
  • Filter: (((status = 'active'::post_status) OR ((status = ANY ('{censored,hidden}'::post_status[])) AND (author_id = 8))) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (author_id = 8)) AND (public OR (author_id = 8) OR ((group_id IS NULL) AND (hashed SubPlan 3)) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))
  • Rows Removed by Filter: 1,611
  • Heap Blocks: exact=140
  • Buffers: shared hit=296
5. 0.001 2.152 ↓ 0.0 0 1

BitmapOr (cost=51.23..51.23 rows=2,985 width=0) (actual time=2.152..2.152 rows=0 loops=1)

  • Buffers: shared hit=68
6. 2.125 2.125 ↓ 1.0 2,397 1

Bitmap Index Scan on index_post_on_category_id (cost=0.00..39.81 rows=2,361 width=0) (actual time=2.125..2.125 rows=2,397 loops=1)

  • Index Cond: (category_id = ANY ('{23,25,26,1,16,6,15,9,10,8,2,3,5,7,4,18,19,20,21,22,17,24}'::integer[]))
  • Buffers: shared hit=60
7. 0.026 0.026 ↓ 1.1 676 1

Bitmap Index Scan on index_post_on_category_id (cost=0.00..10.96 rows=624 width=0) (actual time=0.026..0.026 rows=676 loops=1)

  • Index Cond: (category_id IS NULL)
  • Buffers: shared hit=8
8.          

SubPlan (for Bitmap Heap Scan)

9. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_users_active on users u (cost=0.14..2.16 rows=1 width=0) (never executed)

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 0
10. 0.053 0.053 ↓ 3.2 130 1

Index Only Scan using index_users_active on users u_1 (cost=0.14..4.91 rows=40 width=4) (actual time=0.011..0.053 rows=130 loops=1)

  • Heap Fetches: 130
  • Buffers: shared hit=77
11. 0.004 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on user_connection uc (cost=3.00..4.02 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=3
12. 0.007 0.007 ↓ 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..3.00 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)

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

Index Scan using index_pages_group_memberships_on_user_id_and_group_id on pages_group_memberships pgm (cost=0.28..2.30 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 8) AND (group_id = post.group_id))
  • Filter: (status = 'joined'::pages_group_membership_status)
14. 0.010 0.010 ↓ 1.4 7 1

Index Scan using index_pages_group_memberships_on_user_id_and_group_id on pages_group_memberships pgm_1 (cost=0.28..7.66 rows=5 width=8) (actual time=0.006..0.010 rows=7 loops=1)

  • Index Cond: (user_id = 8)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Buffers: shared hit=8
15. 0.000 0.000 ↓ 0.0 0 1,383

Materialize (cost=0.00..1.68 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,383)

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

Seq Scan on user_event_blocks pe (cost=0.00..1.68 rows=1 width=4) (actual time=0.011..0.012 rows=0 loops=1)

  • Filter: (user_id = 8)
  • Rows Removed by Filter: 57
  • Buffers: shared hit=1
17. 0.000 0.000 ↓ 0.0 0 1,383

Materialize (cost=0.00..1.68 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,383)

  • Buffers: shared hit=1
18. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on user_event_blocks pe_1 (cost=0.00..1.68 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (user_id = 8)
  • Rows Removed by Filter: 57
  • Buffers: shared hit=1
19. 0.000 0.000 ↓ 0.0 0 1,383

Index Scan using pages_groups_pkey on pages_groups (cost=0.28..0.77 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1,383)

  • Index Cond: (post.group_id = id)
  • Buffers: shared hit=1,338
Planning time : 1.140 ms
Execution time : 5.973 ms