explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gLjQ : child

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on c_tree (cost=2,874.53..2,913.41 rows=9 width=32) (actual rows= loops=)

  • Filter: (lvl = 0)
2.          

CTE c_with_level

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.29..2,468.16 rows=861 width=441) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Index Scan using geo_units_pkey on geo_units (cost=0.29..8.30 rows=1 width=441) (actual rows= loops=)

  • Index Cond: (id = 1)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..244.26 rows=86 width=441) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

WorkTable Scan on c_with_level parent (cost=0.00..0.20 rows=10 width=12) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using index_geo_units_on_parent_id on geo_units child (cost=0.29..24.29 rows=9 width=437) (actual rows= loops=)

  • Index Cond: (parent_id = parent.id)
8.          

CTE c_tree

9. 0.000 0.000 ↓ 0.0

Recursive Union (cost=19.41..406.36 rows=1,724 width=308) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=19.41..38.93 rows=4 width=308) (actual rows= loops=)

  • Hash Cond: (c_with_level.lvl = (max(c_with_level_1.lvl)))
11. 0.000 0.000 ↓ 0.0

CTE Scan on c_with_level (cost=0.00..17.22 rows=861 width=276) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=19.39..19.39 rows=1 width=4) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Aggregate (cost=19.37..19.38 rows=1 width=4) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

CTE Scan on c_with_level c_with_level_1 (cost=0.00..17.22 rows=861 width=4) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=29.86..35.02 rows=172 width=308) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=29.86..33.30 rows=172 width=608) (actual rows= loops=)

  • Group Key: branch_parent.*
17. 0.000 0.000 ↓ 0.0

Sort (cost=29.86..30.29 rows=172 width=608) (actual rows= loops=)

  • Sort Key: branch_parent.*
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.30..23.47 rows=172 width=608) (actual rows= loops=)

  • Hash Cond: (branch_parent.id = branch_child.parent_id)
19. 0.000 0.000 ↓ 0.0

CTE Scan on c_with_level branch_parent (cost=0.00..17.22 rows=861 width=576) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=0.80..0.80 rows=40 width=40) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

WorkTable Scan on c_tree branch_child (cost=0.00..0.80 rows=40 width=40) (actual rows= loops=)