explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8mhi : diaspora

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 1,897.078 ↑ 1.0 15 1

Limit (cost=90,800.12..90,800.80 rows=15 width=1,004) (actual time=1,897.006..1,897.078 rows=15 loops=1)

  • Planning time: 18.007 ms
  • Execution time: 1901.303 ms
2. 0.046 1,897.059 ↑ 1,697.0 15 1

Unique (cost=90,800.12..91,945.60 rows=25,455 width=1,004) (actual time=1,897.004..1,897.059 rows=15 loops=1)

3. 149.680 1,897.013 ↑ 1,272.8 20 1

Sort (cost=90,800.12..90,863.76 rows=25,455 width=1,004) (actual time=1,897.000..1,897.013 rows=20 loops=1)

  • Sort Key: posts.created_at DESC, posts.id DESC, posts.author_id, posts.public, posts.guid, posts.text, posts.updated_at, posts.provider_display_name, posts.root_guid, posts.likes_count, posts.comments_count, posts.o_embed_cache_id, posts.reshares_count, posts.interacted_at, posts.tweet_id, posts.open_graph_cache_id, posts.tumblr_ids
  • Sort Method: external sort Disk: 12200kB
4. 63.360 1,747.333 ↓ 1.2 30,021 1

Merge Join (cost=16,810.60..77,712.87 rows=25,455 width=1,004) (actual time=340.974..1,747.333 rows=30,021 loops=1)

  • Merge Cond: (posts.id = taggings.taggable_id)
5. 1,334.705 1,334.705 ↓ 1.0 453,058 1

Index Scan Backward using posts_pkey on posts (cost=0.42..61,890.37 rows=450,502 width=1,000) (actual time=0.200..1,334.705 rows=453,058 loops=1)

  • Filter: (public AND (author_id <> 12457) AND (id <> 20477) AND (created_at < '2019-01-06 20:40:17'::timestamp without time zone) AND ((type)::text = ANY ('{StatusMessage,Reshare}'::text[])) AND ((type)::text = 'StatusMessage'::text))
  • Rows Removed by Filter: 51662
6. 29.593 349.268 ↓ 1.1 30,113 1

Sort (cost=14,299.34..14,370.52 rows=28,473 width=4) (actual time=340.637..349.268 rows=30,113 loops=1)

  • Sort Key: taggings.taggable_id DESC
  • Sort Method: quicksort Memory: 2180kB
7. 309.254 319.675 ↓ 1.1 30,113 1

Bitmap Heap Scan on taggings (cost=664.14..12,192.72 rows=28,473 width=4) (actual time=12.411..319.675 rows=30,113 loops=1)

  • Recheck Cond: (tag_id = ANY ('{179,12,145,13036,112,107,110,2,101,98}'::integer[]))
  • Filter: ((taggable_type)::text = 'Post'::text)
  • Rows Removed by Filter: 812
  • Heap Blocks: exact=8007
8. 10.421 10.421 ↓ 1.1 30,951 1

Bitmap Index Scan on index_taggings_on_tag_id (cost=0.00..657.02 rows=28,903 width=0) (actual time=10.420..10.421 rows=30,951 loops=1)

  • Index Cond: (tag_id = ANY ('{179,12,145,13036,112,107,110,2,101,98}'::integer[]))