Hints for trees

From: Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Hints for trees
Date: 2010-10-18 10:41:31
Message-ID: AANLkTin_fL3ONUsse4E1xg8csRXFJJRaUdF1Ft54D7n4@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.
I need to implement a (classical) categorization of items accordingly
to a tree-like hierarchy.
I mean, I have a tree describing the hierarchy itself. Then I need to
link each item to one or
more categories.
Later I may be asked to select all items belonging to one or more
categories or any of the
descendants.
At the moment I'm not aware of any limitation on the hierarchy depth.
What I know is:
1. the category tree won't change very often;
2. the relation between products and categories will also be rather static.

I've found very interesting the ltree TYPE
(http://www.postgresql.org/docs/current/static/ltree.html)
as well as a number of other SQL-based solution like the WITH
RECURSIVE predicate.

My questions:

1. What would it be a good solution in terms of query speed in this context?
2. Why that one would be better than the others?
3. If the solution is ltree, would it make any sense (and why) to use
wither GIN or GiST indexing?

I'll appreciate also any link to documentation or discussions about this topic.
Thanks.

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

Browse pgsql-general by date

  From Date Subject
Next Message Mike Christensen 2010-10-18 10:45:32 Re: Need some help setting up pgAgent
Previous Message Dave Page 2010-10-18 10:37:48 Re: Need some help setting up pgAgent