explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T3oD : Optimization for: plan #WNlZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.385 134.843 ↑ 86.4 34 1

GroupAggregate (cost=6.61..1,036.04 rows=2,937 width=16) (actual time=0.675..134.843 rows=34 loops=1)

  • Group Key: post.group_id
  • Buffers: shared hit=92,738
2. 9.166 132.458 ↓ 3.5 13,507 1

Merge Left Join (cost=6.61..987.57 rows=3,821 width=16) (actual time=0.119..132.458 rows=13,507 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 - '7 days'::interval)))
  • Rows Removed by Filter: 73,450
  • Buffers: shared hit=92,738
3. 6.591 123.201 ↓ 7.6 86,957 1

Nested Loop (cost=0.85..946.96 rows=11,462 width=24) (actual time=0.030..123.201 rows=86,957 loops=1)

  • Buffers: shared hit=92,690
4. 0.060 0.060 ↓ 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.060 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. 116.550 116.550 ↓ 18.0 2,070 42

Index Scan using index_post_on_group_id on post (cost=0.42..187.10 rows=115 width=24) (actual time=0.007..2.775 rows=2,070 loops=42)

  • Index Cond: (group_id = pages_group_memberships.group_id)
  • Filter: (_active_author AND _valid_image AND (status = 'active'::post_status))
  • Rows Removed by Filter: 820
  • Buffers: shared hit=92,642
6. 0.017 0.091 ↓ 9.0 45 1

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

  • Sort Key: pgm.group_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=48
7. 0.074 0.074 ↓ 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.074 rows=45 loops=1)

  • Index Cond: (user_id = 8)
  • Buffers: shared hit=48
Planning time : 0.947 ms
Execution time : 134.901 ms