explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dgin : Optimization for: Optimization for: plan #GdT8; plan #isUi

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 2.550 ↓ 6.0 6 1

Limit (cost=897.28..897.28 rows=1 width=1,688) (actual time=2.549..2.550 rows=6 loops=1)

2. 0.333 2.548 ↓ 6.0 6 1

Sort (cost=897.28..897.28 rows=1 width=1,688) (actual time=2.547..2.548 rows=6 loops=1)

  • Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
  • Sort Method: top-N heapsort Memory: 54kB
3. 0.100 2.215 ↓ 226.0 226 1

Nested Loop (cost=13.56..897.27 rows=1 width=1,688) (actual time=0.139..2.215 rows=226 loops=1)

4. 0.081 1.437 ↓ 226.0 226 1

Nested Loop (cost=13.14..890.18 rows=1 width=746) (actual time=0.116..1.437 rows=226 loops=1)

5. 0.045 0.446 ↓ 7.2 65 1

Hash Join (cost=12.72..636.55 rows=9 width=575) (actual time=0.087..0.446 rows=65 loops=1)

  • Hash Cond: (topics.category_id = categories.id)
6. 0.345 0.366 ↓ 2.4 104 1

Bitmap Heap Scan on topics (cost=5.70..629.41 rows=44 width=575) (actual time=0.044..0.366 rows=104 loops=1)

  • Recheck Cond: ((user_id = 8,722) AND (deleted_at IS NULL))
  • Filter: (visible AND ((archetype)::text <> 'private_message'::text))
  • Rows Removed by Filter: 65
  • Heap Blocks: exact=167
7. 0.021 0.021 ↓ 1.0 175 1

Bitmap Index Scan on idx_topics_user_id_deleted_at (cost=0.00..5.69 rows=169 width=0) (actual time=0.021..0.021 rows=175 loops=1)

  • Index Cond: (user_id = 8,722)
8. 0.008 0.035 ↑ 1.0 40 1

Hash (cost=6.52..6.52 rows=40 width=4) (actual time=0.035..0.035 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
9. 0.027 0.027 ↑ 1.0 40 1

Seq Scan on categories (cost=0.00..6.52 rows=40 width=4) (actual time=0.005..0.027 rows=40 loops=1)

  • Filter: (read_restricted IS FALSE)
  • Rows Removed by Filter: 12
10. 0.910 0.910 ↓ 3.0 3 65

Index Scan using index_forum_thread_links_on_forum_thread_id on topic_links (cost=0.42..28.17 rows=1 width=171) (actual time=0.007..0.014 rows=3 loops=65)

  • Index Cond: (topic_id = topics.id)
  • Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 8,722))
  • Rows Removed by Filter: 6
11. 0.678 0.678 ↑ 1.0 1 226

Index Scan using index_posts_on_id_and_baked_version on posts (cost=0.42..7.09 rows=1 width=938) (actual time=0.003..0.003 rows=1 loops=226)

  • Index Cond: (id = topic_links.post_id)
  • Filter: ((user_id = 8,722) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Planning time : 1.233 ms
Execution time : 2.667 ms