Thread: Using Ltree For Hierarchical Structures

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
writtena 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
evenexperienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly table
designsand 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
youshow in the blog. 

Regards,
Igor Neyman

<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/> 

Don Parris wrote on 24.02.2013 23:20:
> 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.
>

How do you ensure referential integrity with this approach?
(i.e. make sure that all elements from the path column actually point to an existing category)

Thomas

From: Don Parris [mailto:parrisdc@gmail.com]
Sent: Tuesday, February 26, 2013 1:23 PM
To: Misa Simic
Cc: Igor Neyman; pgsql-sql@postgresql.org
Subject: Re: [SQL] Using Ltree For Hierarchical Structures

Hi Igor,
As Misa points out, my original design used 2 tables - category & line-items.  Either way it's two tables.    The
biggestdifference lies in the approach to summing line-items by various levels of category - with each branch of the
treehaving different levels. 
I cannot speak to performance, but understanding CTEs has been difficult for me.  Ltree is so much simpler.  I am
certainthere is a place for CTEs, but why torture myself trying to hash out a CTE when ltree makes the queries so much
easierto write? 


Don,

To answer this question...
Before I worked (mostly) with Oracle which has "connect by" construct to work with hierarchies.
So, when I switched to Postgres I was happy to find "connect_by" contrib. modul.
And with more recent PG versions, it was just natural transition from contrib. module to recursive CTEs.

Igor