From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Cc: David Garamond <davidgaramond@gmail.com>
Subject: Re: Array: comparing first N elements?
Date: Tue, 12 May 2009 11:38:32 +0300
Message-ID: <200905121138.33188.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com>
References: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com>
Óôéò 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
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, davidgaramond@gmail.com
Subject: Re: Array: comparing first N elements?
In-Reply-To: <200905121138.33188.achill@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