Re: Tree structure

On Sun, Sep 22, 2013 at 9:48 PM, Kaare Rasmussen <kaare(at)jasonic(dot)dk> wrote:

> Hi Alban
>
>
> 4. Using a recursive common table expression (CTE).
>> http://www.postgresql.org/**docs/9.2/static/queries-with.**html<http://www.postgresql.org/docs/9.2/static/queries-with.html>
>>
>
> Yes, you're right. In fact that's what I'm testing a way to replace, as
> I'm not confident in the performance in all situations. My fault entirely;
> I should have told so from the start.

It might be helpful for you to discuss what sorts of concerns you have and
how they fit into the specifics of your data. Trees are an area where
different uses may have different recommended solutions. I gave my
thoughts on performance on trees above. There are a few really bad areas I
can think of. For example, if you had a ten-layer deep scan where each
scan pulled around 10% or so of the table, you might be looking at 10
sequential scans and a fair bit of CPU time. If the result set was very
large, you might see things written to disk. There are a number of gotchas.

This being said, *usually* I find that recursive CTE's are one of the
better solutions out there for trees and I think they will perform better
in more situations than many of the other solutions.

>
> --
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml