From: | Victor Yegorov <viy(at)pirmabanka(dot)lv> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>, Bruno Wolff III <bruno(at)wolff(dot)to>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Dynamic SELECT condition |
Date: | 2003-03-03 07:27:13 |
Message-ID: | 20030303072713.GC9377@pirmabanka.lv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
* Greg Stark <gsstark(at)mit(dot)edu> [02.03.2003 16:22]:
> Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
>
> You might want to look into the "Nested Sets" cookbook page too:
>
> http://www.brasileiro.net:8080/postgres/cookbook/view-recipes.adp?section_id=310&format=long
>
> This representation of hierarchies has a lot of nice properties including
> being able to look up a whole subtree quickly. It makes it a bit of a pain to
> modify the tree though.
>
> > If you want to know the direct kids (4,5 in the example) of id=3, then it is
> > select from table where itoar(3) ~ parents and level(parents)=2 (itoar and
> > level are simple C functions that convert an int4 to its corresponding 1x1
> > array and calculate the length of the array , respectively)
>
> In the "int_agg" directory in contrib there are operators that do this, you
> can say "WHERE parents *= 3". I prefer to keep level in a separate column
> though.
>
> I've find the features in the "int_agg" and "array" directories in the contrib
> directory to be extremely useful. The only disadvantage is that the optimizer
> doesn't have good basis for guessing the selectivity of the *= type operators.
>
> GiST indexing of arrays is nice but depends on knowing which element of the
> array you're looking for. If you're looking for 3 anywhere in the hierarchy I
> don't think you can use the index.
Thanks a lot.
Nested sets are pretty good.
In my app trees arebeing updated quite often, so, instead of having a field
field in a tree tale, I've combined nested sets with adjacency list. Now I
have such table:
tree_id int4 not null,
branch_id int4 not null,
leaf_id int4 not null,
left int4 not null,
right int4 not null,
...
Quite usefull, I think.
--
Victor Yegorov
From | Date | Subject | |
---|---|---|---|
Next Message | val | 2003-03-03 09:16:00 | copy help |
Previous Message | Rajesh Kumar Mallah | 2003-03-03 06:25:58 | Re: HardCORE QUERY HELP!!! |