From: | Antonio Fiol Bonnín <fiol(at)w3ping(dot)com> |
---|---|
To: | knut(dot)suebert(at)web(dot)de |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing a tree |
Date: | 2001-11-12 09:41:28 |
Message-ID: | 3BEF9948.12D60D5C@w3ping.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-jdbc |
Well, if you
CREATE TABLE tree ( treeid int, lft int, rgt int );
CREATE INDEX tree_idx ON tree (treeid);
And then you issue a SELECT query having a WHERE clause that includes
"treeid=1234", you will only get your 1234 tree.
OTOH, you are required to include that "treeid=1234" on EVERY query, as nodes
are not uniquely identified by "lft".
Workaround: Include a (nodeid int) field you may also have an index on and then
use the (unique) nodeid to locate your reference node. Then you will need to
include a "t1.treeid=t2.treeid" or something like that, to avoid crossing trees
on your requests.
Wow! re-reading my e-mail I found that understanding my words may be very
difficult. Sorry!
Antonio Fiol
knut(dot)suebert(at)web(dot)de wrote:
> Antonio Fiol Bonnín schrieb:
> > If you consider the approach using multiple trees, it may have quite a good
> > performance even for something like a threaded message board, if you think
> > of each thread as a different tree. Then trees are not enormous, and so
> > updates would not be so slow.
> >
> > Performance will be poor, however, when updating very large trees.
>
> Hello Antonio,
>
> how would you organize multiple trees?
>
> Multiple trees in one table made by multiple top level entries
> wouldn't help, as far as I understand that model. It seems more a
> question, if the update/insert is in the left or right wing of the
> tree - maybe it could help a bit to make the tree grow in both
> directions (lowering left to <0 or altering rgt), depending on where
> the insert happens.
>
> The hard and expensive things become easy and cheap -- and vice versa,
> I fear.
>
> Bye,
> Knut Sübert
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2001-11-12 13:09:59 | Re: Trigger documentation problem |
Previous Message | Antonio Fiol Bonnín | 2001-11-12 09:34:31 | Re: Storing a tree |
From | Date | Subject | |
---|---|---|---|
Next Message | Gunnar Rønning | 2001-11-12 10:29:36 | Re: JDBC driver |
Previous Message | Antonio Fiol Bonnín | 2001-11-12 09:34:31 | Re: Storing a tree |