Re: indexing array columns

Rajarshi Guha <rguha(at)indiana(dot)edu> writes:
> select A from theTable where sim(B, C) > 0.8

> I realize that my table is essentially a collection of 12-dimensional
> points and that I could replace my similarity function with a distance
> function.

> Thus my query boils down to asking 'find me rows of the table that are
> within X distance of my query'

> I know that the GIS community deals with 2D points, but I'm not familiar
> with this area and if I understand correctly, they use Euclidean
> distances, where as I need Manhattan distances.

> What type of indexing, available in Postgres could be used for my
> problem? Would it require me to implement my own indexing scheme?

AFAIK there's nothing "canned" that addresses this problem. In
principle you could implement it as an operator class for GIST,
which is much less work than inventing your own index access method,
but still not exactly trivial.

In any case you would need to recast the queries so that the WHERE
clauses look like binary operators returning boolean. I think the
most straightforward way would be to invent a concept of a 12-D
Manhattan sphere, and a point-contained-in-sphere operator, so that
your query becomes

select ... where B <@ sphere(C, 0.8);

If you've got distance, volume, and contains/contained-in operations
for your datatype, then it should be fairly straightforward to adapt
one of the existing GIST "r-tree" opclasses.

regards, tom lane