
Dear Hackers,

While working on a postgres-based fulltext searching system 
we encountered the following problem:

 There is  a table 
  create table t (
    x  int []
  ) 
 and a given integer constant y.
 The task is to find those records of this table, which contain the
 value y in the arrays x.

This could be implemented by writing a function 
 array_contains(array,value)
and  selecting :
 select * from table where array_contains(table.x, y);

Such SQL statement would result in a long sequential scan, which is not
cool and not fast. It could be much more useful if we could use an index
for such a query.

If there were a kind of B-tree index which allows to have several
key values for a record, the problem could be  solved easily!

We would like to know if such a feature is already implemented          
in postgres indexes, otherwise  are there any serious difficulties in
implementing  it.

May be, GiSt could be useful for this task. Does anybody know any alive
implementation of GiST ?

Regards, Ivan Panchenko 







Mon, 20 Nov 2000 16:08:01 +0300 (MSK)
"Ivan E. Panchenko" <ivan@xray.sai.msu.ru>
Indexing on  arrays 

I am also working on a full text search system, and I have a similar
problem, although I can get around the full table scan if I can simply
return a full set of tuples.

select * from table where table.key in ( function('bla bla bla') );

Or

create table result as function('bla bla bla');

select * from table where table.key = result.key;



I have been trying to figure out how to return a variable number and
format of tuples, but am getting lost in the code. Any help anyone has
would be greatly appreciated.


"Ivan E. Panchenko" wrote:
> 
> Dear Hackers,
> 
> While working on a postgres-based fulltext searching system
> we encountered the following problem:
> 
>  There is  a table
>   create table t (
>     x  int []
>   )
>  and a given integer constant y.
>  The task is to find those records of this table, which contain the
>  value y in the arrays x.
> 
> This could be implemented by writing a function
>  array_contains(array,value)
> and  selecting :
>  select * from table where array_contains(table.x, y);
> 
> Such SQL statement would result in a long sequential scan, which is not
> cool and not fast. It could be much more useful if we could use an index
> for such a query.
> 
> If there were a kind of B-tree index which allows to have several
> key values for a record, the problem could be  solved easily!
> 
> We would like to know if such a feature is already implemented
> in postgres indexes, otherwise  are there any serious difficulties in
> implementing  it.
> 
> May be, GiSt could be useful for this task. Does anybody know any alive
> implementation of GiST ?
> 
> Regards, Ivan Panchenko

-- 
http://www.mohawksoft.com

Mon, 20 Nov 2000 23:16:06 -0500
mlw <markw@mohawksoft.com>
Re: Indexing on  arrays