explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J3Y2 : adaw

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

Sort (cost=6,519,134.60..6,519,148.34 rows=5,496 width=68) (actual rows= loops=)

  • Sort Key: ((((substr(xx.slot, 16, 4) || substr(xx.slot, 11, 4)) || substr(xx.slot, 6, 4)) || substr(xx.slot, 1, 4)))
2. 0.000 0.000 ↓ 0.0

Subquery Scan on xx (cost=0.29..6,518,793.18 rows=5,496 width=68) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Group (cost=0.29..6,518,642.04 rows=5,496 width=70) (actual rows= loops=)

  • Group Key: s.id
4. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_pkey on shelf_shelf s (cost=0.29..3,794,823.51 rows=5,496 width=6) (actual rows= loops=)

  • Filter: (SubPlan 6)
5.          

SubPlan (for Index Scan)

6. 0.000 0.000 ↓ 0.0

Result (cost=334.59..353.74 rows=847 width=4) (actual rows= loops=)

  • One-Time Filter: ((NOT s.is_cancelled) AND (s.is_shelf OR s.is_box))
7.          

CTE subtable

8. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.29..334.59 rows=851 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_pkey on shelf_shelf shelf_shelf_2 (cost=0.29..2.50 rows=1 width=8) (actual rows= loops=)

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

Nested Loop (cost=0.29..31.51 rows=85 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

WorkTable Scan on subtable ss_2 (cost=0.00..0.20 rows=10 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_parent_id_864a5448 on shelf_shelf s_3 (cost=0.29..3.04 rows=9 width=12) (actual rows= loops=)

  • Index Cond: (parent_id = ss_2.id)
  • Filter: (NOT is_cancelled)
13. 0.000 0.000 ↓ 0.0

CTE Scan on subtable subtable_2 (cost=334.59..353.74 rows=847 width=4) (actual rows= loops=)

  • Filter: (id <> 1)
14.          

SubPlan (for Group)

15. 0.000 0.000 ↓ 0.0

CTE Scan on subtable (cost=245.78..247.80 rows=101 width=516) (actual rows= loops=)

16.          

CTE subtable

17. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.29..245.78 rows=101 width=20) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_pkey on shelf_shelf (cost=0.29..2.50 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (id = s.id)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..24.13 rows=10 width=20) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

WorkTable Scan on subtable ss (cost=0.00..0.20 rows=10 width=4) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_pkey on shelf_shelf s_1 (cost=0.29..2.39 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (id = ss.parent_id)
  • Filter: ((NOT is_cancelled) AND (parent_id IS NOT NULL))
22. 0.000 0.000 ↓ 0.0

CTE Scan on subtable subtable_1 (cost=245.78..247.80 rows=101 width=16) (actual rows= loops=)

23.          

CTE subtable

24. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.29..245.78 rows=101 width=24) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_pkey on shelf_shelf shelf_shelf_1 (cost=0.29..2.50 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = s.id)
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..24.13 rows=10 width=24) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

WorkTable Scan on subtable ss_1 (cost=0.00..0.20 rows=10 width=4) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using shelf_shelf_pkey on shelf_shelf s_2 (cost=0.29..2.39 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = ss_1.parent_id)
  • Filter: ((NOT is_cancelled) AND (parent_id IS NOT NULL))