Indexing on arrays

From: "Ivan E(dot) Panchenko" <ivan(at)xray(dot)sai(dot)msu(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Indexing on arrays
Date: 2000-11-20 13:08:01
Message-ID: Pine.LNX.4.21.0011201602250.1097-100000@xray.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Philip Warner 2000-11-20 13:15:09 Re: Current CVS broken?
Previous Message Denis Perchine 2000-11-20 12:57:48 Re: Current CVS broken?