explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qUjX

Settings
# exclusive inclusive rows x rows loops node
1. 2.795 238.234 ↑ 74.1 34 1

GroupAggregate (cost=7.03..5,115.86 rows=2,518 width=16) (actual time=1.553..238.234 rows=34 loops=1)

  • Group Key: post.group_id
  • Buffers: shared hit=251,617
2. 4.717 235.439 ↓ 5.4 13,509 1

Nested Loop (cost=7.03..5,078.09 rows=2,518 width=16) (actual time=0.128..235.439 rows=13,509 loops=1)

  • Buffers: shared hit=251,617
3. 10.452 202.928 ↓ 3.7 13,897 1

Merge Left Join (cost=6.61..3,202.52 rows=3,725 width=20) (actual time=0.117..202.928 rows=13,897 loops=1)

  • Merge Cond: (post.group_id = pgm.group_id)
  • Filter: (post.created_at > COALESCE(pgm.last_opened_at, ((CURRENT_TIMESTAMP)::timestamp without time zone - '15 days'::interval)))
  • Rows Removed by Filter: 79,085
  • Buffers: shared hit=196,378
4. 7.519 192.385 ↓ 8.3 92,982 1

Nested Loop (cost=0.85..3,162.91 rows=11,176 width=28) (actual time=0.029..192.385 rows=92,982 loops=1)

  • Buffers: shared hit=196,330
5. 0.066 0.066 ↓ 8.4 42 1

Index Scan using index_pages_group_memberships_on_user_id_and_group_id on pages_group_memberships (cost=0.43..5.71 rows=5 width=8) (actual time=0.014..0.066 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
6. 133.022 184.800 ↓ 19.6 2,214 42

Index Scan using index_post_on_group_id on post (cost=0.42..630.31 rows=113 width=28) (actual time=0.007..4.400 rows=2,214 loops=42)

  • Index Cond: (group_id = pages_group_memberships.group_id)
  • Filter: ((status = 'active'::post_status) AND ((image_id IS NULL) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 676
  • Buffers: shared hit=196,282
7.          

SubPlan (for Index Scan)

8. 51.778 51.778 ↑ 1.0 1 25,889

Index Scan using stored_image_pkey on stored_image si (cost=0.42..2.45 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=25,889)

  • Index Cond: (id = post.image_id)
  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=103,640
9. 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[]))
10. 0.018 0.091 ↓ 9.0 45 1

Sort (cost=5.75..5.77 rows=5 width=16) (actual time=0.084..0.091 rows=45 loops=1)

  • Sort Key: pgm.group_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=48
11. 0.073 0.073 ↓ 9.0 45 1

Index Scan using index_pages_group_memberships_on_user_id_and_group_id on pages_group_memberships pgm (cost=0.43..5.70 rows=5 width=16) (actual time=0.004..0.073 rows=45 loops=1)

  • Index Cond: (user_id = 8)
  • Buffers: shared hit=48
12. 27.794 27.794 ↑ 1.0 1 13,897

Index Only Scan using index_users_active on users u (cost=0.42..0.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13,897)

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 13,509
  • Buffers: shared hit=55,239
Planning time : 1.427 ms
Execution time : 238.306 ms