explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y9WN

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,228.400 ↓ 27.0 27 1

Limit (cost=155,630.12..155,630.13 rows=1 width=516) (actual time=1,228.396..1,228.400 rows=27 loops=1)

  • Buffers: shared hit=23,300
2. 0.035 1,228.397 ↓ 27.0 27 1

Sort (cost=155,630.12..155,630.13 rows=1 width=516) (actual time=1,228.395..1,228.397 rows=27 loops=1)

  • Sort Key: post.id DESC
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=23,300
3. 42.172 1,228.362 ↓ 27.0 27 1

Nested Loop Anti Join (cost=5,946.54..155,630.11 rows=1 width=516) (actual time=39.475..1,228.362 rows=27 loops=1)

  • Join Filter: (post_downrates.post_id = post.id)
  • Rows Removed by Join Filter: 719,172
  • Buffers: shared hit=23,300
4. 0.023 1,144.907 ↓ 27.0 27 1

Nested Loop Anti Join (cost=5,946.54..154,781.80 rows=1 width=516) (actual time=36.211..1,144.907 rows=27 loops=1)

  • Buffers: shared hit=16,577
5. 0.013 1,144.776 ↓ 27.0 27 1

Nested Loop (cost=5,946.25..154,781.16 rows=1 width=516) (actual time=36.199..1,144.776 rows=27 loops=1)

  • Buffers: shared hit=16,428
6. 8.620 1,144.628 ↓ 27.0 27 1

Nested Loop Left Join (cost=5,945.82..154,779.21 rows=1 width=516) (actual time=36.185..1,144.628 rows=27 loops=1)

  • Join Filter: (post.group_id = pages_groups.id)
  • Rows Removed by Join Filter: 160,212
  • Filter: ((pages_groups.status IS NULL) OR (pages_groups.status = 'active'::group_status) OR (post.author_id = 8))
  • Buffers: shared hit=16,320
7. 750.008 1,125.397 ↓ 27.0 27 1

Nested Loop Semi Join (cost=5,945.82..154,199.31 rows=1 width=516) (actual time=34.411..1,125.397 rows=27 loops=1)

  • Join Filter: (((uc.user_a_id = post.author_id) AND (uc.user_b_id = 8)) OR ((uc.user_b_id = post.author_id) AND (uc.user_a_id = 8)))
  • Rows Removed by Join Filter: 10,336,386
  • Buffers: shared hit=11,550
8. 18.862 48.993 ↓ 2.6 11,657 1

Bitmap Heap Scan on post (cost=5,942.47..147,862.93 rows=4,569 width=516) (actual time=30.417..48.993 rows=11,657 loops=1)

  • Recheck Cond: ((created_at > '2020-08-24 12:11:53.151098'::timestamp without time zone) AND _active_author AND _valid_image)
  • 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 = 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 (SubPlan 1)) OR (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 9,971
  • Heap Blocks: exact=8,025
  • Buffers: shared hit=10,847
9. 0.304 29.611 ↓ 0.0 0 1

BitmapAnd (cost=5,942.47..5,942.47 rows=19,604 width=0) (actual time=29.611..29.611 rows=0 loops=1)

  • Buffers: shared hit=2,084
10. 2.160 2.160 ↑ 1.0 22,420 1

Bitmap Index Scan on index_post_on_created_at (cost=0.00..231.77 rows=22,579 width=0) (actual time=2.160..2.160 rows=22,420 loops=1)

  • Index Cond: (created_at > '2020-08-24 12:11:53.151098'::timestamp without time zone)
  • Buffers: shared hit=64
11. 27.147 27.147 ↓ 1.0 738,390 1

Bitmap Index Scan on tmp1 (cost=0.00..5,708.17 rows=736,749 width=0) (actual time=27.147..27.147 rows=738,390 loops=1)

  • Buffers: shared hit=2,020
12.          

SubPlan (for Bitmap Heap Scan)

13. 0.000 0.460 ↓ 0.0 0 115

Bitmap Heap Scan on user_connection uc_1 (cost=2.89..3.91 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=115)

  • Recheck Cond: (((user_a_id = post.author_id) AND (user_b_id = 8)) OR ((user_a_id = 8) AND (user_b_id = post.author_id)))
  • Buffers: shared hit=690
14. 0.000 0.460 ↓ 0.0 0 115

BitmapOr (cost=2.89..2.89 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=115)

  • Buffers: shared hit=690
15. 0.345 0.345 ↓ 0.0 0 115

Bitmap Index Scan on index_user_connection_on_user_a_id_and_user_b_id (cost=0.00..1.44 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=115)

  • Index Cond: ((user_a_id = post.author_id) AND (user_b_id = 8))
  • Buffers: shared hit=345
16. 0.115 0.115 ↓ 0.0 0 115

Bitmap Index Scan on index_user_connection_on_user_a_id_and_user_b_id (cost=0.00..1.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=115)

  • Index Cond: ((user_a_id = 8) AND (user_b_id = post.author_id))
  • Buffers: shared hit=345
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 = 8) AND (group_id = post.group_id))
  • Filter: (status = 'joined'::pages_group_membership_status)
18. 0.060 0.060 ↓ 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.011..0.060 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
19. 325.333 326.396 ↓ 14.5 887 11,657

Materialize (cost=3.35..65.57 rows=61 width=8) (actual time=0.000..0.028 rows=887 loops=11,657)

  • Buffers: shared hit=703
20. 0.992 1.063 ↓ 14.5 887 1

Bitmap Heap Scan on user_connection uc (cost=3.35..65.27 rows=61 width=8) (actual time=0.124..1.063 rows=887 loops=1)

  • Recheck Cond: ((user_b_id = 8) OR (user_a_id = 8))
  • Heap Blocks: exact=694
  • Buffers: shared hit=703
21. 0.001 0.071 ↓ 0.0 0 1

BitmapOr (cost=3.35..3.35 rows=61 width=0) (actual time=0.071..0.071 rows=0 loops=1)

  • Buffers: shared hit=9
22. 0.009 0.009 ↑ 3.3 6 1

Bitmap Index Scan on index_user_connection_on_user_b_id (cost=0.00..1.58 rows=20 width=0) (actual time=0.009..0.009 rows=6 loops=1)

  • Index Cond: (user_b_id = 8)
  • Buffers: shared hit=3
23. 0.061 0.061 ↓ 21.5 881 1

Bitmap Index Scan on index_user_connection_on_user_a_id_and_user_b_id (cost=0.00..1.74 rows=41 width=0) (actual time=0.061..0.061 rows=881 loops=1)

  • Index Cond: (user_a_id = 8)
  • Buffers: shared hit=6
24. 10.611 10.611 ↑ 1.7 5,934 27

Seq Scan on pages_groups (cost=0.00..400.51 rows=10,251 width=12) (actual time=0.001..0.393 rows=5,934 loops=27)

  • Buffers: shared hit=4,770
25. 0.135 0.135 ↑ 1.0 1 27

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.005..0.005 rows=1 loops=27)

  • Index Cond: (post_id = post.id)
  • Filter: COALESCE(((google_comment_analyzer_payload -> 'severe_toxicity'::text) < '0.55'::jsonb), true)
  • Buffers: shared hit=108
26. 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
27. 41.283 41.283 ↑ 1.0 26,636 27

Seq Scan on post_downrates (cost=0.00..515.36 rows=26,636 width=8) (actual time=0.002..1.529 rows=26,636 loops=27)

  • Buffers: shared hit=6,723
Planning time : 3.759 ms
Execution time : 1,229.140 ms