explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yZQH : Optimization for: plan #wgXQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 0.100 ↓ 6.0 6 1

Limit (cost=27.35..27.36 rows=1 width=1,763) (actual time=0.099..0.100 rows=6 loops=1)

2. 0.019 0.099 ↓ 6.0 6 1

Sort (cost=27.35..27.36 rows=1 width=1,763) (actual time=0.098..0.099 rows=6 loops=1)

  • Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
  • Sort Method: quicksort Memory: 47kB
3. 0.004 0.080 ↓ 6.0 6 1

Nested Loop (cost=5.96..27.34 rows=1 width=1,763) (actual time=0.041..0.080 rows=6 loops=1)

4. 0.005 0.058 ↓ 6.0 6 1

Nested Loop (cost=5.68..20.18 rows=1 width=786) (actual time=0.033..0.058 rows=6 loops=1)

5. 0.016 0.029 ↓ 2.0 2 1

Bitmap Heap Scan on topics (cost=5.41..11.78 rows=1 width=605) (actual time=0.025..0.029 rows=2 loops=1)

  • Recheck Cond: ((user_id = 1) AND (deleted_at IS NULL))
  • Filter: (visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
  • Heap Blocks: exact=2
6. 0.006 0.006 ↑ 1.0 2 1

Bitmap Index Scan on idx_topics_user_id_deleted_at (cost=0.00..4.29 rows=2 width=0) (actual time=0.006..0.006 rows=2 loops=1)

  • Index Cond: (user_id = 1)
7.          

SubPlan (for Bitmap Heap Scan)

8. 0.007 0.007 ↑ 1.0 8 1

Seq Scan on categories (cost=0.00..1.10 rows=8 width=4) (actual time=0.005..0.007 rows=8 loops=1)

  • Filter: (NOT read_restricted)
  • Rows Removed by Filter: 2
9. 0.024 0.024 ↓ 3.0 3 2

Index Scan using index_topic_links_on_topic_id on topic_links (cost=0.28..8.39 rows=1 width=181) (actual time=0.005..0.012 rows=3 loops=2)

  • Index Cond: (topic_id = topics.id)
  • Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 1))
  • Rows Removed by Filter: 24
10. 0.018 0.018 ↑ 1.0 1 6

Index Scan using posts_pkey on posts (cost=0.28..6.89 rows=1 width=973) (actual time=0.003..0.003 rows=1 loops=6)

  • Index Cond: (id = topic_links.post_id)
  • Filter: ((deleted_at IS NULL) AND (user_id = 1) AND (post_type = ANY ('{1,2,3}'::integer[])))
Planning time : 0.814 ms
Execution time : 0.188 ms