explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

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

CTE Scan on child_posts (cost=12,662,373.58..12,662,415.60 rows=2,101 width=16) (actual time=0.131..76,554.823 rows=77 loops=1)

2.          

CTE child_posts

3. 0.128 76,554.778 ↑ 27.3 77 1

Recursive Union (cost=0.69..12,662,373.58 rows=2,101 width=16) (actual time=0.129..76,554.778 rows=77 loops=1)

4. 0.128 0.128 ↑ 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.128..0.128 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,836.135 76,554.522 ↑ 26.2 8 9

Hash Join (cost=0.89..1,266,232.88 rows=210 width=16) (actual time=8,486.235..8,506.058 rows=8 loops=9)

  • Hash Cond: (children.parent_id = child_posts_1.id)
  • Join Filter: (children.created_at > child_posts_1.created_at)
6. 47,718.342 47,718.342 ↓ 1.3 67,608,444 9

Index Only Scan using hive_posts_id_parent_id_created_at_depth_btree on hive_posts children (cost=0.57..1,066,072.24 rows=53,373,986 width=16) (actual time=0.024..5,302.038 rows=67,608,444 loops=9)

  • Heap Fetches: 499636
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.452 ms
Execution time : 76,554.942 ms