Postgres Pro
Downloads
Home   >   mailing lists

Thread: index ops for _int4 and trees?

index ops for _int4 and trees?

From
Lincoln Yeoh
Date:
Hi,

Say I have the following table:

create table test (
id int,
lineage integer[]
);

insert into test (id,lineage) values ('8','{1,2,3}');
insert into test (id,lineage) values ('9','{1,3,7}');
insert into test (id,lineage) values ('10','{1,2,3}');
insert into test (id,lineage) values ('11','{1,2,3,10}');
insert into test (id,lineage) values ('12','{1,3,7,9}');

1) How do I create an index on integer[] aka _int4?

2) Is it possible to do something similar to the following select?

select * from test where lineage like '{1,2,3,%';

I'm basically using this as a method of fetching rows in a particular
branch of a whole tree, without having to do recursion and multiple selects.

If 1 or 2 are not possible then I'll stick with using text and converting
ids to zeropadded hexadecimal <sigh>.

I'm thinking that there should be a quick way to do branches and trees,
after all there's a btree index type, so... ;).

Using text works but is rather crude, any working suggestions?

Thanks,
Link.

Re: index ops for _int4 and trees?

From
"Gyozo Papp"
Date:
Hello,

have a look at contrib/intarray directory!
There is a pretty good index-support for one-dimensional integer array - solution for your 1st question.
... and it also includes two simple operator @ (= 'contains' similiar to AND) and && ( = 'overlap' similiar to OR) to
checkarray values against an other array. 

There is a short README.intarray file telling you what to do.
There is another contrib in contrib/array for more support to check array values. But I don't know this contibution can
profitfrom the other's index technique. 

After you 've installed these contribs your query can be written:
> select * from test where lineage like '{1,2,3,%';
select * from test where lineage[1:3] @ '{1,2,3}';

I think these satisfy you.
Best,

Papp Gyozo
- pgerzson@freestart.hu

----- Original Message -----
From: "Lincoln Yeoh" <lyeoh@pop.jaring.my>
To: "PostgreSQL-General" <pgsql-general@postgresql.org>
Sent: 2001. május 15. 10:20
Subject: [GENERAL] index ops for _int4 and trees?


> Hi,
>
> Say I have the following table:
>
> create table test (
> id int,
> lineage integer[]
> );
>
> insert into test (id,lineage) values ('8','{1,2,3}');
> insert into test (id,lineage) values ('9','{1,3,7}');
> insert into test (id,lineage) values ('10','{1,2,3}');
> insert into test (id,lineage) values ('11','{1,2,3,10}');
> insert into test (id,lineage) values ('12','{1,3,7,9}');
>
> 1) How do I create an index on integer[] aka _int4?
>
> 2) Is it possible to do something similar to the following select?
>
> select * from test where lineage like '{1,2,3,%';
>
> I'm basically using this as a method of fetching rows in a particular
> branch of a whole tree, without having to do recursion and multiple selects.
>
> If 1 or 2 are not possible then I'll stick with using text and converting
> ids to zeropadded hexadecimal <sigh>.
>
> I'm thinking that there should be a quick way to do branches and trees,
> after all there's a btree index type, so... ;).
>
> Using text works but is rather crude, any working suggestions?
>
> Thanks,
> Link.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: index ops for _int4 and trees?

From
"Gyozo Papp"
Date:
Oh,
I forgot that without any contrib you can select rows whose papth to the root {1,2,3}:

select * from test where lineage[1:3] = '{1,2,3}';

but the gist indexing (intarray) performs a significant speed increase.

BTW, lineage represents egde-list of a directed graph or a tree ?
> insert into test (id,lineage) values ('8','{1,2,3}');
------------------------------------------------|-^
> insert into test (id,lineage) values ('9','{1,3,7}');
It seems to me that node 3 can be accessed from both node 1 and node 3 directly, or it's just a mistake?

----- Original Message -----
From: "Gyozo Papp" <pgerzson@freestart.hu>
To: "PostgreSQL-General" <pgsql-general@postgresql.org>; "Lincoln Yeoh" <lyeoh@pop.jaring.my>
Sent: 2001. április 15. 13:23
Subject: Re: [GENERAL] index ops for _int4 and trees?


Hello,

have a look at contrib/intarray directory!
There is a pretty good index-support for one-dimensional integer array - solution for your 1st question.
... and it also includes two simple operator @ (= 'contains' similiar to AND) and && ( = 'overlap' similiar to OR) to
checkarray values against an other array. 

There is a short README.intarray file telling you what to do.
There is another contrib in contrib/array for more support to check array values. But I don't know this contibution can
profitfrom the other's index technique. 

After you 've installed these contribs your query can be written:
> select * from test where lineage like '{1,2,3,%';
select * from test where lineage[1:3] @ '{1,2,3}';

I think these satisfy you.
Best,

Papp Gyozo
- pgerzson@freestart.hu

----- Original Message -----
From: "Lincoln Yeoh" <lyeoh@pop.jaring.my>
To: "PostgreSQL-General" <pgsql-general@postgresql.org>
Sent: 2001. május 15. 10:20
Subject: [GENERAL] index ops for _int4 and trees?


> Hi,
>
> Say I have the following table:
>
> create table test (
> id int,
> lineage integer[]
> );
>
> insert into test (id,lineage) values ('8','{1,2,3}');
> insert into test (id,lineage) values ('9','{1,3,7}');
> insert into test (id,lineage) values ('10','{1,2,3}');
> insert into test (id,lineage) values ('11','{1,2,3,10}');
> insert into test (id,lineage) values ('12','{1,3,7,9}');
>
> 1) How do I create an index on integer[] aka _int4?
>
> 2) Is it possible to do something similar to the following select?
>
> select * from test where lineage like '{1,2,3,%';
>
> I'm basically using this as a method of fetching rows in a particular
> branch of a whole tree, without having to do recursion and multiple selects.
>
> If 1 or 2 are not possible then I'll stick with using text and converting
> ids to zeropadded hexadecimal <sigh>.
>
> I'm thinking that there should be a quick way to do branches and trees,
> after all there's a btree index type, so... ;).
>
> Using text works but is rather crude, any working suggestions?
>
> Thanks,
> Link.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: index ops for _int4 and trees?

From
Lincoln Yeoh
Date:
At 06:29 PM 15-05-2001 +0200, Gyozo Papp wrote:
>Oh,
>I forgot that without any contrib you can select rows whose papth to the
root {1,2,3}:
>
>select * from test where lineage[1:3] = '{1,2,3}';
>but the gist indexing (intarray) performs a significant speed increase.

Thanks.

>BTW, lineage represents egde-list of a directed graph or a tree ?
>> insert into test (id,lineage) values ('8','{1,2,3}');
>------------------------------------------------|-^
>> insert into test (id,lineage) values ('9','{1,3,7}');
>It seems to me that node 3 can be accessed from both node 1 and node 3
directly, or it's just a mistake?

It's a mistake in my example.

Aside but related:
Oleg Bartunov also mentioned that subset searches are possible with gist:

select * from table <TABLE> where <array_field> @ '{1,2,3}'

So I've asked him whether his work on gist indexing int arrays can be used
to do substring indexing on text, as a built-in to Postgresql.

I think it can be done. Then subtext_ops here we come :).

If not I'll resort to converting text characters to their code values and
stuffing them into int arrays. Ugly :). Not sure what happens when the
arrays get large.

Cheerio,
Link.