explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ir6

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 158,744.996 ↑ 1.0 20 1

Limit (cost=9,527,725.18..9,527,726.63 rows=20 width=1,065) (actual time=158,585.117..158,744.996 rows=20 loops=1)

2. 159.707 158,744.991 ↑ 62.8 20 1

Unique (cost=9,527,725.18..9,527,816.24 rows=1,256 width=1,065) (actual time=158,585.115..158,744.991 rows=20 loops=1)

3. 0.000 158,585.284 ↑ 1.2 1,057 1

Sort (cost=9,527,725.18..9,527,728.32 rows=1,256 width=1,065) (actual time=158,585.113..158,585.284 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. 2,781.065 158,893.078 ↓ 4.5 5,641 1

Gather (cost=9,469,990.97..9,527,660.53 rows=1,256 width=1,065) (actual time=152,711.578..158,893.078 rows=5,641 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
5. 0.541 156,112.013 ↓ 4.0 627 9 / 9

Nested Loop (cost=9,468,990.97..9,526,534.93 rows=157 width=1,065) (actual time=153,101.646..156,112.013 rows=627 loops=9)

6. 0.281 156,108.965 ↓ 4.0 627 9 / 9

Nested Loop Left Join (cost=9,468,990.40..9,526,362.28 rows=158 width=1,064) (actual time=153,101.263..156,108.965 rows=627 loops=9)

7. 0.287 156,108.684 ↓ 4.0 627 9 / 9

Nested Loop Left Join (cost=9,468,990.12..9,526,249.08 rows=158 width=1,063) (actual time=153,101.249..156,108.684 rows=627 loops=9)

8. 0.150 156,108.397 ↓ 4.0 627 9 / 9

Nested Loop (cost=9,468,989.84..9,526,135.00 rows=158 width=1,051) (actual time=153,101.246..156,108.397 rows=627 loops=9)

9. 994.836 156,105.113 ↓ 4.0 627 9 / 9

Merge Left Join (cost=9,468,989.41..9,525,994.64 rows=158 width=1,043) (actual time=153,101.179..156,105.113 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: 2603240
10. 8,199.797 151,707.816 ↑ 1.1 2,052,362 9 / 9

Sort (cost=8,357,537.57..8,363,252.03 rows=2,285,784 width=1,043) (actual time=150,677.209..151,707.816 rows=2,052,362 loops=9)

  • Sort Key: hive_posts_cache.post_id
  • Sort Method: external merge Disk: 2489304kB
11. 143,508.019 143,508.019 ↑ 1.1 2,052,362 9 / 9

Parallel Index Scan using hive_posts_cache_hot_post_id_depth on hive_posts_cache (cost=0.57..7,517,876.12 rows=2,285,784 width=1,043) (actual time=0.066..143,508.019 rows=2,052,362 loops=9)

  • Index Cond: (depth = 0)
12. 2,716.639 3,402.461 ↑ 1.0 8,439,553 9 / 9

Sort (cost=1,111,451.85..1,132,550.77 rows=8,439,569 width=14) (actual time=2,420.675..3,402.461 rows=8,439,553 loops=9)

  • Sort Key: hive_reblogs.post_id
  • Sort Method: quicksort Memory: 779612kB
13. 685.822 685.822 ↑ 1.0 8,439,557 9 / 9

Seq Scan on hive_reblogs (cost=0.00..140,532.69 rows=8,439,569 width=14) (actual time=0.047..685.822 rows=8,439,557 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. 2.507 2.507 ↑ 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.004..0.004 rows=1 loops=5,641)

  • Index Cond: (id = hive_posts_cache.post_id)
  • Filter: (NOT is_deleted)
Planning time : 9.247 ms
Execution time : 160,038.362 ms