Optimise recursive CTE or rewrite to PL/pgSQL LOOP

Question

I've got a procedure with recursion in Postgres 13 version. It makes orgstructure from Vice to simple employer

with recursive relations_recurs(
    pos_id, boss_pos_id, level_num, link_type, link_type_array, pos_id_array
) as (
    select  l.pos_id,
            l.boss_pos_id,
            1 as level_num,
            l.link_type,
            l.link_type_array,
            l.pos_id_array
    from    temp_loop l
    union all
    select  l.pos_id,
            l.boss_pos_id,
            (r.level_num + 1) as level_num,
            l.link_type,
            (r.link_type_array || l.link_type) as link_type_array,
            (r.pos_id_array || l.pos_id) as pos_id_array
    from    temp_pos_boss_with_min_link l
    join    relations_recurs r
    on      l.pos_id = r.boss_pos_id
    and     l.pos_id <> all (r.pos_id_array)
)
select  distinct pos_id_array[1] as pos_id,
        boss_pos_id as boss_pos_id,
        level_num as level_id,
        pos_id as pos_original_id,
        (case 
            when array[1, 2] <@ t.link_type_array then 0
            when array[1] <@ t.link_type_array then 1
            else 2
        end) link_type
from    relations_recurs t;

But it's slow. I'm trying to write query whithout recursion in order to increase performance, I was told using WHILE with LOOP can improve code.

How to make this into a procedure using WHILE and LOOP?

Here is a db<>fiddle link.

Answer 1

Another way to have performances while navigating into tree structures is to use the nested set model instead of the adjacency one.

(extratcs from my owen papers...)

Once you transpose the ajacency list to the nested set all recursive queries that are strictly iterative (eg read one by one every row repeatedly) will execute as set based statement and can use indexes and parallelism...

Also avoid using arrays that are strictly iterative toot and replace tems by tables...