explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Q8Z : Optimization for: Optimization for: Optimization for: Optimization for: plan #GdT8; plan #isUi; plan #Dgin; plan #Jywi

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 17.862 ↓ 6.0 6 1

Limit (cost=4,861.78..4,861.78 rows=1 width=1,688) (actual time=17.860..17.862 rows=6 loops=1)

2. 1.136 17.859 ↓ 6.0 6 1

Sort (cost=4,861.78..4,861.78 rows=1 width=1,688) (actual time=17.859..17.859 rows=6 loops=1)

  • Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
  • Sort Method: top-N heapsort Memory: 49kB
3. 0.726 16.723 ↓ 682.0 682 1

Nested Loop (cost=36.46..4,861.77 rows=1 width=1,688) (actual time=0.890..16.723 rows=682 loops=1)

4. 0.316 13.951 ↓ 341.0 682 1

Nested Loop (cost=36.04..4,849.50 rows=2 width=746) (actual time=0.865..13.951 rows=682 loops=1)

5. 0.187 2.835 ↓ 4.8 270 1

Hash Join (cost=35.62..3,384.09 rows=56 width=575) (actual time=0.824..2.835 rows=270 loops=1)

  • Hash Cond: (topics.category_id = categories.id)
6. 1.951 2.611 ↓ 1.0 293 1

Bitmap Heap Scan on topics (cost=28.60..3,376.31 rows=284 width=575) (actual time=0.777..2.611 rows=293 loops=1)

  • Recheck Cond: ((user_id = 1) AND (deleted_at IS NULL))
  • Filter: (visible AND ((archetype)::text <> 'private_message'::text))
  • Rows Removed by Filter: 807
  • Heap Blocks: exact=1,018
7. 0.660 0.660 ↓ 1.1 1,153 1

Bitmap Index Scan on idx_topics_user_id_deleted_at (cost=0.00..28.53 rows=1,081 width=0) (actual time=0.660..0.660 rows=1,153 loops=1)

  • Index Cond: (user_id = 1)
8. 0.009 0.037 ↑ 1.0 40 1

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

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

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

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

Index Scan using index_forum_thread_links_on_forum_thread_id on topic_links (cost=0.42..26.16 rows=1 width=171) (actual time=0.025..0.040 rows=3 loops=270)

  • Index Cond: (topic_id = topics.id)
  • Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 1))
  • Rows Removed by Filter: 14
11. 2.046 2.046 ↑ 1.0 1 682

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

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