explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EybB

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,890,184.460 ↑ 1.0 20 1

Limit (cost=2.13..369.43 rows=20 width=1,063) (actual time=1,888,522.431..1,890,184.460 rows=20 loops=1)

2. 0.022 1,890,184.458 ↑ 2,139,281.4 20 1

Nested Loop Left Join (cost=2.13..785,766,961.75 rows=42,785,628 width=1,063) (actual time=1,888,522.430..1,890,184.458 rows=20 loops=1)

3. 0.020 1,890,184.436 ↑ 2,139,281.4 20 1

Nested Loop Left Join (cost=1.84..755,768,943.05 rows=42,785,628 width=1,062) (actual time=1,888,522.425..1,890,184.436 rows=20 loops=1)

4. 0.035 1,890,184.416 ↑ 2,139,281.4 20 1

Nested Loop (cost=1.56..725,866,399.33 rows=42,785,628 width=1,050) (actual time=1,888,522.420..1,890,184.416 rows=20 loops=1)

5. 0.034 1,890,184.281 ↑ 2,139,281.4 20 1

Nested Loop (cost=1.14..688,318,314.82 rows=42,785,628 width=1,042) (actual time=1,888,522.392..1,890,184.281 rows=20 loops=1)

6. 178,785.827 1,890,184.127 ↑ 2,139,281.4 20 1

Index Scan using hive_posts_cache_created_at on hive_posts_cache (cost=0.57..643,059,021.44 rows=42,785,628 width=1,041) (actual time=1,888,522.372..1,890,184.127 rows=20 loops=1)

  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 85569895
7.          

SubPlan (for Index Scan)

8. 85,569.915 1,711,398.300 ↑ 1.0 20 85,569,915

Limit (cost=0.57..14.04 rows=20 width=12) (actual time=0.010..0.020 rows=20 loops=85,569,915)

9. 171,139.830 1,625,828.385 ↑ 43.2 20 85,569,915

Result (cost=0.57..582.62 rows=864 width=12) (actual time=0.009..0.019 rows=20 loops=85,569,915)

10. 85,569.915 1,454,688.555 ↑ 43.2 20 85,569,915

Append (cost=0.57..573.98 rows=864 width=4) (actual time=0.009..0.017 rows=20 loops=85,569,915)

11. 85,569.915 1,369,118.640 ↑ 26.9 20 85,569,915

Subquery Scan on *SELECT* 1 (cost=0.57..565.73 rows=539 width=4) (actual time=0.009..0.016 rows=20 loops=85,569,915)

12. 1,283,548.725 1,283,548.725 ↑ 26.9 20 85,569,915

Index Scan using hive_posts_ix3 on hive_posts hive_posts_1 (cost=0.57..560.34 rows=539 width=62) (actual time=0.009..0.015 rows=20 loops=85,569,915)

  • Index Cond: (((author)::text = 'blocktrades'::text) AND (depth = 0))
  • Filter: (community_id IS NULL)
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using hive_reblogs_ux1 on hive_reblogs (cost=0.56..8.25 rows=325 width=4) (never executed)

  • Index Cond: (account = 'blocktrades'::text)
  • Heap Fetches: 0
14. 0.120 0.120 ↑ 1.0 1 20

Index Scan using hive_posts_pkey on hive_posts (cost=0.57..1.06 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=20)

  • Index Cond: (id = hive_posts_cache.post_id)
15. 0.100 0.100 ↑ 1.0 1 20

Index Scan using hive_accounts_ux1 on hive_accounts (cost=0.43..0.88 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=20)

  • Index Cond: ((name)::text = (hive_posts_cache.author)::text)
16. 0.000 0.000 ↓ 0.0 0 20

Index Scan using hive_communities_pkey on hive_communities (cost=0.28..0.70 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=20)

  • Index Cond: (hive_posts_cache.community_id = id)
17. 0.000 0.000 ↓ 0.0 0 20

Index Scan using hive_roles_ux1 on hive_roles (cost=0.29..0.70 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=20)

  • Index Cond: ((hive_accounts.id = account_id) AND (hive_posts_cache.community_id = community_id))
Planning time : 4.779 ms
Execution time : 1,890,184.579 ms