explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a3h5 : Optimization for: plan #B4sA

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 14.685 ↓ 30.0 30 1

Limit (cost=1,011.30..1,011.31 rows=1 width=516) (actual time=14.682..14.685 rows=30 loops=1)

  • Buffers: shared hit=14,431
2. 0.038 14.683 ↓ 30.0 30 1

Sort (cost=1,011.30..1,011.31 rows=1 width=516) (actual time=14.681..14.683 rows=30 loops=1)

  • Sort Key: post.id DESC
  • Sort Method: quicksort Memory: 40kB
  • Buffers: shared hit=14,431
3. 0.016 14.645 ↓ 43.0 43 1

Nested Loop Anti Join (cost=9.54..1,011.29 rows=1 width=516) (actual time=0.990..14.645 rows=43 loops=1)

  • Buffers: shared hit=14,431
4. 0.035 14.586 ↓ 43.0 43 1

Nested Loop Anti Join (cost=9.25..1,010.58 rows=1 width=516) (actual time=0.986..14.586 rows=43 loops=1)

  • Buffers: shared hit=14,345
5. 0.035 14.422 ↓ 43.0 43 1

Nested Loop Left Join (cost=8.96..1,009.36 rows=1 width=516) (actual time=0.979..14.422 rows=43 loops=1)

  • Filter: ((pages_groups.status IS NULL) OR (pages_groups.status = 'active'::group_status) OR (post.author_id = 274,178))
  • Buffers: shared hit=14,118
6. 0.035 14.301 ↓ 43.0 43 1

Nested Loop (cost=8.68..1,008.85 rows=1 width=516) (actual time=0.971..14.301 rows=43 loops=1)

  • Buffers: shared hit=14,013
7. 0.543 14.086 ↓ 15.0 45 1

Nested Loop (cost=8.25..1,002.28 rows=3 width=516) (actual time=0.955..14.086 rows=45 loops=1)

  • Buffers: shared hit=13,832
8. 0.802 8.191 ↓ 26.2 1,784 1

Nested Loop (cost=4.62..247.45 rows=68 width=8) (actual time=0.876..8.191 rows=1,784 loops=1)

  • Buffers: shared hit=8,027
9. 0.896 1.164 ↓ 20.8 2,075 1

HashAggregate (cost=4.20..5.20 rows=100 width=4) (actual time=0.864..1.164 rows=2,075 loops=1)

  • Group Key: unnest(_user_connections.user_ids)
  • Buffers: shared hit=8
10. 0.255 0.268 ↓ 20.8 2,075 1

ProjectSet (cost=0.42..2.95 rows=100 width=4) (actual time=0.035..0.268 rows=2,075 loops=1)

  • Buffers: shared hit=8
11. 0.013 0.013 ↑ 1.0 1 1

Index Scan using _user_connections_pkey on _user_connections (cost=0.42..2.44 rows=1 width=77) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (user_id = 274,178)
  • Buffers: shared hit=4
12. 6.225 6.225 ↑ 1.0 1 2,075

Index Only Scan using index_users_active on users u (cost=0.42..2.42 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,075)

  • Index Cond: (id = (unnest(_user_connections.user_ids)))
  • Heap Fetches: 1,784
  • Buffers: shared hit=8,019
13. 5.032 5.352 ↓ 0.0 0 1,784

Index Scan using index_post_on_author_id_and_created_at on post (cost=3.62..11.09 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=1,784)

  • Index Cond: ((author_id = u.id) AND (created_at > '2020-08-27 18:44:14.33897'::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: 0
  • Buffers: shared hit=5,805
14.          

SubPlan (for Index Scan)

15. 0.051 0.051 ↑ 1.0 1 51

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=1 loops=51)

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 51
  • Buffers: shared hit=206
16. 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
17. 0.076 0.076 ↑ 1.0 1 19

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=19)

  • Index Cond: (id = post.image_id)
  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
  • Buffers: shared hit=76
18. 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[]))
19. 0.174 0.181 ↓ 20.8 2,075 1

ProjectSet (cost=0.42..2.95 rows=100 width=4) (actual time=0.046..0.181 rows=2,075 loops=1)

  • Buffers: shared hit=8
20. 0.007 0.007 ↑ 1.0 1 1

Index Scan using _user_connections_pkey on _user_connections _user_connections_1 (cost=0.42..2.44 rows=1 width=77) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (user_id = 274,178)
  • Buffers: shared hit=4
21. 0.012 0.012 ↓ 0.0 0 6

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=6)

  • Index Cond: ((user_id = 274,178) AND (group_id = post.group_id))
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Buffers: shared hit=18
22. 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 = 274,178)
  • Filter: (status = 'joined'::pages_group_membership_status)
23. 0.180 0.180 ↑ 1.0 1 45

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.004..0.004 rows=1 loops=45)

  • 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=181
24. 0.086 0.086 ↑ 1.0 1 43

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

  • Index Cond: (post.group_id = id)
  • Buffers: shared hit=105
25. 0.129 0.129 ↓ 0.0 0 43

Index Scan using index_user_event_blocks_on_suggested_user_id on user_event_blocks pe (cost=0.29..0.75 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=43)

  • Index Cond: (suggested_user_id = post.author_id)
  • Filter: (user_id = 274,178)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=227
26. 0.043 0.043 ↓ 0.0 0 43

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

  • Index Cond: (post_id = post.id)
  • Heap Fetches: 0
  • Buffers: shared hit=86
Planning time : 6.156 ms
Execution time : 14.827 ms