explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n14B

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

Limit (cost=321.64..61,121.75 rows=30 width=516) (actual time=82.721..1,534.976 rows=27 loops=1)

  • Buffers: shared hit=660,317
2. 0.011 1,534.971 ↑ 40.3 27 1

Nested Loop Left Join (cost=321.64..2,207,365.49 rows=1,089 width=516) (actual time=82.720..1,534.971 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=660,317
3. 0.003 1,534.906 ↑ 40.6 27 1

Nested Loop Anti Join (cost=321.36..2,206,938.52 rows=1,096 width=516) (actual time=82.714..1,534.906 rows=27 loops=1)

  • Buffers: shared hit=660,266
4. 0.022 1,534.795 ↑ 41.0 27 1

Nested Loop Anti Join (cost=321.06..2,206,420.05 rows=1,106 width=516) (actual time=82.710..1,534.795 rows=27 loops=1)

  • Buffers: shared hit=660,117
5. 697.854 1,534.746 ↑ 42.3 27 1

Nested Loop Semi Join (cost=320.78..2,205,990.38 rows=1,142 width=516) (actual time=82.707..1,534.746 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=660,063
6. 4.915 513.660 ↓ 5.1 11,544 1

Nested Loop (cost=313.45..2,193,652.83 rows=2,283 width=516) (actual time=0.029..513.660 rows=11,544 loops=1)

  • Buffers: shared hit=659,365
7. 484.866 485.455 ↓ 2.6 11,645 1

Index Scan Backward using post_pkey on post (cost=313.02..2,184,761.00 rows=4,565 width=516) (actual time=0.013..485.455 rows=11,645 loops=1)

  • Filter: (_active_author AND _valid_image AND ((NOT _negative_content) OR (_negative_content IS NULL)) AND (created_at > '2020-08-24 12:20:59.042173'::timestamp without time zone) AND (_active_author OR (author_id = 8)) 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: 836,925
  • Buffers: shared hit=612,748
8.          

SubPlan (for Index Scan Backward)

9. 0.430 0.515 ↓ 2.9 887 1

Bitmap Heap Scan on user_connection uc_1 (cost=7.33..311.84 rows=301 width=4) (actual time=0.135..0.515 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.074 0.074 ↓ 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.074 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. 321.757 323.232 ↓ 2.9 887 11,544

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

  • Buffers: shared hit=698
15. 1.321 1.475 ↓ 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.475 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.154 0.154 ↓ 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.154..0.154 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 : 3.013 ms
Execution time : 1,535.319 ms