explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 77,756.574 77,756.574 ↑ 27.3 77 1

CTE Scan on child_posts (cost=12,662,347.01..12,662,389.03 rows=2,101 width=16) (actual time=0.129..77,756.574 rows=77 loops=1)

2.          

CTE child_posts

3. 0.135 77,756.535 ↑ 27.3 77 1

Recursive Union (cost=0.69..12,662,347.01 rows=2,101 width=16) (actual time=0.128..77,756.535 rows=77 loops=1)

4. 0.126 0.126 ↑ 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.125..0.126 rows=1 loops=1)

  • Index Cond: (((author)::text = 'blocktrades'::text) AND ((permlink)::text = 'should-long-term-hive-proposals-cost-more-to-create'::text))
5. 29,204.541 77,756.274 ↑ 26.2 8 9

Hash Join (cost=0.89..1,266,230.23 rows=210 width=16) (actual time=8,618.699..8,639.586 rows=8 loops=9)

  • Hash Cond: (children.parent_id = child_posts_1.id)
  • Join Filter: (children.created_at > child_posts_1.created_at)
6. 48,551.688 48,551.688 ↓ 1.3 67,608,361 9

Index Only Scan using hive_posts_id_parent_id_created_at_depth_btree on hive_posts children (cost=0.57..1,066,069.82 rows=53,373,921 width=16) (actual time=0.030..5,394.632 rows=67,608,361 loops=9)

  • Heap Fetches: 498890
7. 0.027 0.045 ↑ 1.1 9 9

Hash (cost=0.20..0.20 rows=10 width=12) (actual time=0.005..0.005 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.341 ms
Execution time : 77,756.695 ms