pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing Ltree For Hierarchical Structures
9+ messages / 4 participants
[nested] [flat]
* Using Ltree For Hierarchical Structures
@ 2013-02-24 22:20 Don Parris <parrisdc@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Don Parris @ 2013-02-24 22:20 UTC (permalink / raw)
To: pgsql-sql
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
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris;
GPG Key ID: F5E179BE
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 14:01 Igor Neyman <ineyman@perceptron.com>
parent: Don Parris <parrisdc@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Igor Neyman @ 2013-02-26 14:01 UTC (permalink / raw)
To: Don Parris <parrisdc@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 16:14 Misa Simic <misa.simic@gmail.com>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 9+ messages in thread
From: Misa Simic @ 2013-02-26 16:14 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Don Parris <parrisdc@gmail.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 18:22 Don Parris <parrisdc@gmail.com>
parent: Misa Simic <misa.simic@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Don Parris @ 2013-02-26 18:22 UTC (permalink / raw)
To: Misa Simic <misa.simic@gmail.com>; +Cc: Igor Neyman <ineyman@perceptron.com>; pgsql-sql
Hi Igor,
As Misa points out, my original design used 2 tables - category &
line-items. Either way 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.
I cannot speak to performance, but understanding CTEs has been difficult
for me. Ltree is so much simpler. I am certain there is a place for CTEs,
but why torture myself trying to hash out a CTE when ltree makes the
queries so much easier to write?
On Feb 26, 2013 11:14 AM, "Misa Simic" <misa.simic@gmail.com> wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 18:43 Thomas Kellerer <spam_eater@gmx.net>
parent: Don Parris <parrisdc@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2013-02-26 18:43 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 19:30 Igor Neyman <ineyman@perceptron.com>
parent: Don Parris <parrisdc@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Igor Neyman @ 2013-02-26 19:30 UTC (permalink / raw)
To: Don Parris <parrisdc@gmail.com>; Misa Simic <misa.simic@gmail.com>; +Cc: pgsql-sql
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 biggest difference lies in the approach to summing line-items by various levels of category - with each branch of the tree having different levels.
I cannot speak to performance, but understanding CTEs has been difficult for me. Ltree is so much simpler. I am certain there is a place for CTEs, but why torture myself trying to hash out a CTE when ltree makes the queries so much easier to 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 21:54 Don Parris <parrisdc@gmail.com>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 9+ messages in thread
From: Don Parris @ 2013-02-26 21:54 UTC (permalink / raw)
To: pgsql-sql
On Tue, Feb 26, 2013 at 2:30 PM, Igor Neyman <ineyman@perceptron.com> wrote:
>
>
> 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 biggest difference lies in
> the approach to summing line-items by various levels of category - with
> each branch of the tree having different levels.
> I cannot speak to performance, but understanding CTEs has been difficult
> for me. Ltree is so much simpler. I am certain there is a place for CTEs,
> but why torture myself trying to hash out a CTE when ltree makes the
> queries so much easier to 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
>
>
That is helpful to know. I am unaware of the connect_by module, so will
have to look into it. It is good to know about Oracle's Connect By
construct - I wondered how they dealt with that. If nothing else, I am
learning more about CTE's. This whole thread came about because, while I
am normally able to look at example code and translate that into my
particular problem, I really got myself in over my head with this category
tree. Most of my queries have been fairly simple so far, but this one
stumped me due to my lack of experience.
In the meanwhile, I think understanding CTEs is probably still an important
skill, so I am sure I should be familiar with both. I greatly appreciate
everyone's contributions - I am learning a great deal here. It's one of
the reasons why I post my blog stories - to give back a little something of
what I've discovered and learned. Misa gave me an example of CTE code to
play around with, and I am going to tackle that in more depth later this
week.
Cheers!
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris;
GPG Key ID: F5E179BE
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-26 23:12 Misa Simic <misa.simic@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 9+ messages in thread
From: Misa Simic @ 2013-02-26 23:12 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
2013/2/26 Thomas Kellerer <spam_eater@gmx.net>
> 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/<http://dcparris.net/2013/...;
>>
>> 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
Hi Thomas,
Yes we met that problem and it further makes deeper problems... i.e. what
if some category in up level - change his parent (updated path field) -
path must be changed for all childs...
Of several solutions - we have picked to use the best from both worlds...
So we still use - parent_id column... and ltree is used just as
materialized path - to improve performance... I think Materialized Views
what comming in 9.3 - (I still havent seen how it works) - will help in
that way - we will see...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using Ltree For Hierarchical Structures
@ 2013-02-27 16:02 Igor Neyman <ineyman@perceptron.com>
parent: Don Parris <parrisdc@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Igor Neyman @ 2013-02-27 16:02 UTC (permalink / raw)
To: Don Parris <parrisdc@gmail.com>; pgsql-sql
From: Don Parris [mailto:parrisdc@gmail.com]
Sent: Tuesday, February 26, 2013 4:55 PM
To: pgsql-sql@postgresql.org
Subject: Re: Using Ltree For Hierarchical Structures
...
I am unaware of the connect_by module, so will have to look into it.
...
Cheers!
Don
For that look into tablefunc Extension.
It also has other useful functions, such as crosstab.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-02-27 16:02 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-24 22:20 Using Ltree For Hierarchical Structures Don Parris <parrisdc@gmail.com>
2013-02-26 14:01 ` Igor Neyman <ineyman@perceptron.com>
2013-02-26 16:14 ` Misa Simic <misa.simic@gmail.com>
2013-02-26 18:22 ` Don Parris <parrisdc@gmail.com>
2013-02-26 19:30 ` Igor Neyman <ineyman@perceptron.com>
2013-02-26 21:54 ` Don Parris <parrisdc@gmail.com>
2013-02-27 16:02 ` Igor Neyman <ineyman@perceptron.com>
2013-02-26 18:43 ` Thomas Kellerer <spam_eater@gmx.net>
2013-02-26 23:12 ` Misa Simic <misa.simic@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox