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.


1 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...Left and right boundaries for a tree in interval mode

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

  • 1
    What you call interval mode is often referred to as nested set. Yet you say that interval mode is better than nested set, is that a typo? Sep 28, 2021 at 14:01
  • 1
    There is a confusion in terminology here: What as been called interval mode (not a name I remember seeing but that doesn't mean it is invalid) by SQLPro is generally called the “nested set model” and is one way of representing a nested set (i.e. a tree structure) (see en.wikipedia.org/wiki/Nested_set_model). What is used in the question is a directed adjacency list, still representing a tree or nested set structure but by just listing which node is any other node's parent. Sep 28, 2021 at 14:32
  • thanks for the corrections
    – SQLpro
    Sep 28, 2021 at 17:14
  • 2
    Just to mention in there are other ways than nested set to encapsulate the transitive closure of a tree. You can for example use a materialized path or an explicit table for the tc. Main benefit of the nested set compared to the others is that the representation takes up very little space (2 numbers per node). On the other hand it is a nightmare compared to the other when it comes to maintaining it during modification of the tree. You may also want to read some of VADIM TROPASHKO's work on the subject. Here is a starter: arxiv.org/pdf/cs/0402051.pdf Sep 29, 2021 at 15:44
  • 1
    I personally prefer an explicit table for the transitive closure. It can easily be maintained via triggers and is the only one that offers referential integrity. Sep 29, 2021 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.