Postgres Pro
Downloads
Home   >   mailing lists

Re: Using Ltree For Hierarchical Structures - Mailing list pgsql-sql

From Misa Simic
Subject Re: Using Ltree For Hierarchical Structures
Date
Msg-id CAH3i69m3kROAWSEOFBEMx+65PQdF7X-jjG3_mKWupicCLvuJpA@mail.gmail.com
Whole thread Raw
In response to Re: Using Ltree For Hierarchical Structures  (Igor Neyman <ineyman@perceptron.com>)
Responses Re: Using Ltree For Hierarchical Structures
List pgsql-sql
Tree view
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    


2013/2/26 Igor Neyman <ineyman@perceptron.com>


From: Don Parris [mailto:parrisdc@gmail.com]
Sent: Sunday, February 24, 2013 5:21 PM
To: pgsql-sql@postgresql.org
Subject: Using Ltree For Hierarchical Structures

Hi all,
With many thanks to Misa and others who helped out with my question about working with hierarchical data, I have now written a blog post on how I implemented the ltree module to solve my problem.

http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/
Frankly, if you work with hierarchical data, I'm not sure I could recommend it strongly enough.  I should think that even experienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly table designs and recursive queries in order to work with hierarchical structures.
I really hope this blog post will help others in the same boat.


Regards,
Don


It's all "in the eyes of beholder".
IMHO, recursive CTEs are perfect for hierarchical structures, and much cleaner than 2-table design using ltree, that you show in the blog.

Regards,
Igor Neyman




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

pgsql-sql by date:

Previous
From: Igor Neyman
Date:
Subject: Re: Using Ltree For Hierarchical Structures
Next
From: mkumbale
Date:
Subject: Re: Creating a new database with a TEMPLATE did not work