From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: Victor Yegorov <viy@pirmabanka.lv>
Cc: pgsql-sql@postgresql.org
Subject: Re: Dynamic SELECT condition
Date: Mon, 3 Mar 2003 10:01:17 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0303030945400.25006-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <87smu5kg0z.fsf@stark.dyndns.tv>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, gsstark@mit.edu, bruno@wolff.to, viy@pirmabanka.lv
Subject: Re: Dynamic SELECT condition
In-Reply-To: <Pine.LNX.4.44.0303030945400.25006-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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