explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W9y6 : Optimization for: Optimization for: plan #R6AQ; plan #eams

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.279 4.455 ↓ 1.6 1,367 1

Nested Loop Left Join (cost=55.53..14,378.29 rows=858 width=374) (actual time=0.417..4.455 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,642
2. 0.423 3.176 ↓ 1.6 1,383 1

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

  • Join Filter: (pe_1.suggested_user_id = post.author_id)
  • Buffers: shared hit=304
3. 0.559 2.753 ↓ 1.6 1,383 1

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

  • Join Filter: (pe.suggested_user_id = post.author_id)
  • Buffers: shared hit=303
4. 1.838 2.194 ↓ 1.5 1,383 1

Bitmap Heap Scan on post (cost=55.25..13,675.52 rows=906 width=374) (actual time=0.386..2.194 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,612
  • Heap Blocks: exact=140
  • Buffers: shared hit=302
5. 0.001 0.230 ↓ 0.0 0 1

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

  • Buffers: shared hit=71
6. 0.203 0.203 ↓ 1.0 2,398 1

Bitmap Index Scan on index_post_on_category_id (cost=0.00..39.81 rows=2,361 width=0) (actual time=0.203..0.203 rows=2,398 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=63
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.096 0.096 ↓ 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.021..0.096 rows=130 loops=1)

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

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

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=3
12. 0.006 0.006 ↓ 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.006..0.006 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.024 0.024 ↓ 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.020..0.024 rows=7 loops=1)

  • Index Cond: (user_id = 8)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Buffers: shared hit=11
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.007 0.007 ↓ 0.0 0 1

Seq Scan on user_event_blocks pe (cost=0.00..1.68 rows=1 width=4) (actual time=0.007..0.007 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 : 3.480 ms
Execution time : 4.917 ms