explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4WB5

Settings
# exclusive inclusive rows x rows loops node
1. 4.666 29.588 ↑ 1.0 1 1

Aggregate (cost=301.78..301.79 rows=1 width=32) (actual time=29.588..29.588 rows=1 loops=1)

2.          

CTE cte1

3. 0.013 0.104 ↑ 2.2 15 1

Recursive Union (cost=0.00..19.34 rows=33 width=36) (actual time=0.026..0.104 rows=15 loops=1)

4. 0.033 0.033 ↓ 4.0 12 1

Seq Scan on budgets b (cost=0.00..1.03 rows=3 width=36) (actual time=0.024..0.033 rows=12 loops=1)

5. 0.028 0.058 ↑ 1.5 2 2

Hash Join (cost=1.07..1.76 rows=3 width=36) (actual time=0.021..0.029 rows=2 loops=2)

  • Hash Cond: (c.parent = c2.id)
6. 0.004 0.004 ↑ 3.8 8 2

WorkTable Scan on cte1 c (cost=0.00..0.60 rows=30 width=12) (actual time=0.001..0.002 rows=8 loops=2)

7. 0.012 0.026 ↓ 4.0 12 1

Hash (cost=1.03..1.03 rows=3 width=28) (actual time=0.026..0.026 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.014 0.014 ↓ 4.0 12 1

Seq Scan on budgets c2 (cost=0.00..1.03 rows=3 width=28) (actual time=0.010..0.014 rows=12 loops=1)

9.          

CTE cte2

10. 0.423 0.658 ↑ 15.1 61 1

Recursive Union (cost=0.00..150.06 rows=922 width=52) (actual time=0.041..0.658 rows=61 loops=1)

11. 0.067 0.067 ↑ 1.0 42 1

Seq Scan on transaction_amounts t (cost=0.00..1.42 rows=42 width=52) (actual time=0.036..0.067 rows=42 loops=1)

12. 0.064 0.168 ↑ 8.8 10 2

Hash Join (cost=1.94..13.02 rows=88 width=52) (actual time=0.062..0.084 rows=10 loops=2)

  • Hash Cond: (c_1.alias = t2.id)
13. 0.018 0.018 ↑ 14.0 30 2

WorkTable Scan on cte2 c_1 (cost=0.00..8.40 rows=420 width=12) (actual time=0.002..0.009 rows=30 loops=2)

14. 0.035 0.086 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=44) (actual time=0.086..0.086 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.051 0.051 ↑ 1.0 42 1

Seq Scan on transaction_amounts t2 (cost=0.00..1.42 rows=42 width=44) (actual time=0.028..0.051 rows=42 loops=1)

16.          

CTE cte3

17. 0.469 9.653 ↑ 3.2 61 1

WindowAgg (cost=33.32..37.68 rows=194 width=107) (actual time=9.183..9.653 rows=61 loops=1)

18. 0.142 9.184 ↑ 3.2 61 1

Sort (cost=33.32..33.80 rows=194 width=147) (actual time=9.173..9.184 rows=61 loops=1)

  • Sort Key: c_2.main_id, t2_1.name, c_2.lev
  • Sort Method: quicksort Memory: 33kB
19. 0.104 9.042 ↑ 3.2 61 1

Hash Left Join (cost=3.89..25.94 rows=194 width=147) (actual time=6.754..9.042 rows=61 loops=1)

  • Hash Cond: (c_2.alias = t_1.id)
20. 1.539 2.365 ↑ 3.2 61 1

Hash Join (cost=1.94..22.86 rows=194 width=119) (actual time=0.136..2.365 rows=61 loops=1)

  • Hash Cond: (c_2.transaction = t2_1.id)
21. 0.747 0.747 ↑ 15.1 61 1

CTE Scan on cte2 c_2 (cost=0.00..18.44 rows=922 width=80) (actual time=0.045..0.747 rows=61 loops=1)

22. 0.035 0.079 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=43) (actual time=0.079..0.079 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.044 0.044 ↑ 1.0 42 1

Seq Scan on transactions t2_1 (cost=0.00..1.42 rows=42 width=43) (actual time=0.022..0.044 rows=42 loops=1)

24. 0.113 6.573 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=32) (actual time=6.573..6.573 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 6.460 6.460 ↑ 1.0 42 1

Seq Scan on transaction_amounts t_1 (cost=0.00..1.42 rows=42 width=32) (actual time=0.013..6.460 rows=42 loops=1)

26.          

CTE cte4

27. 0.478 11.072 ↓ 5.7 187 1

WindowAgg (cost=6.84..7.58 rows=33 width=132) (actual time=10.552..11.072 rows=187 loops=1)

28. 0.442 10.594 ↓ 5.7 187 1

Sort (cost=6.84..6.92 rows=33 width=140) (actual time=10.543..10.594 rows=187 loops=1)

  • Sort Key: c_3.main_budget, i.name, c_3.lev
  • Sort Method: quicksort Memory: 42kB
29. 0.197 10.152 ↓ 5.7 187 1

Hash Join (cost=1.07..6.01 rows=33 width=140) (actual time=9.380..10.152 rows=187 loops=1)

  • Hash Cond: (i.budget = c_3.id)
30. 9.814 9.814 ↑ 3.2 61 1

CTE Scan on cte3 i (cost=0.00..3.88 rows=194 width=116) (actual time=9.188..9.814 rows=61 loops=1)

31. 0.007 0.141 ↑ 2.2 15 1

Hash (cost=0.66..0.66 rows=33 width=28) (actual time=0.141..0.141 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.134 0.134 ↑ 2.2 15 1

CTE Scan on cte1 c_3 (cost=0.00..0.66 rows=33 width=28) (actual time=0.029..0.134 rows=15 loops=1)

33.          

CTE cte5

34. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on scenarios (cost=0.00..24.50 rows=1,450 width=20) (actual time=0.014..0.014 rows=0 loops=1)

35.          

CTE cte6

36. 0.918 24.007 ↑ 1.3 187 1

WindowAgg (cost=51.86..56.64 rows=239 width=151) (actual time=23.051..24.007 rows=187 loops=1)

37. 0.774 23.089 ↑ 1.3 187 1

Sort (cost=51.86..52.46 rows=239 width=163) (actual time=23.035..23.089 rows=187 loops=1)

  • Sort Key: t_2.transaction, t_2.budget
  • Sort Method: quicksort Memory: 51kB
38. 0.220 22.315 ↑ 1.3 187 1

Hash Right Join (cost=5.59..42.42 rows=239 width=163) (actual time=22.127..22.315 rows=187 loops=1)

  • Hash Cond: (s.id = t_2.scenario)
39. 0.018 0.018 ↓ 0.0 0 1

CTE Scan on cte5 s (cost=0.00..29.00 rows=1,450 width=20) (actual time=0.018..0.018 rows=0 loops=1)

40. 0.652 22.077 ↓ 5.7 187 1

Hash (cost=5.18..5.18 rows=33 width=147) (actual time=22.077..22.077 rows=187 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
41. 4.014 21.425 ↓ 5.7 187 1

Hash Left Join (cost=3.17..5.18 rows=33 width=147) (actual time=19.943..21.425 rows=187 loops=1)

  • Hash Cond: (t_2.name = n.id)
42. 1.186 17.193 ↓ 5.7 187 1

Hash Right Join (cost=1.07..2.98 rows=33 width=132) (actual time=16.037..17.193 rows=187 loops=1)

  • Hash Cond: (a.id = t_2.alias)
43. 0.045 0.045 ↑ 1.0 42 1

Seq Scan on transaction_amounts a (cost=0.00..1.42 rows=42 width=16) (actual time=0.035..0.045 rows=42 loops=1)

44. 0.336 15.962 ↓ 5.7 187 1

Hash (cost=0.66..0.66 rows=33 width=120) (actual time=15.962..15.962 rows=187 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
45. 15.626 15.626 ↓ 5.7 187 1

CTE Scan on cte4 t_2 (cost=0.00..0.66 rows=33 width=120) (actual time=10.558..15.626 rows=187 loops=1)

46. 0.098 0.218 ↓ 2.3 114 1

Hash (cost=1.49..1.49 rows=49 width=19) (actual time=0.218..0.218 rows=114 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
47. 0.120 0.120 ↓ 2.3 114 1

Seq Scan on names n (cost=0.00..1.49 rows=49 width=19) (actual time=0.059..0.120 rows=114 loops=1)

48. 24.922 24.922 ↑ 1.3 187 1

CTE Scan on cte6 (cost=0.00..4.78 rows=239 width=40) (actual time=23.073..24.922 rows=187 loops=1)

Planning time : 8.872 ms
Execution time : 32.446 ms