explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Gyw

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 162,551.267 ↑ 1.0 20 1

Limit (cost=20,730,056.22..20,730,056.27 rows=20 width=12) (actual time=162,551.264..162,551.267 rows=20 loops=1)

2. 1,846.553 162,551.264 ↑ 219,508.6 20 1

Sort (cost=20,730,056.22..20,741,031.65 rows=4,390,173 width=12) (actual time=162,551.263..162,551.264 rows=20 loops=1)

  • Sort Key: hive_feed_cache.created_at DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 19,056.562 160,704.711 ↓ 6.1 26,600,016 1

HashAggregate (cost=20,569,333.56..20,613,235.29 rows=4,390,173 width=12) (actual time=154,420.224..160,704.711 rows=26,600,016 loops=1)

  • Group Key: hive_feed_cache.created_at, hive_feed_cache.post_id
4. 9,785.051 141,648.149 ↓ 2.0 26,736,309 1

Hash Join (cost=3,162,391.76..20,502,174.40 rows=13,431,832 width=12) (actual time=15,650.578..141,648.149 rows=26,736,309 loops=1)

  • Hash Cond: (hive_feed_cache.account_id = hive_accounts.id)
5. 17,517.871 130,962.489 ↓ 2.0 26,736,309 1

Nested Loop (cost=3,083,140.65..20,387,664.62 rows=13,431,832 width=20) (actual time=14,745.832..130,962.489 rows=26,736,309 loops=1)

  • Join Filter: (hive_feed_cache.post_id = hive_posts_cache.post_id)
6. 8,238.384 33,228.221 ↓ 2.0 26,738,799 1

Merge Join (cost=3,083,140.08..5,028,479.12 rows=13,431,832 width=20) (actual time=14,745.765..33,228.221 rows=26,738,799 loops=1)

  • Merge Cond: (hive_feed_cache.post_id = hive_posts.id)
7. 9,739.422 17,837.804 ↓ 2.0 26,738,799 1

Sort (cost=3,083,117.64..3,116,697.22 rows=13,431,832 width=16) (actual time=14,745.693..17,837.804 rows=26,738,799 loops=1)

  • Sort Key: hive_feed_cache.post_id
  • Sort Method: external merge Disk: 784904kB
8. 6,887.516 8,098.382 ↓ 2.0 26,738,799 1

Seq Scan on hive_feed_cache (cost=1,011,831.83..1,492,845.63 rows=13,431,832 width=16) (actual time=1,467.919..8,098.382 rows=26,738,799 loops=1)

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

SubPlan (for Seq Scan)

10. 0.000 1,210.866 ↑ 1.1 880,495 1

Gather (cost=1,000.86..1,009,485.48 rows=938,539 width=4) (actual time=0.424..1,210.866 rows=880,495 loops=1)

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

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

12. 933.757 933.757 ↑ 1.2 191,294 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.176..933.757 rows=191,294 loops=5)

13. 382.588 382.588 ↓ 0.0 0 956,471 / 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,471)

  • Index Cond: (post_id = hive_posts_cache_1.post_id)
  • Heap Fetches: 641
14. 7,152.033 7,152.033 ↓ 1.0 86,126,335 1

Index Only Scan using hive_posts_pkey on hive_posts (cost=0.57..1,528,825.39 rows=86,122,402 width=4) (actual time=0.065..7,152.033 rows=86,126,335 loops=1)

  • Heap Fetches: 56463
15. 80,216.397 80,216.397 ↑ 1.0 1 26,738,799

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

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

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 65034kB
17. 629.302 629.302 ↓ 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.125..629.302 rows=1,383,801 loops=1)

  • Heap Fetches: 212671
Planning time : 5.476 ms
Execution time : 162,892.275 ms