explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ej4S

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,459.029 ↑ 1.1 27 1

Limit (cost=321.64..53,306.29 rows=30 width=516) (actual time=81.734..1,459.029 rows=27 loops=1)

  • Buffers: shared hit=585,662
2. 0.011 1,459.024 ↑ 40.3 27 1

Nested Loop Left Join (cost=321.64..1,923,664.57 rows=1,089 width=516) (actual time=81.733..1,459.024 rows=27 loops=1)

  • Filter: ((pages_groups.status IS NULL) OR (pages_groups.status = 'active'::group_status) OR (post.author_id = 8))
  • Buffers: shared hit=585,662
3. 0.003 1,458.959 ↑ 40.6 27 1

Nested Loop Anti Join (cost=321.36..1,923,237.60 rows=1,096 width=516) (actual time=81.727..1,458.959 rows=27 loops=1)

  • Buffers: shared hit=585,611
4. 0.022 1,458.848 ↑ 41.0 27 1

Nested Loop Anti Join (cost=321.06..1,922,719.13 rows=1,106 width=516) (actual time=81.722..1,458.848 rows=27 loops=1)

  • Buffers: shared hit=585,462
5. 706.057 1,458.799 ↑ 42.3 27 1

Nested Loop Semi Join (cost=320.78..1,922,289.46 rows=1,142 width=516) (actual time=81.719..1,458.799 rows=27 loops=1)

  • Join Filter: (post.author_id = CASE WHEN (uc.user_a_id = 8) THEN uc.user_b_id ELSE uc.user_a_id END)
  • Rows Removed by Join Filter: 10,236,155
  • Buffers: shared hit=585,408
6. 5.085 441.054 ↓ 5.1 11,544 1

Nested Loop (cost=313.45..1,909,951.91 rows=2,283 width=516) (actual time=0.032..441.054 rows=11,544 loops=1)

  • Buffers: shared hit=584,710
7. 412.094 412.679 ↓ 2.6 11,645 1

Index Scan Backward using tmp1 on post (cost=313.02..1,901,060.07 rows=4,565 width=516) (actual time=0.017..412.679 rows=11,645 loops=1)

  • Filter: (((NOT _negative_content) OR (_negative_content IS NULL)) AND (created_at > '2020-08-24 12:20:59.042173'::timestamp without time zone) 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 = 8))) AND ((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)) AND (public OR (author_id = 8) OR ((group_id IS NULL) AND (hashed SubPlan 1)) OR (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 726,745
  • Buffers: shared hit=538,093
8.          

SubPlan (for Index Scan Backward)

9. 0.427 0.512 ↓ 2.9 887 1

Bitmap Heap Scan on user_connection uc_1 (cost=7.33..311.84 rows=301 width=4) (actual time=0.134..0.512 rows=887 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=694
  • Buffers: shared hit=698
10. 0.085 0.085 ↓ 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.25 rows=301 width=0) (actual time=0.085..0.085 rows=887 loops=1)

  • Index Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=4
11. 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 = 8) AND (group_id = post.group_id))
  • Filter: (status = 'joined'::pages_group_membership_status)
12. 0.073 0.073 ↓ 8.4 42 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.073 rows=42 loops=1)

  • Index Cond: (user_id = 8)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=48
13. 23.290 23.290 ↑ 1.0 1 11,645

Index Scan using index_pages_content_analysis_on_post_id on pages_content_analysis (cost=0.42..1.95 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11,645)

  • 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=46,617
14. 310.191 311.688 ↓ 2.9 887 11,544

Materialize (cost=7.33..312.60 rows=301 width=8) (actual time=0.000..0.027 rows=887 loops=11,544)

  • Buffers: shared hit=698
15. 1.342 1.497 ↓ 2.9 887 1

Bitmap Heap Scan on user_connection uc (cost=7.33..311.09 rows=301 width=8) (actual time=0.246..1.497 rows=887 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=694
  • Buffers: shared hit=698
16. 0.155 0.155 ↓ 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.25 rows=301 width=0) (actual time=0.155..0.155 rows=887 loops=1)

  • Index Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=4
17. 0.027 0.027 ↓ 0.0 0 27

Index Only Scan using index_post_downrates_on_post_id on post_downrates (cost=0.29..0.38 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=27)

  • Index Cond: (post_id = post.id)
  • Heap Fetches: 0
  • Buffers: shared hit=54
18. 0.108 0.108 ↓ 0.0 0 27

Index Scan using index_user_event_blocks_on_suggested_user_id on user_event_blocks pe (cost=0.29..0.47 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=27)

  • Index Cond: (suggested_user_id = post.author_id)
  • Filter: (user_id = 8)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=149
19. 0.054 0.054 ↑ 1.0 1 27

Index Scan using pages_groups_pkey on pages_groups (cost=0.29..0.37 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=27)

  • Index Cond: (post.group_id = id)
  • Buffers: shared hit=51
Planning time : 2.856 ms
Execution time : 1,459.369 ms