Re: 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