Date: Mon, 3 Mar 2003 09:27:13 +0200
From: Victor Yegorov <viy@pirmabanka.lv>
To: Greg Stark <gsstark@mit.edu>
Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>,
	Bruno Wolff III <bruno@wolff.to>, <pgsql-sql@postgresql.org>
Subject: Re: Dynamic SELECT condition
Message-ID: <20030303072713.GC9377@pirmabanka.lv>
Mail-Followup-To: Greg Stark <gsstark@mit.edu>,
	Achilleus Mantzios <achill@matrix.gatewaynet.com>,
	Bruno Wolff III <bruno@wolff.to>, <pgsql-sql@postgresql.org>
References: <Pine.LNX.4.44.0303021347340.20034-100000@matrix.gatewaynet.com>
	<87smu5kg0z.fsf@stark.dyndns.tv>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="1SQmhf2mF2YjsYvc"
Content-Disposition: inline
In-Reply-To: <87smu5kg0z.fsf@stark.dyndns.tv>
User-Agent: Mutt/1.4i

--1SQmhf2mF2YjsYvc
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Greg Stark <gsstark@mit.edu> [02.03.2003 16:22]:
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
>=20
> You might want to look into the "Nested Sets" cookbook page too:
>=20
> http://www.brasileiro.net:8080/postgres/cookbook/view-recipes.adp?section=
_id=3D310&format=3Dlong
>=20
> 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 pai=
n to
> modify the tree though.
>=20
> > If you want to know the direct kids (4,5 in the example) of id=3D3, the=
n it is
> > select from table where itoar(3) ~ parents and level(parents)=3D2 (itoa=
r and
> > level are simple C functions that convert an int4 to its corresponding =
1x1
> > array and calculate the length of the array , respectively)
>=20
> In the "int_agg" directory in contrib there are operators that do this, y=
ou
> can say "WHERE parents *=3D 3". I prefer to keep level in a separate colu=
mn
> though.
>=20
> I've find the features in the "int_agg" and "array" directories in the co=
ntrib
> directory to be extremely useful. The only disadvantage is that the optim=
izer
> doesn't have good basis for guessing the selectivity of the *=3D type ope=
rators.
>=20
> GiST indexing of arrays is nice but depends on knowing which element of t=
he
> array you're looking for. If you're looking for 3 anywhere in the hierarc=
hy 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.

--=20

Victor Yegorov

--1SQmhf2mF2YjsYvc
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+YwPR2RKGLEykUDYRAs8nAKDFdzcUzDOSa3KMOmiSpp4zL0NDswCgnNvV
zxOGShM/bsAEqY3m+g199qY=
=bQ/I
-----END PGP SIGNATURE-----

--1SQmhf2mF2YjsYvc--