explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2idk

Settings

Optimization(s) for this plan:

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

Aggregate (cost=36,002,592.23..36,002,592.24 rows=1 width=32) (actual rows= loops=)

2.          

CTE child_to_parents

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=1,000.43..2,839,748.64 rows=73,747,960 width=331) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.43..21,807.02 rows=2,860 width=331) (actual rows= loops=)

  • Workers Planned: 2
5. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.43..20,521.02 rows=1,192 width=331) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on addrobj (cost=0.00..7,915.84 rows=2,384 width=331) (actual rows= loops=)

  • Filter: ((actstatus = '1'::numeric) AND (currstatus = '0'::numeric) AND (livestatus = 1))
7. 0.000 0.000 ↓ 0.0

Index Scan using fias_house_aoguid on house (cost=0.43..490.80 rows=101 width=16) (actual rows= loops=)

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

Merge Join (cost=23,422.74..134,298.24 rows=7,374,510 width=331) (actual rows= loops=)

  • Merge Cond: (addrobj_1.aoguid = child_to_parents.parentguid)
9. 0.000 0.000 ↓ 0.0

Sort (cost=20,733.81..20,862.73 rows=51,570 width=331) (actual rows= loops=)

  • Sort Key: addrobj_1.aoguid
10. 0.000 0.000 ↓ 0.0

Seq Scan on addrobj addrobj_1 (cost=0.00..8,762.86 rows=51,570 width=331) (actual rows= loops=)

  • Filter: (currstatus = '0'::numeric)
11. 0.000 0.000 ↓ 0.0

Sort (cost=2,688.93..2,760.43 rows=28,600 width=16) (actual rows= loops=)

  • Sort Key: child_to_parents.parentguid
12. 0.000 0.000 ↓ 0.0

WorkTable Scan on child_to_parents (cost=0.00..572.00 rows=28,600 width=16) (actual rows= loops=)

13.          

CTE res

14. 0.000 0.000 ↓ 0.0

Sort (cost=31,995,166.48..32,056,623.17 rows=24,582,676 width=612) (actual rows= loops=)

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

Append (cost=0.00..2,090,195.05 rows=24,582,676 width=612) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

CTE Scan on child_to_parents a (cost=0.00..1,720,785.73 rows=24,582,653 width=1,096) (actual rows= loops=)

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

Subquery Scan on *SELECT* 2 (cost=577.77..669.40 rows=23 width=99) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on house house_2 (cost=577.77..669.12 rows=23 width=42) (actual rows= loops=)

  • Recheck Cond: ((endedate = $5) AND (houseguid = '12cb7afc-55e4-4d1c-a31b-0ab1825a2207'::uuid))
19.          

Initplan (forBitmap Heap Scan)

20. 0.000 0.000 ↓ 0.0

Result (cost=27.76..27.77 rows=1 width=8) (actual rows= loops=)

21.          

Initplan (forResult)

22. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..27.76 rows=1 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan Backward using fias_house_endedate on house house_1 (cost=0.43..549,975.99 rows=20,123 width=8) (actual rows= loops=)

  • Index Cond: (endedate IS NOT NULL)
  • Filter: (houseguid = '12cb7afc-55e4-4d1c-a31b-0ab1825a2207'::uuid)
24. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=550.00..550.00 rows=23 width=0) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fias_house_endedate (cost=0.00..86.38 rows=4,527 width=0) (actual rows= loops=)

  • Index Cond: (endedate = $5)
26. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fias_house_houseguid (cost=0.00..463.35 rows=20,123 width=0) (actual rows= loops=)

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

CTE names

28. 0.000 0.000 ↓ 0.0

CTE Scan on res (cost=0.00..553,110.21 rows=24,582,676 width=32) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

CTE Scan on names (cost=0.00..491,653.52 rows=24,582,676 width=32) (actual rows= loops=)