explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MY7w

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 0.199 ↑ 3.4 9 1

Sort (cost=157.94..158.02 rows=31 width=210) (actual time=0.199..0.199 rows=9 loops=1)

  • Output: t.id, t.name, t.name_path, t.level, rt.name, t.element_path
  • Sort Key: t.element_path
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=48
2.          

CTE element_tree

3. 0.011 0.147 ↑ 3.1 10 1

Recursive Union (cost=0.14..141.33 rows=31 width=337) (actual time=0.011..0.147 rows=10 loops=1)

  • Buffers: shared hit=47
4. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_element_type on public.element_type et (cost=0.14..2.15 rows=1 width=337) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: et.id, et.name, NULL::uuid, et.attributes, NULL::uuid, et.allow_versioned_children, et.allow_versions, et.allow_all_modifications, et.is_base_type, 1, ARRAY[et.id], (et.name)::text
  • Index Cond: (et.id = '6b8942c1-43ca-4f1d-98e0-0441eb54ae3c'::uuid)
  • Buffers: shared hit=2
5. 0.012 0.126 ↑ 3.0 1 7

Nested Loop (cost=0.38..13.91 rows=3 width=337) (actual time=0.016..0.018 rows=1 loops=7)

  • Output: child.id, child.name, parent.id, child.attributes, etr.relation_type_id, child.allow_versioned_children, child.allow_versions, child.allow_all_modifications, child.is_base_type, (parent.level + 1), (parent.element_path || child.id), ((parent.name_path || '/'::text) || (child.name)::text)
  • Inner Unique: true
  • Buffers: shared hit=45
6. 0.046 0.105 ↑ 3.0 1 7

Nested Loop (cost=0.23..10.41 rows=3 width=337) (actual time=0.013..0.015 rows=1 loops=7)

  • Output: child.id, child.name, child.attributes, child.allow_versioned_children, child.allow_versions, child.allow_all_modifications, child.is_base_type, etr.relation_type_id, parent.id, parent.level, parent.element_path, parent.name_path
  • Inner Unique: true
  • Join Filter: (cardinality(array_positions(parent.element_path, child.id)) < 2)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=27
7. 0.021 0.049 ↑ 8.0 1 7

Hash Join (cost=0.09..1.15 rows=8 width=116) (actual time=0.007..0.007 rows=1 loops=7)

  • Output: etr.relation_type_id, etr.from_type_id, parent.id, parent.level, parent.element_path, parent.name_path
  • Hash Cond: (etr.to_type_id = parent.id)
  • Buffers: shared hit=7
8. 0.014 0.014 ↑ 1.0 8 7

Seq Scan on public.element_type_relation etr (cost=0.00..1.02 rows=8 width=48) (actual time=0.001..0.002 rows=8 loops=7)

  • Output: etr.from_type_id, etr.to_type_id, etr.relation_type_id, etr.attributes, etr.description
  • Buffers: shared hit=7
9. 0.007 0.014 ↑ 10.0 1 7

Hash (cost=0.04..0.04 rows=10 width=84) (actual time=0.002..0.002 rows=1 loops=7)

  • Output: parent.id, parent.level, parent.element_path, parent.name_path
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.007 0.007 ↑ 10.0 1 7

WorkTable Scan on element_tree parent (cost=0.00..0.04 rows=10 width=84) (actual time=0.000..0.001 rows=1 loops=7)

  • Output: parent.id, parent.level, parent.element_path, parent.name_path
11. 0.010 0.010 ↑ 1.0 1 10

Index Scan using pk_element_type on public.element_type child (cost=0.14..1.15 rows=1 width=237) (actual time=0.001..0.001 rows=1 loops=10)

  • Output: child.id, child.name, child.allow_versions, child.allow_versioned_children, child.is_base_type, child.allow_all_modifications, child.attributes, child.description
  • Index Cond: (child.id = etr.from_type_id)
  • Buffers: shared hit=20
12. 0.009 0.009 ↑ 1.0 1 9

Index Only Scan using pk_relation_type on public.relation_type rt_1 (cost=0.15..1.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=9)

  • Output: rt_1.id
  • Index Cond: (rt_1.id = etr.relation_type_id)
  • Heap Fetches: 9
  • Buffers: shared hit=18
13. 0.011 0.187 ↑ 3.4 9 1

Hash Join (cost=0.26..15.85 rows=31 width=210) (actual time=0.185..0.187 rows=9 loops=1)

  • Output: t.id, t.name, t.name_path, t.level, rt.name, t.element_path
  • Hash Cond: (rt.id = t.relation_type_id)
  • Buffers: shared hit=48
14. 0.009 0.009 ↑ 480.0 2 1

Seq Scan on public.relation_type rt (cost=0.00..11.92 rows=960 width=24) (actual time=0.008..0.009 rows=2 loops=1)

  • Output: rt.id, rt.name, rt.attributes, rt.is_owner_relation
  • Buffers: shared hit=1
15. 0.008 0.167 ↑ 3.4 9 1

Hash (cost=0.12..0.12 rows=31 width=218) (actual time=0.167..0.167 rows=9 loops=1)

  • Output: t.id, t.name, t.name_path, t.level, t.element_path, t.relation_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=47
16. 0.159 0.159 ↑ 3.1 10 1

CTE Scan on element_tree t (cost=0.00..0.12 rows=31 width=218) (actual time=0.012..0.159 rows=10 loops=1)

  • Output: t.id, t.name, t.name_path, t.level, t.element_path, t.relation_type_id
  • Buffers: shared hit=47