explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B4sA

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 957.343 ↑ 1.0 30 1

Limit (cost=121,143.23..124,747.88 rows=30 width=516) (actual time=85.431..957.343 rows=30 loops=1)

  • Buffers: shared hit=102,131
2. 0.019 957.338 ↑ 8.8 30 1

Nested Loop Anti Join (cost=121,143.23..152,864.17 rows=264 width=516) (actual time=85.430..957.338 rows=30 loops=1)

  • Join Filter: (pe.suggested_user_id = post.author_id)
  • Buffers: shared hit=102,131
3. 606.231 957.319 ↑ 8.8 30 1

Nested Loop Semi Join (cost=121,142.93..152,844.56 rows=264 width=516) (actual time=85.422..957.319 rows=30 loops=1)

  • Join Filter: (post.author_id = CASE WHEN (uc.user_a_id = 274,178) THEN uc.user_b_id ELSE uc.user_a_id END)
  • Rows Removed by Join Filter: 8,915,576
  • Buffers: shared hit=102,129
4. 1.825 75.312 ↓ 8.1 4,309 1

Nested Loop Left Join (cost=121,118.14..126,681.53 rows=529 width=520) (actual time=44.327..75.312 rows=4,309 loops=1)

  • Filter: ((pages_groups.status IS NULL) OR (pages_groups.status = 'active'::group_status) OR (post.author_id = 274,178))
  • Buffers: shared hit=100,179
5. 2.755 69.178 ↓ 8.1 4,309 1

Nested Loop (cost=121,117.85..126,404.45 rows=533 width=520) (actual time=44.318..69.178 rows=4,309 loops=1)

  • Buffers: shared hit=89,174
6. 2.332 57.805 ↓ 5.5 4,309 1

Nested Loop (cost=121,117.43..124,706.08 rows=788 width=516) (actual time=44.313..57.805 rows=4,309 loops=1)

  • Buffers: shared hit=71,928
7. 1.755 46.781 ↓ 2.8 4,346 1

Merge Anti Join (cost=121,117.00..121,258.82 rows=1,576 width=516) (actual time=44.298..46.781 rows=4,346 loops=1)

  • Merge Cond: (post.id = post_downrates.post_id)
  • Buffers: shared hit=54,528
8. 2.395 39.211 ↓ 2.7 4,399 1

Sort (cost=118,643.74..118,647.81 rows=1,626 width=516) (actual time=38.489..39.211 rows=4,399 loops=1)

  • Sort Key: post.id DESC
  • Sort Method: quicksort Memory: 3,929kB
  • Buffers: shared hit=54,279
9. 15.621 36.816 ↓ 4.3 7,062 1

Index Scan using index_post_on_created_at on post (cost=2,516.94..118,557.02 rows=1,626 width=516) (actual time=0.037..36.816 rows=7,062 loops=1)

  • Index Cond: (created_at > '2020-08-27 18:38:59.766065'::timestamp without time zone)
  • Filter: (((NOT _negative_content) OR (_negative_content IS NULL)) AND (posted_as <> 'incognito'::pages_posted_as_type) AND (status = 'active'::post_status) AND ((status = 'active'::post_status) OR ((status = ANY ('{censored,hidden}'::post_status[])) AND (author_id = 274,178))) AND ((category_id = ANY ('{23,25,26,1,16,6,15,9,10,8,2,3,5,7,4,18,19,20,21,22,24}'::integer[])) OR (category_id IS NULL)) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (author_id = 274,178)) AND ((image_id IS NULL) OR (alternatives: SubPlan 6 or hashed SubPlan 7)) AND (public OR (author_id = 274,178) OR ((group_id IS NULL) AND (hashed SubPlan 3)) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))
  • Rows Removed by Filter: 6,614
  • Buffers: shared hit=54,279
10.          

SubPlan (for Index Scan)

11. 15.266 15.266 ↑ 1.0 1 7,633

Index Only Scan using index_users_active on users u_1 (cost=0.42..2.44 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=7,633)

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 7,459
  • Buffers: shared hit=30,373
12. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_users_active on users u_2 (cost=0.42..41,890.92 rows=829,141 width=4) (never executed)

  • Heap Fetches: 0
13. 2.216 2.216 ↑ 1.0 1 2,216

