hierarchy_tree.sql

create unlogged table t(id serial primary key, parent_id int references t(id));

insert into t select i, case when i > 10 /* how many top level? */ then i::int / 10 else null end from generate_series(1, 1e4) i;

create index on t(parent_id);

vacuum analyze t;

with recursive q(id, level) as (
select 1 as id, 0 as level -- all descendants of top parent with id=1
union all
select t.id, q.level + 1 from q join t on t.parent_id = q.id
) select * from q;