explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VqRC

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 12.353 ↑ 1.1 27 1

Limit (cost=99,899.18..100,219.95 rows=30 width=516) (actual time=11.858..12.353 rows=27 loops=1)

  • Buffers: shared hit=4,921
2. 0.016 12.350 ↑ 15.6 27 1

Nested Loop Left Join (cost=99,899.18..104,411.28 rows=422 width=516) (actual time=11.857..12.350 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=4,921
3. 0.003 12.280 ↑ 15.7 27 1

Nested Loop Anti Join (cost=99,898.90..104,223.29 rows=424 width=516) (actual time=11.849..12.280 rows=27 loops=1)

  • Buffers: shared hit=4,870
4. 0.025 12.142 ↑ 15.9 27 1

Nested Loop (cost=99,898.61..103,972.76 rows=428 width=516) (actual time=11.840..12.142 rows=27 loops=1)

  • Buffers: shared hit=4,721
5. 0.011 12.090 ↑ 23.5 27 1

Nested Loop (cost=99,898.18..102,680.34 rows=634 width=524) (actual time=11.836..12.090 rows=27 loops=1)

  • Buffers: shared hit=4,613
6. 0.034 11.863 ↑ 46.9 27 1

Merge Anti Join (cost=99,897.76..100,037.88 rows=1,267 width=524) (actual time=11.820..11.863 rows=27 loops=1)

  • Merge Cond: (post.id = post_downrates.post_id)
  • Buffers: shared hit=4,505
7. 0.028 4.930 ↑ 48.4 27 1

Sort (cost=97,424.50..97,427.77 rows=1,307 width=524) (actual time=4.929..4.930 rows=27 loops=1)

  • Sort Key: post.id DESC
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=4,256
8. 0.124 4.902 ↑ 48.4 27 1

Nested Loop (cost=624.87..97,356.85 rows=1,307 width=524) (actual time=2.171..4.902 rows=27 loops=1)

  • Buffers: shared hit=4,256
9. 0.322 2.117 ↓ 2.9 887 1

HashAggregate (cost=311.84..314.85 rows=301 width=8) (actual time=2.014..2.117 rows=887 loops=1)

  • Group Key: CASE WHEN (uc.user_a_id = 8) THEN uc.user_b_id ELSE uc.user_a_id END
  • Buffers: shared hit=698
10. 1.597 1.795 ↓ 2.9 887 1

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

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=694
  • Buffers: shared hit=698
11. 0.198 0.198 ↓ 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.198..0.198 rows=887 loops=1)

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

Index Scan using index_post_on_author_id_and_created_at on post (cost=313.02..322.39 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=887)

  • Index Cond: ((author_id = CASE WHEN (uc.user_a_id = 8) THEN uc.user_b_id ELSE uc.user_a_id END) AND (created_at > '2020-08-24 12:18:06.012593'::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 = 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 ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (author_id = 8)) AND ((image_id IS NULL) OR (alternatives: SubPlan 6 or hashed SubPlan 7)) 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: 0
  • Buffers: shared hit=3,558
13.          

SubPlan (for Index Scan)

14. 0.138 0.138 ↓ 0.0 0 138

Index Only Scan using index_users_active on users u_1 (cost=0.42..2.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=138)

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 62
  • Buffers: shared hit=476
15. 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
16. 0.152 0.152 ↑ 1.0 1 38

Index Scan using stored_image_pkey on stored_image si (cost=0.42..2.45 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=38)

  • Index Cond: (id = post.image_id)
  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
  • Buffers: shared hit=152
17. 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[]))
18. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on user_connection uc_1 (cost=7.33..311.84 rows=301 width=4) (never executed)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
19. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
20. 0.070 0.070 ↓ 0.0 0 35

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) (actual time=0.002..0.002 rows=0 loops=35)

  • Index Cond: ((user_id = 8) AND (group_id = post.group_id))
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Buffers: shared hit=108
21. 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_1 (cost=0.43..5.71 rows=5 width=8) (never executed)

  • Index Cond: (user_id = 8)
  • Filter: (status = 'joined'::pages_group_membership_status)
22. 4.358 6.899 ↑ 72.2 369 1

Sort (cost=2,473.25..2,539.84 rows=26,636 width=8) (actual time=6.885..6.899 rows=369 loops=1)

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

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

  • Buffers: shared hit=249
24. 0.216 0.216 ↑ 1.0 1 27

Index Scan using index_pages_content_analysis_on_post_id on pages_content_analysis (cost=0.42..2.09 rows=1 width=8) (actual time=0.008..0.008 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
25. 0.027 0.027 ↑ 1.0 1 27

Index Only Scan using index_users_active on users u (cost=0.42..2.04 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=27)

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 27
  • Buffers: shared hit=108
26. 0.135 0.135 ↓ 0.0 0 27

Index Scan using index_user_event_blocks_on_suggested_user_id on user_event_blocks pe (cost=0.29..0.59 rows=1 width=4) (actual time=0.005..0.005 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. 0.054 0.054 ↑ 1.0 1 27

Index Scan using pages_groups_pkey on pages_groups (cost=0.29..0.43 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 : 7.528 ms
Execution time : 12.689 ms