0

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.

3
  • Is this your exact query? where p.id in (select p.id from PCte C) is a tautology as long as there are rows in PCte.
    – Lennart
    Sep 28, 2021 at 14:31
  • Please consider reading this advice
    – mustaccio
    Sep 28, 2021 at 15:06
  • @Lennart yes, sorry, I made a mistake and fixed it Sep 28, 2021 at 15:40

1 Answer 1

0

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...

7
  • 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?
    – Lennart
    Sep 28, 2021 at 14:01
  • 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
  • interval mode is extremly performant for SELECT queries because your never have to use a recursive query. Recursive queries are especially slow because they cannot benefits with index and parallelism. Instead the treatment of recursive queries needs an iterative approach. The only counterpart of the interval method is that INSERTs and UPDATEs are more complexe and needs much more resources to execute. But generally this is not a problem : in trees the INSERT/UPDATEs are generally less than the SELECT, because trees are generally used to classify, which suppose to be used for search...
    – SQLpro
    Sep 28, 2021 at 17:19
  • 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
    – Lennart
    Sep 29, 2021 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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