Re: Using Ltree For Hierarchical Structures

<p dir="ltr">Hi Igor, <p dir="ltr">As Misa points out, my original design used 2 tables - category & line-items. 
Eitherway it's two tables.    The biggest difference lies in the approach to summing line-items by various levels of
category- with each branch of the tree having different levels.<p dir="ltr">I cannot speak to performance, but
understandingCTEs has been difficult for me.  Ltree is so much simpler.  I am certain there is a place for CTEs, but
whytorture myself trying to hash out a CTE when ltree makes the queries so much easier to write?<br /><p dir="ltr">On
Feb26, 2013 11:14 AM, "Misa Simic" <<a href="mailto:misa.simic@gmail.com">misa.simic@gmail.com</a>> wrote:<br />
><br/> > Hi Igor,<br /> ><br /> > I agree it is all "in the eyes of beholder".<br /> ><br /> > Would
begood if you can show how to achieve the goal (Summing on Top Levels categories in hierarchy) with CTE?<br /> ><br
/>> For example show all categories in level 2 (x), and sum amounts for each... (Sum takes all amounts from all
transactionsof its child categories in any bellow levels).<br /> ><br /> > I have tested both scenarios - and
indexedltree has given better result - though there is a possibility I haven't pick best approach to solve the problem
withCTE...<br /> ><br /> > I am just interested in performance - implementation detail is less important...<br />
><br/> > Data:<br /> ><br /> > Total number of categories: 1000 (in all levels)<br /> > No of Categories
intop level: 5<br /> > No of categories in level 2: 20<br /> > Total number of levels: can vary - max in my
testingwas 8...<br /> ><br /> ><br /> > Transaction rows with amounts: 1 000 000<br /> ><br /> ><br />
>(though I am not sure what u meant by: "2-table design using ltree", and with CTE there are 2 tables... Categories
andTransactions: just in categories instead of ltree datatype, is integer datatype: parent_id)<br /> ><br /> >
Manythanks,<br /> ><br /> > Misa    <br /> ><br /> ><br /> > 2013/2/26 Igor Neyman <<a
href="mailto:ineyman@perceptron.com">ineyman@perceptron.com</a>><br/> >><br /> >><br /> >><br />
>>From: Don Parris [mailto:<a href="mailto:parrisdc@gmail.com">parrisdc@gmail.com</a>]<br /> >> Sent:
Sunday,February 24, 2013 5:21 PM<br /> >> To: <a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br/> >> Subject: Using Ltree For Hierarchical
Structures<br/> >><br /> >> Hi all,<br /> >> With many thanks to Misa and others who helped out with
myquestion about working with hierarchical data, I have now written a blog post on how I implemented the ltree module
tosolve my problem.<br /> >><br /> >> <a
href="http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/">http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/</a><br
/>>> Frankly, if you work with hierarchical data, I'm not sure I could recommend it strongly enough.  I should
thinkthat even experienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly
tabledesigns and recursive queries in order to work with hierarchical structures.<br /> >> I really hope this
blogpost will help others in the same boat.<br /> >><br /> >><br /> >> Regards,<br /> >> Don<br
/>>><br /> >><br /> >> It's all "in the eyes of beholder".<br /> >> IMHO, recursive CTEs are
perfectfor hierarchical structures, and much cleaner than 2-table design using ltree, that you show in the blog.<br />
>><br/> >> Regards,<br /> >> Igor Neyman<br /> >><br /> >><br /> >><br />
>><br/> >> --<br /> >> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> >> To make changes to your
subscription:<br/> >> <a
href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br/> ><br />
><br/>