explain.depesz.com

PostgreSQL's explain analyze made readable

Result: soXr

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.035 58.693 ↑ 1,331.3 7,124 1

Hash Join (cost=2,703,249.22..5,411,179.59 rows=9,484,444 width=99) (actual time=8.773..58.693 rows=7,124 loops=1)

  • Hash Cond: (ets.child = pr.parent)
2.          

CTE ets

3. 10.083 41.567 ↑ 3,030.4 24,637 1

Recursive Union (cost=146.94..2,702,355.38 rows=74,659,598 width=12) (actual time=0.297..41.567 rows=24,637 loops=1)

4. 0.719 0.998 ↑ 1.0 7,568 1

Bitmap Heap Scan on place_relation (cost=146.94..405.54 rows=7,568 width=12) (actual time=0.294..0.998 rows=7,568 loops=1)

  • Recheck Cond: (parent = 1)
  • Heap Blocks: exact=41
5. 0.279 0.279 ↑ 1.0 7,568 1

Bitmap Index Scan on place_relation_pkey (cost=0.00..145.05 rows=7,568 width=0) (actual time=0.279..0.279 rows=7,568 loops=1)

  • Index Cond: (parent = 1)
6. 10.803 30.486 ↑ 918.5 8,128 3

Merge Join (cost=7,646.85..120,875.79 rows=7,465,203 width=12) (actual time=5.691..10.162 rows=8,128 loops=3)

  • Merge Cond: (pr_1.parent = ets_1.child)
7. 11.877 11.877 ↑ 1.0 30,283 3

Index Scan using place_relation_pkey on place_relation pr_1 (cost=0.29..1,175.47 rows=30,283 width=12) (actual time=0.007..3.959 rows=30,283 loops=3)

8. 6.030 7.806 ↑ 5.5 13,840 3

Sort (cost=7,646.57..7,835.77 rows=75,680 width=4) (actual time=1.866..2.602 rows=13,840 loops=3)

  • Sort Key: ets_1.child
  • Sort Method: quicksort Memory: 49kB
9. 1.776 1.776 ↑ 9.2 8,212 3

WorkTable Scan on ets ets_1 (cost=0.00..1,513.60 rows=75,680 width=4) (actual time=0.001..0.592 rows=8,212 loops=3)

10. 46.191 46.191 ↑ 3,030.4 24,637 1

CTE Scan on ets (cost=0.00..1,493,191.96 rows=74,659,598 width=12) (actual time=0.299..46.191 rows=24,637 loops=1)

11. 1.332 8.467 ↓ 182.7 7,124 1

Hash (cost=893.35..893.35 rows=39 width=87) (actual time=8.467..8.467 rows=7,124 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 683kB
12. 4.197 7.135 ↓ 182.7 7,124 1

Hash Join (cost=44.24..893.35 rows=39 width=87) (actual time=1.684..7.135 rows=7,124 loops=1)

  • Hash Cond: (pr.child = p.id)
13. 2.185 2.185 ↑ 1.0 30,283 1

Seq Scan on place_relation pr (cost=0.00..466.83 rows=30,283 width=12) (actual time=0.008..2.185 rows=30,283 loops=1)

14. 0.280 0.753 ↓ 167.0 1,670 1

Hash (cost=44.11..44.11 rows=10 width=75) (actual time=0.753..0.753 rows=1,670 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 142kB
15. 0.473 0.473 ↓ 167.0 1,670 1

Seq Scan on place p (cost=0.00..44.11 rows=10 width=75) (actual time=0.044..0.473 rows=1,670 loops=1)

  • Filter: ((type)::text = 'ets'::text)
  • Rows Removed by Filter: 243
Planning time : 0.371 ms
Execution time : 61.129 ms