explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Y3n : Optimization for: plan #SDOL

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.004 56,037.029 ↑ 1.0 20 1

Limit (cost=8,871,758.57..8,871,760.02 rows=20 width=1,065) (actual time=55,879.152..56,037.029 rows=20 loops=1)

2. 157.725 56,037.025 ↑ 62.8 20 1

Unique (cost=8,871,758.57..8,871,849.63 rows=1,256 width=1,065) (actual time=55,879.151..56,037.025 rows=20 loops=1)

3. 0.000 55,879.300 ↑ 1.2 1,057 1

Sort (cost=8,871,758.57..8,871,761.71 rows=1,256 width=1,065) (actual time=55,879.149..55,879.300 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,161.413 56,208.070 ↓ 4.5 5,641 1

Gather (cost=8,811,829.15..8,871,693.92 rows=1,256 width=1,065) (actual time=49,854.699..56,208.070 rows=5,641 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
5. 0.283 53,046.657 ↓ 3.9 705 8 / 8

Nested Loop (cost=8,810,829.15..8,870,568.32 rows=179 width=1,065) (actual time=49,857.982..53,046.657 rows=705 loops=8)

6. 0.287 53,044.259 ↓ 3.9 705 8 / 8

Nested Loop Left Join (cost=8,810,828.58..8,870,370.54 rows=181 width=1,064) (actual time=49,857.880..53,044.259 rows=705 loops=8)

7. 0.308 53,043.972 ↓ 3.9 705 8 / 8

Nested Loop Left Join (cost=8,810,828.29..8,870,240.86 rows=181 width=1,063) (actual time=49,857.876..53,043.972 rows=705 loops=8)

8. 0.393 53,043.664 ↓ 3.9 705 8 / 8

Nested Loop (cost=8,810,828.02..8,870,110.17 rows=181 width=1,051) (actual time=49,857.871..53,043.664 rows=705 loops=8)

9. 1,008.684 53,039.745 ↓ 3.9 705 8 / 8

Merge Left Join (cost=8,810,827.59..8,869,949.38 rows=181 width=1,043) (actual time=49,857.807..53,039.745 rows=705 loops=8)

  • 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: 2928666
10. 8,369.194 48,718.538 ↑ 1.1 2,308,922 8 / 8

Sort (cost=7,699,354.77..7,705,885.79 rows=2,612,406 width=1,043) (actual time=47,530.245..48,718.538 rows=2,308,922 loops=8)

  • Sort Key: hive_posts_cache.post_id
  • Sort Method: external merge Disk: 2773136kB
11. 40,349.344 40,349.344 ↑ 1.1 2,308,922 8 / 8

Parallel Index Scan using "hive_posts_cache_hot_post_id_depth_depth=0" on hive_posts_cache (cost=0.56..6,737,196.16 rows=2,612,406 width=1,043) (actual time=0.104..40,349.344 rows=2,308,922 loops=8)

12. 2,638.588 3,312.523 ↑ 1.0 8,439,641 8 / 8

Sort (cost=1,111,472.82..1,132,572.12 rows=8,439,720 width=14) (actual time=2,327.308..3,312.523 rows=8,439,641 loops=8)

  • Sort Key: hive_reblogs.post_id
  • Sort Method: quicksort Memory: 779617kB
13. 673.935 673.935 ↑ 1.0 8,439,643 8 / 8

Seq Scan on hive_reblogs (cost=0.00..140,535.20 rows=8,439,720 width=14) (actual time=0.048..673.935 rows=8,439,643 loops=8)

14. 3.526 3.526 ↑ 1.0 1 5,641 / 8

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 / 8

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 / 8

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. 2.115 2.115 ↑ 1.0 1 5,641 / 8

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

  • Index Cond: (id = hive_posts_cache.post_id)
  • Filter: (NOT is_deleted)
Planning time : 19.143 ms
Execution time : 57,342.893 ms