explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PO4C : Optimization for: Optimization for: plan #UDBN; plan #lTkS

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 78,563.641 78,563.641 ↑ 34.7 77 1

CTE Scan on child_posts (cost=19,322,808.90..19,322,862.32 rows=2,671 width=16) (actual time=0.138..78,563.641 rows=77 loops=1)

2.          

CTE child_posts

3. 0.138 78,563.604 ↑ 34.7 77 1

Recursive Union (cost=0.69..19,322,808.90 rows=2,671 width=16) (actual time=0.137..78,563.604 rows=77 loops=1)

4. 0.135 0.135 ↑ 1.0 1 1

Index Scan using hive_posts_author_permlink_btree on hive_posts (cost=0.69..2.71 rows=1 width=16) (actual time=0.135..0.135 rows=1 loops=1)

  • Index Cond: (((author)::text = 'blocktrades'::text) AND ((permlink)::text = 'should-long-term-hive-proposals-cost-more-to-create'::text))
5. 28,767.366 78,563.331 ↑ 33.4 8 9

Hash Join (cost=0.89..1,932,275.28 rows=267 width=16) (actual time=8,708.801..8,729.259 rows=8 loops=9)

  • Hash Cond: (children.parent_id = child_posts_1.id)
  • Join Filter: (children.created_at > child_posts_1.created_at)
6. 49,795.929 49,795.929 ↑ 1.0 67,608,224 9

Index Only Scan using hive_posts_id_parent_id_created_at_btree on hive_posts children (cost=0.57..1,677,920.26 rows=67,825,252 width=16) (actual time=0.023..5,532.881 rows=67,608,224 loops=9)

  • Index Cond: (parent_id IS NOT NULL)
  • Heap Fetches: 497317
7. 0.018 0.036 ↑ 1.1 9 9

Hash (cost=0.20..0.20 rows=10 width=12) (actual time=0.004..0.004 rows=9 loops=9)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.018 0.018 ↑ 1.1 9 9

WorkTable Scan on child_posts child_posts_1 (cost=0.00..0.20 rows=10 width=12) (actual time=0.001..0.002 rows=9 loops=9)

Planning time : 1.243 ms
Execution time : 78,563.760 ms