sub tree expense, PostgreSQL

Language: Layout:

create table expense
(
  id        integer primary key,
  parent_id integer,
  name      text
);

create table expense_amount
(
  ref_id integer primary key references expense,
  amount numeric(12,2)
);

insert into expense 
  (id, parent_id, name) 
values
  (1,null,'A'),
  (2,null,'B'),
  (3,1,'C'),
  (4,1,'D'),
  (5,4,'E');

insert into expense_amount 
  (ref_id, amount)
values
  (3, 500),
  (4, 200),
  (5, 100);

with recursive tree as (
   select id, parent_id, name, 1 as level, concat('/', id) as path, null::numeric as amount
   from expense
   where parent_id is null
   union all
   select c.id, c.parent_id, c.name, p.level + 1, concat(p.path, '/', c.id), ea.amount
   from expense c 
     join tree p on c.parent_id = p.id
     left join expense_amount ea on ea.ref_id = c.id
)
select e.id, 
       lpad(' ', (e.level - 1) * 2, ' ')||e.name as name,
       e.amount as element_amount,
       (select sum(amount) 
        from tree t
        where t.path like e.path||'%') as sub_tree_amount,
       e.path
from tree e
order by path


absolute service time: 0,51 sec