Στις Tuesday 12 May 2009 11:05:28 ο/η David Garamond έγραψε:
> I have a "materialized path" tree table like this (simplified):
>
> CREATE TABLE product (
> id SERIAL PRIMARY KEY,
> parents INT[] NOT NULL,
> name TEXT NOT NULL,
> UNIQUE (parents, name)
> );
> CREATE INDEX name ON product(name);
>
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
>
Hi, I have used *exactly* the same scheme to model all PMS data in out fleet comprising
of 1.5 million rows, for some 6 years now.
You may find it in literature as genealogical tree representation.
> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';
Node 0001/0010 will have an id lets call it "parid".
If you model your path in parents[] (we also use the same column name!)
starting from the immediate father at parents[1] and going up to the root at parents[#parents]
then what you actually want is to find all nodes for which parents[1]=parid
you would want to look at the intarray contrib package for index suppor and many other goodies,
also you might want to write fucntions first(parents), last(parents) and then have an index
on those as well.
This way searching for the direct children of a node is very fast.
If on the other hand you want to find all children of parid, regardless of level,
then you would do that with: intset(parid) ~ parents
For the above to be efficient you should create an index on parents. Prefer method "gin" with opclass "gin__int_ops"
Well thats how i implemented trees in postgresql anyway.
>
> In other words, testing against the first N elements in an array.
>
> Regards,
> Dave
>
--
Achilleas Mantzios