explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jF5M

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 181,987.938 ↑ 1.0 20 1

Limit (cost=20,730,146.96..20,730,147.01 rows=20 width=12) (actual time=181,987.935..181,987.938 rows=20 loops=1)

2. 1,859.437 181,987.935 ↑ 219,509.5 20 1

Sort (cost=20,730,146.96..20,741,122.44 rows=4,390,191 width=12) (actual time=181,987.933..181,987.935 rows=20 loops=1)

  • Sort Key: hive_feed_cache.created_at DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 19,305.654 180,128.498 ↓ 6.1 26,600,006 1

HashAggregate (cost=20,569,423.65..20,613,325.56 rows=4,390,191 width=12) (actual time=173,840.512..180,128.498 rows=26,600,006 loops=1)

  • Group Key: hive_feed_cache.created_at, hive_feed_cache.post_id
4. 9,975.596 160,822.844 ↓ 2.0 26,736,299 1

Hash Join (cost=3,162,400.65..20,502,264.19 rows=13,431,891 width=12) (actual time=16,469.091..160,822.844 rows=26,736,299 loops=1)

  • Hash Cond: (hive_feed_cache.account_id = hive_accounts.id)
5. 7,873.905 149,914.133 ↓ 2.0 26,736,299 1

Nested Loop (cost=3,083,149.53..20,387,754.25 rows=13,431,891 width=20) (actual time=15,531.880..149,914.133 rows=26,736,299 loops=1)

  • Join Filter: (hive_feed_cache.post_id = hive_posts_cache.post_id)
6. 8,501.135 35,085.072 ↓ 2.0 26,738,789 1

Merge Join (cost=3,083,148.96..5,028,488.51 rows=13,431,891 width=20) (actual time=15,531.859..35,085.072 rows=26,738,789 loops=1)

  • Merge Cond: (hive_feed_cache.post_id = hive_posts.id)
7. 9,806.161 18,654.186 ↓ 2.0 26,738,789 1

Sort (cost=3,083,126.52..3,116,706.25 rows=13,431,891 width=16) (actual time=15,531.786..18,654.186 rows=26,738,789 loops=1)

  • Sort Key: hive_feed_cache.post_id
  • Sort Method: external merge Disk: 784904kB
8. 7,587.024 8,848.025 ↓ 2.0 26,738,789 1

Seq Scan on hive_feed_cache (cost=1,011,831.83..1,492,847.10 rows=13,431,891 width=16) (actual time=1,511.924..8,848.025 rows=26,738,789 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 124858
9.          

SubPlan (for Seq Scan)

10. 0.000 1,261.001 ↑ 1.1 880,455 1

Gather (cost=1,000.86..1,009,485.48 rows=938,539 width=4) (actual time=0.434..1,261.001 rows=880,455 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
11. 0.000 1,339.854 ↑ 1.3 176,091 5 / 5

Nested Loop Anti Join (cost=0.86..914,631.58 rows=234,635 width=4) (actual time=0.205..1,339.854 rows=176,091 loops=5)

12. 985.534 985.534 ↑ 1.2 191,286 5 / 5

Parallel Index Scan using hive_posts_cache_community_id_not_null_idx on hive_posts_cache hive_posts_cache_1 (cost=0.42..805,632.09 rows=236,144 width=4) (actual time=0.132..985.534 rows=191,286 loops=5)

13. 382.572 382.572 ↓ 0.0 0 956,430 / 5

Index Only Scan using hive_reblogs_post_id on hive_reblogs (cost=0.43..0.83 rows=15 width=4) (actual time=0.002..0.002 rows=0 loops=956,430)

  • Index Cond: (post_id = hive_posts_cache_1.post_id)
  • Heap Fetches: 550
14. 7,929.751 7,929.751 ↓ 1.0 86,126,257 1

Index Only Scan using hive_posts_pkey on hive_posts (cost=0.57..1,528,823.59 rows=86,122,349 width=4) (actual time=0.065..7,929.751 rows=86,126,257 loops=1)

  • Heap Fetches: 56384
15. 106,955.156 106,955.156 ↑ 1.0 1 26,738,789

Index Scan using hive_posts_cache_pkey on hive_posts_cache (cost=0.57..1.13 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,738,789)

  • Index Cond: (post_id = hive_posts.id)
16. 270.669 933.115 ↓ 1.0 1,383,801 1

Hash (cost=61,954.87..61,954.87 rows=1,383,700 width=4) (actual time=933.115..933.115 rows=1,383,801 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 65034kB
17. 662.446 662.446 ↓ 1.0 1,383,801 1

Index Only Scan using hive_accounts_pkey on hive_accounts (cost=0.43..61,954.87 rows=1,383,700 width=4) (actual time=0.150..662.446 rows=1,383,801 loops=1)

  • Heap Fetches: 204863
Planning time : 4.469 ms
Execution time : 182,331.173 ms