explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x7Ib : Optimization for: plan #2idk

Settings

Optimization path:

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

Aggregate (cost=1,738.96..1,738.97 rows=1 width=32) (actual rows= loops=)

2.          

CTE child_to_parents

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=20.93..1,700.00 rows=111 width=276) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20.93..65.47 rows=1 width=276) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

HashAggregate (cost=16.49..16.52 rows=3 width=16) (actual rows= loops=)

  • Group Key: house.aoguid
6. 0.000 0.000 ↓ 0.0

Index Scan using fias_house_houseguid on house (cost=0.43..16.48 rows=3 width=16) (actual rows= loops=)

  • Index Cond: (houseguid = '12cb7afc-55e4-4d1c-a31b-0ab1825a2207'::uuid)
7. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on addrobj (cost=4.44..16.31 rows=1 width=276) (actual rows= loops=)

  • Recheck Cond: (aoguid = house.aoguid)
  • Filter: ((actstatus = '1'::numeric) AND (currstatus = '0'::numeric) AND (livestatus = 1))
8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fias_addressobjects_aoguid (cost=0.00..4.44 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (aoguid = house.aoguid)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4.44..163.23 rows=11 width=276) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

WorkTable Scan on child_to_parents (cost=0.00..0.20 rows=10 width=16) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on addrobj addrobj_1 (cost=4.44..16.29 rows=1 width=276) (actual rows= loops=)

  • Recheck Cond: (aoguid = child_to_parents.parentguid)
  • Filter: (currstatus = '0'::numeric)
12. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fias_addressobjects_aoguid (cost=0.00..4.44 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (aoguid = child_to_parents.parentguid)
13.          

CTE res

14. 0.000 0.000 ↓ 0.0

Sort (cost=37.15..37.24 rows=38 width=128) (actual rows= loops=)

  • Sort Key: a.aolevel
15. 0.000 0.000 ↓ 0.0

Append (cost=0.00..36.15 rows=38 width=128) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

CTE Scan on child_to_parents a (cost=0.00..2.59 rows=37 width=344) (actual rows= loops=)

  • Filter: (aolevel >= '1'::numeric)
17. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=16.93..33.00 rows=1 width=99) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using fias_house_houseguid on house house_2 (cost=16.93..32.99 rows=1 width=42) (actual rows= loops=)

  • Index Cond: (houseguid = '12cb7afc-55e4-4d1c-a31b-0ab1825a2207'::uuid)
  • Filter: (enddate = $5)
19.          

Initplan (forIndex Scan)

20. 0.000 0.000 ↓ 0.0

Aggregate (cost=16.49..16.50 rows=1 width=4) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using fias_house_houseguid on house house_1 (cost=0.43..16.48 rows=3 width=4) (actual rows= loops=)

  • Index Cond: (houseguid = '12cb7afc-55e4-4d1c-a31b-0ab1825a2207'::uuid)
22.          

CTE names

23. 0.000 0.000 ↓ 0.0

CTE Scan on res (cost=0.00..0.85 rows=38 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

CTE Scan on names (cost=0.00..0.76 rows=38 width=32) (actual rows= loops=)