Re: Dynamic SELECT condition

On 2 Mar 2003, Greg Stark wrote:

> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
>
> > On Sat, 1 Mar 2003, Bruno Wolff III wrote:
> >
> > > On Mon, Feb 24, 2003 at 19:53:00 +0200,
> > >   Victor Yegorov <viy@pirmabanka.lv> wrote:
> > > >
> > > > I mean, I know the id of a root object, it's 3. If I'll extract with
> > > > condition parent_id = 3, then I'll get only 2 rows. I'd like to have a
> > > > recursive result set - while there are records in the table for which
> > > > count(next_level_parent_id == this_level_child_id) > 0, select parent_id, child_id.
>
> 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.

What do you mean??
GiST indexing just indexes columns of type *array* for the &&,=,@,~,@@,
etc.. operators.
And if i am looking for 3 somewhere in an array column then
i can definately use the index.
See contrib/intarray for further details.
Of course if the majority of queries just want the first direct father
or the most ancient root grandfather or the median ancestor,
there are ways to speed up such situations.

>
> --
> greg
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr