explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WNlZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.808 249.917 ↑ 76.3 34 1

GroupAggregate (cost=7.03..5,207.71 rows=2,595 width=16) (actual time=1.587..249.917 rows=34 loops=1)

  • Group Key: post.group_id
  • Buffers: shared hit=251,617
2. 5.015 247.109 ↓ 5.2 13,509 1

Nested Loop (cost=7.03..5,168.78 rows=2,595 width=16) (actual time=0.128..247.109 rows=13,509 loops=1)

  • Buffers: shared hit=251,617
3. 21.972 214.300 ↓ 3.6 13,897 1

Merge Left Join (cost=6.61..3,237.16 rows=3,840 width=20) (actual time=0.118..214.300 rows=13,897 loops=1)

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

Nested Loop (cost=0.85..3,196.36 rows=11,520 width=28) (actual time=0.030..192.237 rows=92,982 loops=1)

  • Buffers: shared hit=196,330
5. 0.067 0.067 ↓ 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.015..0.067 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. 132.686 184.464 ↓ 19.3 2,214 42

Index Scan using index_post_on_group_id on post (cost=0.42..636.98 rows=115 width=28) (actual time=0.007..4.392 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.019 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.072 0.072 ↓ 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.072 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.323 ms
Execution time : 249.995 ms