explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SDOL

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 64,916.197 ↑ 1.0 20 1

Limit (cost=9,527,842.58..9,527,844.03 rows=20 width=1,065) (actual time=64,758.002..64,916.197 rows=20 loops=1)

2. 158.089 64,916.193 ↑ 62.8 20 1

Unique (cost=9,527,842.58..9,527,933.64 rows=1,256 width=1,065) (actual time=64,758.001..64,916.193 rows=20 loops=1)

3. 0.000 64,758.104 ↑ 1.2 1,057 1

Sort (cost=9,527,842.58..9,527,845.72 rows=1,256 width=1,065) (actual time=64,757.999..64,758.104 rows=1,057 loops=1)

  • Sort Key: hive_posts_cache.created_at DESC, hive_posts_cache.post_id, hive_posts_cache.author, hive_posts_cache.permlink, hive_posts_cache.title, hive_posts_cache.body, hive_posts_cache.category, hive_posts_cache.promoted, hive_posts_cache.payout, hive_posts_cache.payout_at, hive_posts_cache.is_paidout, hive_posts_cache.children, hive_posts_cache.votes, hive_posts_cache.updated_at, hive_posts_cache.rshares, hive_posts_cache.raw_json, hive_posts_cache.json, hive_accounts.reputation, hive_posts_cache.is_hidden, hive_posts_cache.is_grayed, hive_posts_cache.total_votes, hive_posts_cache.flag_weight, hive_posts_cache.sc_trend, hive_accounts.id, hive_roles.title, hive_communities.title, hive_roles.role_id, hive_posts.is_pinned
  • Sort Method: quicksort Memory: 7499kB
4. 3,113.488 65,013.861 ↓ 4.5 5,641 1

Gather (cost=9,470,107.23..9,527,777.93 rows=1,256 width=1,065) (actual time=59,081.599..65,013.861 rows=5,641 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
5. 0.466 61,900.373 ↓ 4.0 627 9 / 9

Nested Loop (cost=9,469,107.23..9,526,652.33 rows=157 width=1,065) (actual time=59,057.562..61,900.373 rows=627 loops=9)

6. 0.279 61,898.653 ↓ 4.0 627 9 / 9

Nested Loop Left Join (cost=9,469,106.67..9,526,479.69 rows=158 width=1,064) (actual time=59,057.523..61,898.653 rows=627 loops=9)

7. 0.293 61,898.374 ↓ 4.0 627 9 / 9

Nested Loop Left Join (cost=9,469,106.38..9,526,366.48 rows=158 width=1,063) (actual time=59,057.514..61,898.374 rows=627 loops=9)

8. 0.695 61,898.081 ↓ 4.0 627 9 / 9

Nested Loop (cost=9,469,106.10..9,526,252.40 rows=158 width=1,051) (actual time=59,057.510..61,898.081 rows=627 loops=9)

9. 941.175 61,894.252 ↓ 4.0 627 9 / 9

Merge Left Join (cost=9,469,105.68..9,526,112.05 rows=158 width=1,043) (actual time=59,057.443..61,894.252 rows=627 loops=9)

  • Merge Cond: (hive_posts_cache.post_id = hive_reblogs.post_id)
  • Filter: ((((hive_posts_cache.community_id IS NOT NULL) AND (hive_reblogs.post_id IS NULL)) OR (hive_posts_cache.community_id IS NULL)) AND (((hive_posts_cache.author)::text = 'blocktrades'::text) OR ((hive_reblogs.account)::text = 'blocktrades'::text)))
  • Rows Removed by Filter: 2603251
10. 7,676.624 57,628.007 ↑ 1.1 2,052,370 9 / 9

Sort (cost=8,357,632.86..8,363,347.48 rows=2,285,849 width=1,043) (actual time=56,710.264..57,628.007 rows=2,052,370 loops=9)

  • Sort Key: hive_posts_cache.post_id
  • Sort Method: external merge Disk: 2484296kB
11. 49,951.383 49,951.383 ↑ 1.1 2,052,370 9 / 9

Parallel Index Scan using hive_posts_cache_hot_post_id_depth on hive_posts_cache (cost=0.57..7,517,948.07 rows=2,285,849 width=1,043) (actual time=0.086..49,951.383 rows=2,052,370 loops=9)

  • Index Cond: (depth = 0)
12. 2,637.911 3,325.070 ↑ 1.0 8,439,603 9 / 9

Sort (cost=1,111,472.82..1,132,572.12 rows=8,439,720 width=14) (actual time=2,345.598..3,325.070 rows=8,439,603 loops=9)

  • Sort Key: hive_reblogs.post_id
  • Sort Method: quicksort Memory: 779614kB
13. 687.159 687.159 ↑ 1.0 8,439,604 9 / 9

Seq Scan on hive_reblogs (cost=0.00..140,535.20 rows=8,439,720 width=14) (actual time=0.046..687.159 rows=8,439,604 loops=9)

14. 3.134 3.134 ↑ 1.0 1 5,641 / 9

Index Scan using hive_accounts_ux1 on hive_accounts (cost=0.43..0.89 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=5,641)

  • Index Cond: ((name)::text = (hive_posts_cache.author)::text)
15. 0.000 0.000 ↓ 0.0 0 5,641 / 9

Index Scan using hive_communities_id_name on hive_communities (cost=0.28..0.72 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=5,641)

  • Index Cond: (hive_posts_cache.community_id = id)
16. 0.000 0.000 ↓ 0.0 0 5,641 / 9

Index Scan using hive_roles_ix1 on hive_roles (cost=0.29..0.72 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=5,641)

  • Index Cond: ((hive_posts_cache.community_id = community_id) AND (hive_accounts.id = account_id))
17. 1.254 1.254 ↑ 1.0 1 5,641 / 9

Index Scan using hive_posts_pkey on hive_posts (cost=0.57..1.09 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=5,641)

  • Index Cond: (id = hive_posts_cache.post_id)
  • Filter: (NOT is_deleted)
Planning time : 9.815 ms
Execution time : 66,213.947 ms