Optimizing a recursive CTE or replacing it with a temporary table

Question

I have a recursive query like this:

with recursive PCte(id) as 
(
    select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
    ('123', '124','125', 'n')
    union all
    select p2.id from Product p2 inner join PCte cte on p2.parent_id = cte.id
) 
select * 
from Product p 
left join Product psub on psub.parent_id = p.id 
where p.id in (select c.id from PCte c)

This query have a slow performance with a large number of children, are there any possible optimization ways? If it is possible to replace the recursive CTE with a temporary table, I would like to see an example, thanks a lot.

Answer 1

The fastest way to operate with a tree consists in representing the tree structure in interval mode instead of adjacency mode.

Nested set is what you have done by adding a FOREIGN KEY with a self reference to the table.

Interval mode use two boudaries that surround the value of the point...

The interval mode does not need recursive queries to find every thing you want...