Hi Igor,
I agree it is all "in the eyes of beholder".
Would be good if you can show how to achieve the goal (Summing on Top Levels categories in hierarchy) with CTE?
For example show all categories in level 2 (x), and sum amounts for each... (Sum takes all amounts from all transactions of its child categories in any bellow levels).
I have tested both scenarios - and indexed ltree has given better result - though there is a possibility I haven't pick best approach to solve the problem with CTE...
I am just interested in performance - implementation detail is less important...
Data:
Total number of categories: 1000 (in all levels)
No of Categories in top level: 5
No of categories in level 2: 20
Total number of levels: can vary - max in my testing was 8...
Transaction rows with amounts: 1 000 000
(though I am not sure what u meant by: "2-table design using ltree", and with CTE there are 2 tables... Categories and Transactions: just in categories instead of ltree datatype, is integer datatype: parent_id)
Many thanks,
Misa