recursion - PostgreSQL recursive CTE million size tree select by dynamic rank with paging -

my goal create query can retrieve tree controlling depth , amount of items returned @ each depth sorted rank descending, rank changing.

i.e.

give me tree starting @ node id 4, maximum depth of 5 (starting @ node) 5 children max @ each level sorted descending rank.

so have tree structure:

create table thingtree (   id uuid not null,   parent_id uuid,   text character varying(2048) not null,   rank integer not null default 0,   constraint thingtree_pkey primary key (id),   constraint thingtree_parent_id_fkey foreign key (parent_id)       references thingtree (id) match simple       on update no action on delete no action ) 

i want select node id , children, configurable limit of records pulled @ each depth , sorted rank, changing.

so decided create data:

create extension "uuid-ossp"; create function build_tree (parent_id uuid,max_depth integer,current_depth integer,max_direct_children integer) returns boolean $$    declare     new_id uuid := uuid_generate_v4();     begin      if current_depth >= max_depth         return 0;     end if;      insert thingtree values (new_id, parent_id,'test',current_depth + 1);     in 0..(max_direct_children - 1) loop         perform build_tree(new_id,max_depth,current_depth + 1, max_direct_children);     end loop;       return 0;        end; $$ language plpgsql; 

i use function generate tree 5 deep 6 children on each depth

select build_tree(null ,5, 0 ,6); 

the above function may take 20 seconds run

next try recursively select data:

with recursive thingtrees (     select tt.id,1::int depth, tt.parent_id, tt.text, tt.rank thingtree tt id = '207f7c55-1c68-4f0e-9493-416b84b6a0a4'   union     select st.id, tts.depth + 1 depth, st.parent_id, st.text, st.rank          thingtrees tts     join      thingtree st      on (tts.id = st.parent_id)     tts.depth <= 5 order st.rank    ) select * thingtrees 

now depth fine, knows not go past 5, issue want maximum of 5 direct children each level of node in tree, result returns 6 (because i'm not limiting it).

the issue if limit recursive union cte:

with recursive thingtrees (     select tt.id,1::int depth, tt.parent_id, tt.text, tt.rank thingtree tt id = '207f7c55-1c68-4f0e-9493-416b84b6a0a4'   union     (select st.id, tts.depth + 1 depth, st.parent_id, st.text, st.rank          thingtrees tts     join      thingtree st      on (tts.id = st.parent_id)     tts.depth <= 5 order st.rank limit 5)    ) select * thingtrees 

the issue here returns first 5 sub nodes of tree instead of 5 each depth.