explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v2bA

Settings
# exclusive inclusive rows x rows loops node
1. 1.776 61.319 ↓ 1.6 35 1

GroupAggregate (cost=1.71..242.49 rows=22 width=16) (actual time=1.370..61.319 rows=35 loops=1)

  • Group Key: post.group_id
  • Buffers: shared hit=84,468
2. 1.625 59.543 ↓ 614.3 13,514 1

Nested Loop (cost=1.71..242.16 rows=22 width=16) (actual time=0.050..59.543 rows=13,514 loops=1)

  • Buffers: shared hit=84,468
3. 1.185 30.114 ↓ 421.3 13,902 1

Nested Loop (cost=1.28..224.39 rows=33 width=20) (actual time=0.039..30.114 rows=13,902 loops=1)

  • Buffers: shared hit=29,209
4. 0.028 0.117 ↓ 42.0 42 1

Merge Join (cost=0.86..11.44 rows=1 width=32) (actual time=0.021..0.117 rows=42 loops=1)

  • Merge Cond: (pgm.group_id = pages_group_memberships.group_id)
  • Buffers: shared hit=96
5. 0.063 0.063 ↓ 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=24) (actual time=0.013..0.063 rows=45 loops=1)

  • Index Cond: (user_id = 8)
  • Buffers: shared hit=48
6. 0.026 0.026 ↓ 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.005..0.026 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
7. 22.892 28.812 ↓ 13.8 331 42

Index Scan using index_post_on_group_id_and_created_at on post (cost=0.42..212.72 rows=24 width=28) (actual time=0.008..0.686 rows=331 loops=42)

  • Index Cond: ((group_id = pgm.group_id) AND (created_at > COALESCE(pgm.last_opened_at, pgm.created_at)))
  • Filter: ((status = 'active'::post_status) AND ((image_id IS NULL) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 95
  • Buffers: shared hit=29,113
8.          

SubPlan (for Index Scan)

9. 5.920 5.920 ↑ 1.0 1 2,960

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=2,960)

  • Index Cond: (id = post.image_id)
  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=11,846
10. 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[]))
11. 27.804 27.804 ↑ 1.0 1 13,902

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

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 13,514
  • Buffers: shared hit=55,259
Planning time : 1.503 ms
Execution time : 61.394 ms