explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VE25

Settings
# exclusive inclusive rows x rows loops node
1. 0.482 15.352 ↑ 7.2 20 1

GroupAggregate (cost=1.29..393.47 rows=145 width=16) (actual time=0.094..15.352 rows=20 loops=1)

  • Group Key: post.group_id
  • Buffers: shared hit=16,088
2. 0.185 14.870 ↓ 17.9 2,596 1

Nested Loop (cost=1.29..391.29 rows=145 width=16) (actual time=0.046..14.870 rows=2,596 loops=1)

  • Buffers: shared hit=16,088
3. 0.343 6.684 ↓ 12.5 2,667 1

Nested Loop (cost=0.86..64.98 rows=214 width=20) (actual time=0.034..6.684 rows=2,667 loops=1)

  • Buffers: shared hit=5,486
4. 0.083 0.083 ↓ 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.083 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
5. 5.224 6.258 ↓ 32.0 64 42

Index Scan using index_post_on_group_id_and_created_at on post (cost=0.43..11.83 rows=2 width=20) (actual time=0.006..0.149 rows=64 loops=42)

  • Index Cond: ((group_id = pages_group_memberships.group_id) AND (created_at > ((CURRENT_TIMESTAMP)::timestamp without time zone - '15 days'::interval)))
  • Filter: ((status = 'active'::post_status) AND ((image_id IS NULL) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 14
  • Buffers: shared hit=5,438
6.          

SubPlan (for Index Scan)

7. 1.034 1.034 ↑ 1.0 1 517

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

  • Index Cond: (id = post.image_id)
  • Filter: (status = ANY ('{uploaded,optimized}'::stored_image_status[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,071
8. 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[]))
9. 8.001 8.001 ↑ 1.0 1 2,667

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

  • Index Cond: (id = post.author_id)
  • Heap Fetches: 2,596
  • Buffers: shared hit=10,602
Planning time : 0.985 ms
Execution time : 15.411 ms