Re: Gist indexes on int arrays

On 3 Mar 2003, Greg Stark wrote:

>
> > What do you mean??
> > GiST indexing just indexes columns of type *array* for the &&,=,@,~,@@,
> > etc.. operators.
>
> Hm, you're right of course. I wonder where I got the idea that it didn't
> handle these operators.
>
> This is fascinating and could be useful for something I'm working on.
>
> How do gist indexes interact with more normal data types to index? I have a
> situation where I have a table with millions of records, and I'm mostly
> operating on a subset of those records, usually 1k-10k of them.
>
> The queries would look like
>
> WHERE foo_id = ?
>   AND '{1}'::integer[] ~ attr_a
>   AND '{2}'::integer[] ~ attr_b
>
> Right now I'm using the contrib/array *= operator and I have an index on
> foo_id. Having to scan through up to 10,000 records isn't great but isn't too
> bad. I wonder whether having a gist index and using the ~ operator would be
> worthwhile?

Absolutely.
Moreover if your array element positions that you want to compare
against(e.g attr_a[1], or attr_b[n], where n is the last element) are
known, then you could have a function "first" that returns
the first element (you must pay attention to nulls and out of bound
situations), and a function "last" that returns the last element.
Then you could have normal btree indexes on first(attr_a), and on
last(attr_b), but unfortunately not an index on both.


>
> The contrib/array, contrib/intagg, and contrib/intarray directories seem to
> all be aimed at handling the same thing and seem to provide mostly
> complementary features. Perhaps they should all be merged into one package. I
> guess it does show there's lots of demand for this type of datatype.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr