On Sun, Sep 22, 2013 at 9:48 PM, Kaare Rasmussen <kaare@xxxxxxxxxx> wrote:
-- Hi AlbanYes, 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.
4. Using a recursive common table _expression_ (CTE). http://www.postgresql.org/docs/9.2/static/queries-with.html
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.