Index Scan using stored_image_pkey on stored_image si (cost=0.42..2.45 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=2,216)

  • Index Cond: (id = post.image_id)
  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=8,876
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on stored_image si_1 (cost=0.00..23,176.86 rows=1,008,339 width=4) (never executed)

  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
15. 3.239 3.668 ↑ 1.2 2,075 1

Bitmap Heap Scan on user_connection uc_1 (cost=24.80..2,510.12 rows=2,555 width=4) (actual time=0.712..3.668 rows=2,075 loops=1)

  • Recheck Cond: ('{274178}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=1,945
  • Buffers: shared hit=1,950
16. 0.429 0.429 ↑ 1.2 2,075 1

Bitmap Index Scan on "index_user_connection_on_ARRAY_user_a_id_user_b_id_gin__int_ops" (cost=0.00..24.16 rows=2,555 width=0) (actual time=0.429..0.429 rows=2,075 loops=1)

  • Index Cond: ('{274178}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=5
17. 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.43..2.45 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 274,178) AND (group_id = post.group_id))
  • Filter: (status = 'joined'::pages_group_membership_status)
18. 0.045 0.045 ↓ 2.4 12 1

Index Scan using index_pages_group_memberships_on_user_id_and_group_id on pages_group_memberships pgm_1 (cost=0.43..5.71 rows=5 width=8) (actual time=0.010..0.045 rows=12 loops=1)

  • Index Cond: (user_id = 274,178)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=24
19. 3.648 5.815 ↑ 211.4 126 1

Sort (cost=2,473.25..2,539.84 rows=26,636 width=8) (actual time=5.804..5.815 rows=126 loops=1)

  • Sort Key: post_downrates.post_id DESC
  • Sort Method: quicksort Memory: 2,017kB
  • Buffers: shared hit=249
20. 2.167 2.167 ↑ 1.0 26,636 1

Seq Scan on post_downrates (cost=0.00..515.36 rows=26,636 width=8) (actual time=0.008..2.167 rows=26,636 loops=1)

  • Buffers: shared hit=249
21. 8.692 8.692 ↑ 1.0 1 4,346

Index Scan using index_pages_content_analysis_on_post_id on pages_content_analysis (cost=0.42..2.19 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,346)

  • Index Cond: (post_id = post.id)
  • Filter: COALESCE(((google_comment_analyzer_payload -> 'severe_toxicity'::text) < '0.55'::jsonb), true)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=17,400
22. 8.618 8.618 ↑ 1.0 1 4,309

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

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 4,309
  • Buffers: shared hit=17,246
23. 4.309 4.309 ↑ 1.0 1 4,309

Index Scan using pages_groups_pkey on pages_groups (cost=0.29..0.50 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=4,309)

  • Index Cond: (post.group_id = id)
  • Buffers: shared hit=11,005
24. 273.020 275.776 ↑ 1.2 2,069 4,309

Materialize (cost=24.80..2,516.51 rows=2,555 width=8) (actual time=0.000..0.064 rows=2,069 loops=4,309)

  • Buffers: shared hit=1,950
25. 2.508 2.756 ↑ 1.2 2,075 1

Bitmap Heap Scan on user_connection uc (cost=24.80..2,503.74 rows=2,555 width=8) (actual time=0.422..2.756 rows=2,075 loops=1)

  • Recheck Cond: ('{274178}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=1,945
  • Buffers: shared hit=1,950
26. 0.248 0.248 ↑ 1.2 2,075 1

Bitmap Index Scan on "index_user_connection_on_ARRAY_user_a_id_user_b_id_gin__int_ops" (cost=0.00..24.16 rows=2,555 width=0) (actual time=0.248..0.248 rows=2,075 loops=1)

  • Index Cond: ('{274178}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=5
27. 0.000 0.000 ↓ 0.0 0 30

Materialize (cost=0.29..3.78 rows=4 width=4) (actual time=0.000..0.000 rows=0 loops=30)

  • Buffers: shared hit=2
28. 0.006 0.006 ↓ 0.0 0 1

Index Scan using index_user_event_blocks_on_user_id on user_event_blocks pe (cost=0.29..3.76 rows=4 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (user_id = 274,178)
  • Buffers: shared hit=2
Planning time : 5.016 ms
Execution time : 957.793 ms